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:
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:
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.
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