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.