ORA:01489 result of string concatenation too long oracle issue

Question

Select col1||chr(31)||col2||chr(31)||col3||chr(31)||col4 from myTable:

Above query throws error as ORA:01489 result of string concatenation too long oracle issue.

How to implement here xmlagg method? Some times no of column is more than 40.

Any suggestion here?

Answer 1

A VARCHAR2 is limited to 4000 bytes in Oracle 11g. If you are going to exceed that limit then you need to use a different data-type that can support longer strings.

For that, you can use a CLOB data-type by prepending EMPTY_CLOB():

Select EMPTY_CLOB()
       || col1 || chr(31)
       || col2 || chr(31)
       || col3 || chr(31)
       || col4
from   myTable

or wrapping the first concatenated item in TO_CLOB:

Select TO_CLOB(col1) || chr(31)
       || col2 || chr(31)
       || col3 || chr(31)
       || col4
from   myTable