Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

4 Answers 4

up vote 9 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 SQL*Plus. It's actually a script that generates another script. Bizarre, but it gets around certain limitations of SQL*Plus. I think it's still floating around on the web somewhere. –  Walter Mitty Feb 25 '12 at 12:22

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.

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 at 15:49

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

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

 
discard

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.