Re: implicit trimming of trailing spaces for bulk collect

Hello Mladen,

The example I provided with the variable was just to prove the point that if declared smaller length compared with the input string will give an error even if the trailing characters are spaces. In you code replace myvar varchar2(5); with myvar varchar2(4); you'll get the error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
But this will not happen for bulk collect, it will just trim the trailing spaces.

I asked a colleague developer about this and he opened a question on stackoverflow, most probably he explains better the behavior: https://stackoverflow.com/questions/71704554/bulk-collect-rtrims-strings-to-fit-varchar2-length

THank you.

În vin., 1 apr. 2022 la 18:14, Gogala, Mladen (Short Hills) < mladen.gogala_at_fiserv.com> a scris:

> Hi Laurentiu,
>
> I don’t see that happening on my DB:
>
>
>
> 1 declare
>
> 2 myvar varchar2(5);
>
> 3 begin
>
> 4 myvar:='1234 ';
>
> 5 dbms_output.put_line(length(myvar));
>
> 6* end;
>
> SQL> /
>
> 5
>
> SQL> select * from v$version;
>
>
>
> BANNER
>
>
> --------------------------------------------------------------------------------
>
> BANNER_FULL
>
>
> --------------------------------------------------------------------------------
>
> BANNER_LEGACY
>
>
> --------------------------------------------------------------------------------
>
> CON_ID
>
> ----------
>
> Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
>
> Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
>
> Version 19.14.0.0.0
>
>
>

În vin., 1 apr. 2022 la 08:17, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> a scris:

> 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