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 have a query that hits multiple tables in an Oracle database (11g). I don't have access to the server itself. I use SQL Developer or SQL*Plus to connect.

There are about 31 million rows, and 7 columns, and I want to dump it into a CSV file.

How can I achieve this?

share|improve this question
    
my recommendation - install oracle express on your pc, export the data from the unprivileged machine and import into your local environment. –  haki May 3 '13 at 19:58
3  
What are you going to use the csv for? In other words, what are your goals for the data? –  thursdaysgeek May 3 '13 at 23:15

2 Answers 2

Asuming this is to transport data to an other system. It that case this will work:

set colsep ";"
set linesize 9999
set trimspool on
set heading off
set pagesize 0
set wrap off
set feedback off
set newpage 0
set arraysize 5000
spool you csv_file.csv
select rows from your tables;
spool off

If you don't want a header line, change to heading off

If this is to analyze the data in excel, please wake up and learn sql. SQL, especially Oracle, has many great tools to help analyzing your data. By the time the data is transported into the csv file, Oracle analytics has already done some great analysis.Data Warehousing and Business Intelligence but for many tasks plain SQL can be good enough. Start reading about Oracle Analytics.

share|improve this answer
1  
I'm not a fan of these abbreviated SQL*Plus formatting commands -- PAGESIZE and PAGES sound like they mean different things to me, although the latter is just an abbreviation for the former. PAGESIZE 9999 and HEADING ON don't sound like good choices. –  David Aldridge May 4 '13 at 9:44
    
@David-Aldridge yes, you are right, would be nice to be able to dump just 1 header instead of for every page. Max pages size 50000 rows still gives lots of headers. –  ik_zelf May 4 '13 at 10:18
1  
Or no headers at all ... pagesize 0? –  David Aldridge May 4 '13 at 12:03

I'd look at writing a query to produce the comma separated values, taking care to string out any extraneous carriage returns or other funnies that would break the format, especially if you have columns that are free-form text entry and might have all sorts of entries in them. I once saw data for "email_content" that had an example of comma-separated values pasted into it. That was fun.

Your code would end up along the lines of ....

select
  to_char(my_date,'YYYY-MM-DD HH24:MI:SS')
  ||','  ||to_char(my_number,'fm999999999999990.00000')
  ||',"' ||regexp_replace(my_string,'[[:cntrl]]','')
  ||'",' ||to_char(my_number,'fm999999999999990.00000')
from
  my_table;

Then either use SQL*Plus to spool the output, using the commands shown in the docs to suppress headings, set a suitable line size, suppress feedback of the number of rows, increase the default arraysize to around 100 perhaps, or use UTL_File to spool it out as a server process.

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.