Database.ca SQL Server Blog

My Blog

Thursday, February 27 2014

Keywords: Oracle, Error, ORA-01489,result of string concatenation is too long

ORA-01489:result of string concatenation is too long

Some colleague sent me the following the other day:

 

Oracle, Error, ORA-01489,result of string concatenation is too long

After little trouble shooting, I pinpointed the problem to be occurring inside an Oracle Function...specifically inside a recursive CTE (common table expression).
 
I debugged it...and it occurred where we were concatenating two varchar2(4000) strings together. What was very strange was that the error was raised even without the length of final string being longer than 4000…it was as if Oracle was throwing an error just in case...worried that the length might pass the limit in some cases…hmmmm
 
Here you will the culprit:

 

Oracle, Error, ORA-01489,result of string concatenation is too long

The solution was to use to_clob function to get the concatenation done. And then to_char to return the actual value from the pipelined function.

 

Oracle, Error, ORA-01489,result of string concatenation is too long

When using Unicode fields use :
 
To_nclob to concatenate
 
To_nchar to convert back to string at the end and return the value.

 

 

 

 

 

 

 

Written by Ramin Haghighat