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?
now that sqlcl is available fromOracle SQL Developer 4.1 EA2 (22.214.171.124.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.
You should look at the built in UTL_FILE package. There are several ways you could use it.
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.
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.
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.
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.
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) for row in chunk])) f_out.write('\n')
I used this approach when I wrote TableHunter-For-Oracle