Sunday, September 27, 2009

ORA-01489: result of string concatenation is too long


One of  our customers reported us this error during reading stored procedures from Oracle database. What have caused this error? USER_SOURCE view retrieve name, type and line by line of function/stored procedure definition. Each line in a new row. We used hierarchical queries and sys_connect_by_path function to concatenate all of those lines in one which keeps the full function/stored procedure definition.

The problem is, that SQL in Oracle is able to return just 4000 bytes in one column. So, if any string is longer than 4000 bytes in concatenation you will get this error. And there is no possibility to fix it. You must change logic, use chunks or something similar (CLOBs).

As a small notice, T-SQL variables can be used for concatenation very big strings which exceeds 4000 characters. Again, you will not be able to return it back with SQL.

As a sample, the following code produces the error:

select rpad(' ',4000)||'1' as 4001StringLength
from TestTable
but the following is fine:

testVar varchar2(32767);
testVar := rpad(' ',4000)||'1'