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 ================================================ SELECT * FROM tbl1; 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.