Hi Steve,
I don't consider that as a bug, since Oracle needs to change the value: for
example you have a column - "c3 char(2 byte)" and it stores one multibyte
character.
In that case the value will not be padded with chr(32), but if you change
the column to "char(2 char)", you will see that the value was padded with
one extra byte - chr(32).
Of course, your case with char(1 byte) is the only variant when you don't
need to update these values, since it can't be padded, but oracle doesn't
analyze it.
If you enable tracing, you will see this update:
update "A_BUG" set "C3"=sys_op_trtb("C3", 9, 4, 1)
SYS_OP_TRTB(A,B,C,D) is a special trimming/padding function, where
A - the value for trimming/padding,
C - minimum bytes,
D - minimum characters.
B - this arguments accepts literals only and it switches between trimming
and padding, or, to be accurate, we can see that it enables/disables
ORA-01401, so with some values you can silently get trimmed values.
I don't know how exactly it works, but my test shows that oracle raises
this error when 5th bit is set to 0:
table t_log contains the values when oracle raises ORA-1401:
SQL> create table t_log(n int);
Table created.
SQL> declare
2 s varchar(100);
3 res varchar2(100);
4 e_1401 exception;
5 pragma exception_init(e_1401, -1401);
6 procedure log_e(n int) is
7 pragma autonomous_transaction;
8 begin
9 insert into t_log values(n);
10 commit;
11 end;
12 begin
13 for i in 0..1024 loop
14 begin
15 s:=q'[select dump(sys_op_trtb('--', ]'|| i ||q'[, 3, 2)) n
FROM dual]';
16 execute immediate s into res;
17 exception when e_1401 then
18 log_e(i);
19 end;
20 end loop;
21 end;
22 /
PL/SQL procedure successfully completed.
SQL> select min(n),max(n), max(n)-min(n)
2 from (select n, n-row_number()over(order by n) x from t_log) group by
x order by 1;
MIN(N) MAX(N) MAX(N)-MIN(N)
---------- ---------- -------------
1 7 6
16 23 7
32 39 7
48 55 7
64 71 7
80 87 7
96 103 7
112 119 7
128 135 7
144 151 7
160 167 7
176 183 7
192 199 7
208 215 7
224 231 7
240 247 7
257 263 6
272 279 7
288 295 7
304 311 7
320 327 7
336 343 7
352 359 7
368 375 7
384 391 7
400 407 7
416 423 7
432 439 7
448 455 7
464 471 7
480 487 7
496 503 7
513 519 6
528 535 7
544 551 7
560 567 7
576 583 7
592 599 7
608 615 7
624 631 7
640 647 7
656 663 7
672 679 7
688 695 7
704 711 7
720 727 7
736 743 7
752 759 7
769 775 6
784 791 7
800 807 7
816 823 7
832 839 7
848 855 7
864 871 7
880 887 7
896 903 7
912 919 7
928 935 7
944 951 7
960 967 7
976 983 7
992 999 7
1008 1015 7
64 rows selected.
On Wed, Mar 16, 2016 at 2:48 AM, Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx>
wrote:
I'm after an opinion before I go down the Oracle SR rabbit hole.
It seems strange to me but modifying the char/byte length semantics of a
CHAR column in an ALTER TABLE causes a DML trigger to fire. Doing the same
thing to a VARCHAR2 column does not.
Our Oracle version is 11.2.0.4 and our DB char set is AL32UTF8.
SQL> create table a_bug (c1 number, c2 varchar2(1 byte), c3 char(1 byte));
Table created.
SQL> insert into a_bug values (10, 'a', 'a');
1 row created.
SQL> commit;
Commit complete.
SQL> create or replace trigger a_bug_t1
2 before update on a_bug
3 begin
4 raise_application_error(-20001, 'Splat');
5 end;
6 /
Trigger created.
SQL> alter table a_bug modify(c2 varchar2(1 char));
Table altered.
SQL> alter table a_bug modify(c3 char(1 char));
alter table a_bug modify(c3 char(1 char))
*
ERROR at line 1:
ORA-20001: Splat
ORA-06512: at "SB.A_BUG_T1", line 2
ORA-04088: error during execution of trigger 'SB.A_BUG_T1'
SQL> drop table a_bug purge;
Table dropped.
Thanks,
Steve
------------------------------------------------------------------
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email..
------------------------------------------------------------------
