How can I unload Oracle database tables into a csv files?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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 (http://www.bronzeage.us/downloads.html) because my boss didn't want to spend too much on license.

Lei
Oracle DBA
Yahoo!

Answer 4

Arun, easiest for this is 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.

Ronald.