implicit trimming of trailing spaces for bulk collect
Date: Fri, 1 Apr 2022 08:17:48 +0300
Message-ID: <CA+riqSWuegHZkGbo7QspBottjRXu2u77fmsc=vwhzsXMEW5iQQ_at_mail.gmail.com>
Hello,
I just faced a strange behavior which apparently replicates in both 12c and
19c, which I was not expecting.
To replicate the behavior:
create table mytable (col1 varchar2(8));
insert into mytable values('123456 ');
If i select this column value into a variable, assuming I declare it
VARCHAR2(8), I get no error, if I declare it VARCHAR2(7) it will
fail like expected with: ORA-06502: PL/SQL: numeric or value error:
character string buffer too small
DECLARE
If I declare a type and I use bulk collect, assuming that in type I declare
the record as VARCHAR2(7) it will trim the last space, VARCHAR2(6) the
last two spaces and for VARCHAR2(5) it will finally fail with ORA-06502:
PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
I tried to look into the oracle documentation to find this behavior
documented but maybe I didn't know how to search because I didn't find
anything.
My question is: is this something expected and documented? I can understand
that the behavior is to allow the code to run without failures but also on
the other hand is altering my data.
DECLARE
-> I create a table with a single column where I insert a single value
which contains 2 trailing spaces:
commit;
select dump(col1),col1,length(col1) from mytable;
v1 VARCHAR2(7);
BEGIN
SELECT col1 INTO v1 FROM mytable;
dbms_output.put_line('Value is: "'|| v1|| '" Length is : '||
length(v1));
END;
/
TYPE rt_type IS RECORD (col1 VARCHAR2(6));
TYPE rec_rt_type IS TABLE OF rt_type;
CURSOR c2 IS SELECT col1 FROM mytable;
BEGIN
OPEN c2;
LOOP
FETCH c2 BULK COLLECT INTO v_c2;
dbms_output.put_line('Value is: "' || v_c2(1).col1|| '" Length is : '
||length(v_c2(1).col1));
EXIT
WHEN c2%NOTFOUND;
END LOOP;
CLOSE c2;
END;
/
THank you
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 01 2022 - 07:17:48 CEST