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

Arun,

now that sqlcl is available fromOracle SQL Developer 4.1 EA2 (4.1.0.18.37) 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.

Ronald.

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 because my boss didn't want to spend too much on license.

Answer 4

You can use Python and cx_Oracle module to extract data to disk in CSV format.

Here’s how you connect to Oracle using cx_Oracle:

constr='scott/tiger@localhost:1521/ORCL12'
con = cx_Oracle.connect(constr)
cur = con.cursor()

After data fetch you can loop through Python list and save data in CSV format.

for i, chunk in enumerate(chunks(cur)):
    f_out.write('\n'.join([column_delimiter.join(row[0]) for row in chunk]))
    f_out.write('\n')

I used this approach when I wrote TableHunter-For-Oracle