How to write to a CSV file using Oracle SQL*Plus

 

SPOOL command 

The SPOOL command is unavailable in the browser-based SQL*Plus version, iSQL*Plus. To generate files while using iSQL*Plus, change the necessary preference settings to directly output to a file.

This is accomplished using the SPOOL statement.

While SPOOL is active, SQL*PLus will store the output of any query to the specified file.

Therefore, the next command to enter is spool:

spool filepath

Skipping ahead slightly, after your query is inserted, you also need to halt spool so the file output is closed by using the spool off command:

spool off

Insert the query 

The last step after the settings are modified and spool is running is to insert your query. For our simple example, we’re outputting all books from our books table.

SELECT
  title,
  author
FROM
  authors;

Don’t forget the semi-colon to close out your query statement, then enter the aforementioned spool off command.

That’s it, you’ve generated a new text file with the results of your query using SQL*Plus.

Using a script file 

---

Rather than manually entering every line, it is suggested to enter all the settings into a new script file that you can execute in SQL*Plus in a single command.

Create a new script file with the EDIT statement:

EDIT filename

Now paste the entire script command list into your new file and save. The full contents of our example script can be found below.

set colsep ,
set headsep off
set pagesize 0
set trimspool on
set linesize 2
set numwidth 5

spool authors.csv

SELECT
  title,
  author
FROM
  authors;

spool off

To execute the script, simply use the @ symbol followed by the file name:

@filename

Script should be executed and the .csv file created as expected.