One of the major performance problem that we face in our day to day operation is the fact that spooling a char delimited file using SQLPLUS is very slow.
For example a script can take 3 minute to run and then 40 minutes to spool. Let’s say –
Create table x as select * from table1
And then
Spool file.dat
select column1||’|’||column2||’|’||column3 … from table
spool off;
We then call the SQL script using an NT command file of a UNIX shell script.
I’ve been searching the web for a better solution and I’ve found topic like custom pro*c script or use the util_file package to write the file on the server. I don’t know how to use the pro*c and I have no access to the unix server where Oracle is installed and therefore can’t test the util_file solution.
Any advice would be appreciated.