Oracle SQL*Plus .csv Export

The simplest method of exporting Oracle data to a flat file is to use the spool file feature of SQL*Plus, which will create a fixed format output file. To create a clean file several SQL*Plus settings need to be modified to remove unwanted SQL*Plus messages from the output.

Using the emp table common to the Oracle manuals here is a short sample sql script to create a .csv format spool file. By resorting to using a column list and the concatenation symbol, ||, a comma delimited file can easily be created.

Create the file emp_export.sql:


set echo off
set feedback off
set linesize 100
set pagesize 0
set sqlprompt ''
set trimspool on
spool emp.csv

select empno||','||
ename||','||
job||','||
hiredate
from emp;

spool off


Once the SQL file has been saved, execute the following command to create the emp.csv file.

#sqlplus -S [username]/[password]@[SID] @emp_export.sql

This produces the file emp.csv which contains:

7369,SMITH,CLERK,17-DEC-80
7499,ALLEN,SALESMAN,20-FEB-81
7521,WARD,SALESMAN,22-FEB-81
7566,JONES,MANAGER,02-APR-81
7654,MARTIN,SALESMAN,28-SEP-81

Note: You can not use this technique against columns of type long, raw, long raw, and LOB datatype as Oracle will not concatenate these.