Column Headings using sqlplus script not working

#1

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

#2

Roger wrote:

Quote:

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

#3

Roger wrote:

Quote:

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

A question: do you expect csv output?

#4

On Jan 11, 3:06*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:

Quote:

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 Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

#5

On Jan 11, 3:18*pm, Charles Hooper <hooperc2... (AT) gmail (DOT) com> wrote:

Quote:

On Jan 11, 3:06*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) 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

#6

On Sun, 15 Jan 2012 19:39:58 -0800, onedbguru wrote:

Quote:

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

Maybe in that case the script should start with "#!/usr/bin/perl -w"?

--
http://mgogala.byethost5.com

#7

On Jan 15, 11:50*pm, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:

Quote:

On Sun, 15 Jan 2012 19:39:58 -0800, onedbguru wrote:
If you are expecting more than 50K records you can modify your script
to:

Maybe in that case the script should start with "#!/usr/bin/perl -w"?

--http://mgogala.byethost5.com

except that this is a sql script and not a perl script...

???

#8

On Mon, 16 Jan 2012 05:44:57 -0800, onedbguru wrote:

Quote:

except that this is a sql script and not a perl script... ???

My personal preference for retrieving 50,000 rows would be a Perl script.
I am not sure what would "sql script" do with the output?

--
http://mgogala.byethost5.com

#9

On Jan 16, 7:38*am, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:

Quote:

On Mon, 16 Jan 2012 05:44:57 -0800, onedbguru wrote:
except that this is a sql script and not a perl script... *???

My personal preference for retrieving 50,000 rows would be a Perl script.
I am not sure what would "sql script" do with the output?

--http://mgogala.byethost5.com

If Excel is going to be involved, I suspect about 15535 more rows
might start having issues.

jg
--
@home.com is bogus.

#10

On Mon, 16 Jan 2012 08:42:16 -0800, joel garry wrote:

Quote:

If Excel is going to be involved, I suspect about 15535 more rows might
start having issues.

There are some things in life, like using Excel, writing "smart" scripts
in sqlplus and doing a root canal, that I am trying to avoid as much as
possible.

--
http://mgogala.byethost5.com