How to get flat table from SQLPlus using spool

Post date: Aug 29, 2014 8:23:15 AM

I want to get the flat comma-separated file from terminal that runs SQLPlus. Here is how I manage to do it.

further resources:

sed techniques can be found here: http://superuser.com/questions/241018/how-to-replace-multiple-spaces-by-one-tab
useful suggestion on using spool and display setting: http://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus

Set output display parameters so that the output would look nice
1. SQL> set colsep , -- separate columns with a comma
2. SQL> set trimspool on -- remove trailing blanks
3. SQL> set pagesize 0 -- No header rows
4. SQL> set echo off
5. SQL> set feedback off
6. SQL> set headsep off -- this may or may not be useful...depends on your headings.
7. SQL> set linesize X -- X should be the sum of the column widths
8. SQL> set numw X -- X should be the length you want for numbers (avoid scientific notation on IDs)
Start recording any output into an output text file using command 'spool':
Now you can run any query
When done, you will need to turn the spool off using:
1. SQL> spool off;
and you will find your output ready for you in the specified directory.
Now you will find that your output looks quite dirty, for instance, there are leading/trailing spaces. It's very convenient to use sed to replace the spaces with comma or empty character or whatever suitable. I use the sed command below to replace multiple spaces with empty character.

Google Sites

Report abuse