Bulk collect RTRIMs strings to fit VARCHAR2 length

Viewed 103

I have encountered the following interesting behavior with the following code:

create or replace type schema1.t_varchar2_tab force as table of varchar2(8 byte);

declare
    v_list schema1.t_varchar2_tab;
begin
    
    select col1 
    bulk collect into v_list
    from (
        select '12345678' col1 from dual
        union all
        select '12345678                    ' from dual
    );
    
    for idx in 1..v_list.count
    loop
        dbms_output.put_line('val = #'||v_list(idx)||'#');
    end loop;

end;
/

It returns:

val = #12345678#
val = #12345678#

It looks like when you bulk collect into a nested table and a selected string is longer than the what is set in the type and if the extra characters are spaces, Oracle RTRIMs the spaces to make it fit in the VARCHAR2 size.

Is this behavior normal? Have you encountered this? I've checked the Oracle on-line documentation, but so far could not find anything related.

0 Answers
Related