Hi I have to fetch around 1.5 million records from the DB and spool it to a text file. Below is my code, I had to use to_clob as there are about 1.5 million records in the DB and it gives an error (ORA-01489: result of string concatenation is too long) without using to_clob. However, using to_clob affects the performance. It takes several days to finish running. Please suggests any improvements in my code. I would need the data to be in the txt file in couple of hours. SQL developer takes only 3 hours to export all the data.
set heading off
SET NEWPAGE NONE
set linesize 20000 pagesize 10000
set feedback off verify off trimspool on trimout on
set long 2000000000
SET LONGCHUNKSIZE 10000
SELECT '"'||to_clob(ID)||'"'||'|'||'"'||to_clob(NUMBER)||'"'||'|'||'"'||to_clob(Department)||'"'||'|'||'"'||to_clob(NAME)||'"' FROM table;