Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to retrieve certain data from an Oracle 11g release 1 database and provide it as one or more CSV files. The data resides in multiple tables and/or views. All this should work via the command line. What would be the best approach to this?

share|improve this question
up vote 10 down vote accepted

Tom Kyte has a number of different solutions to generating flat files from Oracle on his site. There is a PL/SQL implementation using UTL_FILE as well as a Pro*C SQL unloader application.

share|improve this answer
As a historical note, I wrote a script for this back in the 1990s, when I was getting used to Oracle. Standard Oracle at that time didn't come with either a utility to do this or with PL/SQL. So I wrote an unloader in SQLPlus. It's actually a script that generates another script. Bizarre, but it gets around certain limitations of SQLPlus. I think it's still floating around on the web somewhere. – Walter Mitty Feb 25 '12 at 12:22

You should look at the built in UTL_FILE package. There are several ways you could use it.

  1. You could write any number of procedures in packages that use the UTL_FILE package to write to any number of files. These procedures can then be called from almost any application including SQL*Plus.

  2. You could write a PL/SQL script to do the same work and call the script from the SQL*Plus command line itself by specifying @scriptname on the command line.

  3. You could paste a block using UTL_FILE directly into SQL*Plus, but this should only be used for one time exports and even then it might not be the best route.

In their simplest a file export using UTL_FILE would be composed of a call to FOPEN, one or more calls to PUT_LINE, and a call to FCLOSE.

share|improve this answer


now that sqlcl is available fromOracle SQL Developer 4.1 EA2 ( you can use it a lot like the old and famous sqlplus. sqlcl has an output format setting for csv

set sqlformat csv
spool x.csv
select * from yourtable[s];
spool off

for more info about sqlcl checkout Kris' blog

before sqlcl was around easiest for this was to use APEX and export the report to csv. In plain old sqlplus you can do this by using

set lines 9999 -- the appropriate size
set head off  -- no header lines
set colsep ';' --column separator to ;
set pages 0 -- no pages
set feed off
select your data;
spool to_tofile
spool off

This works best if the results are to be written on an application server or client. If they have to be written on the database server, utl_file might be a better option.


share|improve this answer
Not sure why the downvotes, this is a perfectly good answer to the question (and the suggestion I followed to solve this problem for myself in SQLPlus). – Gabriel Magana May 25 '14 at 15:49

If performance is a concern, you may want to consider tools from vendors.

I have evaluated tools from BMC, Wisdomforce, CoSort, DBCrane. They are all significantly faster than spool, utl_file or external table. We are using DBCrane because my boss didn't want to spend too much on license.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.