ORA-01489: result of string concatenation is too long

Question

ORA-01489: result of string concatenation is too long

The sql query below is meant to extract data from the database as pipe delimited and spools it to a text file on unix

select a||'|'||b||'|'||c||'|'||d from table 
union
select a||'|'||b||'|'||c||'|'||d from table 

It some times gives the ORA error ORA-01489: result of string concatenation is too long

This looks like occuring if the select exceeds 4000 limit

I tried using to_clob but this works only with "union all"

Is there a way i can get around this problem

Answer 1

Do the union before the concatenation.

select to_clob(a) ||'|'|| to_clob(b) ||'|'|| to_clob(c) ||'|'|| to_clob(d) from
  (
   select a, b, c, d from table1
    union
   select a, b, c, d from table2
  )