Script to Output Tables to CSV Showing Heading Only Once 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.
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.