implicit trimming of trailing spaces for bulk collect

From: Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
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:
-> I create a table with a single column where I insert a single value
which contains 2 trailing spaces:

create table mytable (col1 varchar2(8)); insert into mytable values('123456 ');
commit;
select dump(col1),col1,length(col1) from mytable;

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
    v1 VARCHAR2(7);
BEGIN
    SELECT col1 INTO v1 FROM mytable;
    dbms_output.put_line('Value is: "'|| v1|| '" Length is : '|| length(v1));
END;
/

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
TYPE rt_type IS RECORD (col1 VARCHAR2(6)); TYPE rec_rt_type IS TABLE OF rt_type;
v_c2 rec_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-l
Received on Fri Apr 01 2022 - 07:17:48 CEST

Original text of this message