Lutz Haake | Freelance Oracle Consultant

A CSV export script similar to this one resulted in “ORA-01489: result of string conca­te­nation is too long”:

set heading off feed off verify off lines 8500 trimspool on long 8500 longchunksize 8500 termout off pages 0

spool out.csv 

SELECT '"' ||
  a || '";"' ||
  b || '";"' ||
  c || '";"' ||
  d || '"' 
from T 
where ... ;

spool off
exit

with c and d of varchar(4000) format.

The solution is to conca­tenate the entire export line as a CLOB:

SELECT to_clob('"' ||
  a || '";"' ||
  b || '";"') ||
  c || '";"' ||
  d || '"' 
FROM T
where ... ;