How to unload Oracle database tables into a csv files

command lineexportoracleoracle-11g

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?

Best Answer

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.

Related Question