How to spool data from Oracle database using SQL*Plus

If you need to dump data into a file, the basic commands to redirect output are:

spool <filename>
...
spool off

However, this will have the headers, pagination, and probably lots of other stuff. To disable all of that and leave just the data use the following:

set heading off
set echo off
set termout off
set feedback off
set pagesize 0
set timing off
spool on
spool <filename>
...
spool off

Now, you try that and you still see extra stuff in the output file, how come? Well, some of these only work from a script. Just create a file, let's say get-data.sql, and run it as

SQL> @get-data

Another tip, for Excel you may want to produce a CSV file. You can join columns with a comma. Below is a complete example:

> cat get-data.sql

set heading off
set echo off
set termout off
set feedback off
set pagesize 0
set timing off
spool on
spool all-sales.csv
 select date || ',' || sum(amount)
   from sales
  group by date
  order by date
spool off