ORA-06502: PL/SQL: numeric or value error: character string buffer too small in Concatenation operator ||

Question

I have a string with 250,000 characters and I want copy that value to clob type. I chunked to 8 chunk (varchar2 type), Then I want convert to clob format but I get this error:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

My code:

declare   vstr_part1 varchar2(32767) := ' long string with  32,670  length '  
vstr_part2 varchar2(32767) := ' long string with  32,670  length ' 
vstr_part3 varchar2(32767) := ' long string with  32,670  length ' 
vstr_part4 varchar2(32767) := ' long string with  32,670  length ' 
vstr_part5 varchar2(32767) := ' long string with  32,670  length ' 
vstr_part6 varchar2(32767) := ' long string with  32,670  length ' 
vstr_part7 varchar2(32767) := ' long string with  32,670  length ' 
vstr_part8 varchar2(32767) := ' long string with  32,670  length '
vClobVal   clob;
vClobVal :=  vstr_part1 ||  vstr_part2 ||  vstr_part3 ||vstr_part4 || vstr_part5||  vstr_part6 ||vstr_part7 ||vstr_part8 ; 

When I use just two chunk that worked but more than 2 chunk I get the same error.

What is my mistake?

Answer 1

The only error in your code is to convert the varchar2 to CLOB first using TO_CLOB function and then you can concat them -

declare   vstr_part1 varchar2(32767) := ' long string with  32,670  length '  
vstr_part2 varchar2(32767) := ' long string with  32,670  length ' 
vstr_part3 varchar2(32767) := ' long string with  32,670  length ' 
vstr_part4 varchar2(32767) := ' long string with  32,670  length ' 
vstr_part5 varchar2(32767) := ' long string with  32,670  length ' 
vstr_part6 varchar2(32767) := ' long string with  32,670  length ' 
vstr_part7 varchar2(32767) := ' long string with  32,670  length ' 
vstr_part8 varchar2(32767) := ' long string with  32,670  length '
vClobVal   clob;
BEGIN
vClobVal :=  TO_CLOB(vstr_part1) || TO_CLOB(vstr_part2) || TO_CLOB(vstr_part3)  || TO_CLOB(vstr_part4) || TO_CLOB(vstr_part5) || TO_CLOB(vstr_part6) || TO_CLOB(vstr_part7) || TO_CLOB(vstr_part8);
END;
/

Demo.