This question already has an answer here:

I am trying to write a script that will output a table to a CSV. The tricky part has been to get the output to only show the header once. The script below does not show the header at all. The other option we have used is is setting the pagesize extremely high. That has also been very problematic.

set colsep |
set pagesize 0
set trimspool on
set linesize 10000

spool table1.csv
SELECT host_name from v$instance;
select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "CURRENT TIME" from dual;
prompt ================================================
prompt    TABLE 1
prompt    SELECT * FROM tbl1;
prompt ================================================
spool off

For clarity: I do not have direct access to the database. I will be passing the script to a DBA to run against the database, so PL/SQL or anything else more advanced than a very basic script is probably not an option. Also because of this, I want the column headers to show ONCE and only once whether there are 10 or 10 million records in the table. This will give me reasonable descriptions of data that I need and minimize the cleanup before analyzing the data.

marked as duplicate by RolandoMySQLDBA, Mark Storey-Smith, Derek Downey, Kin Shah, Aaron Bertrand Jun 5 '14 at 3:00

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • My question is slightly different from the thread "How can I unload Oracle database tables into a csv files?" Mainly I need the head to show once and ONLY once, and I am not a DBA on the databases I need to pull. – Andrew Jun 3 '14 at 12:30
  • Then maybe your DBA should be involved? Saying that PL/SQL is not an option is kind of a non-starter. – Aaron Bertrand Jun 3 '14 at 13:58

You could run two queries - the first pulls the list of field names in tbl1 from user_tables and then the second query then pulls the actual data from tbl1? Spool all to the same file. Don't have a running instance of Oracle to test, but that may be a solution to your issue? Removes the need for PL/SQL - although I fail to see the difference between running a shell script and a simple PL/SQL one.

  • Yes, this is one of the solutions that occurred to me, but it is not as elegant as I would like. I suppose I will have to suck it up. Thanks for the help. – Andrew Jun 3 '14 at 18:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.