SQL*Plus formatting and automation

Here is some advice on how to run your SQL and generate a file with the delimiter of your choice.

1. Login to sqlplus
   
2. Set your page size (how often you get headers – so set this to be arbitrarily large), line size (try to make this long enough to hold a complete row of data), column separator, and suppress the “X rows selected” feedback.
   set pagesize 5000 linesize 500 colsep ',' feedback off
   
3. Setup an output file
   spool output.txt
   
4. Run your sql command
   select ... from ... where ... ;
   
5. Turn off the output
   spool off
   At this point you should have a file called output.txt with your data. Don’t worry if you have some command data in there, just read on.
   
6. You might also read up on how to format columns – in particular you can have nicer column headings:
   http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch6.htm
   

Once you have tweaked these settings to your liking, you can automate the process pretty easily. Put your commands into a file. To suppress output on screen you can set termout off (this only works with a command file):

set pagesize 5000 linesize 500 colsep ',' feedback off termout off
spool output.txt
select whatever;
spool off
quit

Then run sqlplus with that file as an argument (the -S will run the commands silently):

sqlplus -S login/pwd@server @commands.txt

An added bonus of doing it this way is you won’t see the sql commands in your output file anymore.

This is particularly handy if you need to do some scripting in an environment where you don't have control over what libraries are installed; I recently used something similar on a unix machine that had Perl installed but not DBD::Oracle. I shelled out and ran my query using sqlplus, and parsed the output in the script. It won't perform as well as a built-in, but for occasional use it is very low rent.