Re: Column Headings using sqlplus script not working

From: onedbguru <onedbguru_at_yahoo.com>
Date: Sun, 15 Jan 2012 19:39:58 -0800 (PST)
Message-ID: <b8148caf-7adc-4657-bc28-e5f16863e870_at_z19g2000vbe.googlegroups.com>

On Jan 11, 3:18 pm, Charles Hooper <hooperc2..._at_gmail.com> wrote:
> On Jan 11, 3:06 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
>
>
>
>
>
>
>
>
>
> > Roger wrote:
> > > Hello,
> > > Stupid question here. I'm having brain freeze.
> > > I have a real simple script that I run in sqlplus
>
> > > set colsep ','
> > > set HEAD ON
> > > set pagesize 0
> > > set trimspool on
> > > set linesize 1000
> > > set feedback off
> > > spool paymastr3.csv
> > > select * from paymastr
> > >    where check_date='30-Dec-11';
> > > spool off
>
> > > I can execute the sql script and the csv file is created, but NO
> > > column headings are appearing. I have tried everything, but still no
> > > headings. I've tried set HEADINGS ON but that doesn't work
>
> > > This is Oracle 9i
>
> > > Any ideas?
>
> > > Thanks,
> > > -R
>
> > because of the "set pagesize 0"
>
> > that is why
>
> Gerard is correct.
>
> See this page for more:http://gennick.com/html.html
> "50,000 lines is the largest PAGESIZE value that SQL*Plus supports.
> You can use SET PAGESIZE 0 to disable pagination completely, but then
> you don't get any column headings."
>
> Charles Hooperhttp://hoopercharles.wordpress.com/
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

If you are expecting more than 50K records you can modify your script to:

set colsep ','
set HEAD ON
set pagesize 0
set trimspool on
set linesize 1000
set feedback off
spool paymastr3.csv
select 'colname1,colname2,colname3,,,colnnamen' from dual; select <use explicit col list here ie: col1,col2,col3> from paymastr   where check_date='30-Dec-11';
spool off Received on Sun Jan 15 2012 - 21:39:58 CST