How to export Oracle Database query result to CSV

I recently had to perform a database load, an action whereby you take a query result/s from one database store and then migrate that to another database store. My idea was to automate it as this was usually done manually with a lot of clicky clicky and the query ran for a 20mins, so certainly this was not fun in the slightest bit.

Ideally what you can do with clicky clicky can be done using some form of tool or program, thankfully SQLPlus a GUI application which I use to interact with Oracle Databases has a Commandline component and I could use that to run queries against Oracle databases from a terminal/shell. Once I got valid documentation that supported that, I got to work.

#!/bin/bash

FILE="file.csv"

sqlplus -silent xxxxx/xxxxxx//xxxxx:1521/xxxx <<EOF
SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 200
SET FEEDBACK OFF
SPOOL $FILE

SELECT * FROM EMP;

SPOOL OFF
EXIT
EOF

The resulting bash script above, once it runs creates a file called file.csv and contained in this file is the result of our query SELECT * FROM EMP; . Now let’s try and understand the scripts and the options set.

SET PAGESIZE 50000 => Set this to a much bigger value. This value indicates the number of lines per page. The header line will get printed on every page. In order to avoid this, set it to a bigger value so that the header appears only once.

SET COLSEP “,” => Setting the column separator to “,”. With this setting, the list displayed by the SELECT clause will be comma-separated. This is the most important setting of this script.

SET LINESIZE 200 => The number of characters per line. The default is 80 which means after 80 characters, the rest of the content will be in the next line. Set this to a value that is good enough for the entire record to come in a single line.

SET FEEDBACK OFF => When a select query is executed, a statement appears at the prompt, say “25 rows selected”. In order to prevent this from appearing in the CSV file, the feedback is put off.

SPOOL $FILE => Spool command records the session queries and results into the file specified. In other words, this will write the results of the query to the file.

SELECT * FROM EMP => The query which gives the entire table contents of EMP. If only a part of the table is desired, the query can be updated to get the desired result.

SPOOL OFF => To stop writing the contents of the SQL session to the file.

And that’s all folks.