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.

  1. logon to any machine (14001, 25001, ...) you want and open SQLPlus
  1. Congratulations. Now your data is clean and ready for further analysis.

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
    1. sqlplus <user_id>_ro@<db_name>
  1. 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)
  2. Start recording any output into an output text file using command 'spool':
      1. SQL> spool '/tmp/kittipat_out_data.dat';
  3. Now you can run any query
  4. When done, you will need to turn the spool off using:
    1. SQL> spool off;
  5. and you will find your output ready for you in the specified directory.
  6. 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.
    1. sed 's/ \+//g' /tmp/kittipat_out_data.dat > /tmp/kittipat_out_data_clean.dat