Concatenate CLOB with null strings using "||" in PL/SQL

user_11596750 0
Viewed 387

Lately, I discovered that code below does not behave as I have expected. For the code below, I am expecting v_result is returning '1234567890{test}'. However, recently when we have upgraded to Oracle DB 19c, v_result returns '{test}7890'. Does anyone know the reason behind it? Is there any DB upgraded feature that I am not aware of? Also, what should be the solution to it?

declare
    v_result varchar2(1000);
    v_open_tag varchar2(1);
    v_close_tag varchar2(1);
    v_string clob;
    v_string2 varchar2(10);
begin
    v_string := '1234567890';
    v_string2 := '{test}';
    v_result := v_open_tag || v_string || v_close_tag || v_string2;
    dbms_output.put_line('v_result='||v_result);
end;
plsql oracle
2 Answers

That is old bug, replace it to:

v_result := '' || v_open_tag || v_string || v_close_tag || v_string2;
user_4291000

ALso another way to fix the problem: just decrease plsql_optimize_level from default level to 1:

--default: wrong results:
declare
    v_result varchar2(1000);
    v_open_tag varchar2(1);
    v_close_tag varchar2(1);
    v_string clob;
    v_string2 varchar2(10);
begin
    v_string := '1234567890';
    v_string2 := '{test}';
    v_result := v_open_tag || v_string || v_close_tag || v_string2;
    dbms_output.put_line('v_result='||v_result);
end;
/
v_result={test}7890

Fixed using plsql_optimize_level=1:

alter session set plsql_optimize_level=1
/
declare
    v_result varchar2(1000);
    v_open_tag varchar2(1);
    v_close_tag varchar2(1);
    v_string clob;
    v_string2 varchar2(10);
begin
    v_string := '1234567890';
    v_string2 := '{test}';
    v_result := v_open_tag || v_string || v_close_tag || v_string2;
    dbms_output.put_line('v_result='||v_result);
end;
/
v_result=1234567890{test}
user_4291000
Related