Column Headings using sqlplus script not working

[Roger]

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

[Gerard H. Pille]

because of the "set pagesize 0"

that is why

[Gerard H. Pille]

A question: do you expect csv output?

[Charles Hooper]

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.

[onedbguru]

> 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

[Mladen Gogala]

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

[onedbguru]

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

[Mladen Gogala]

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

[joel garry]

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

jg
--
@home.com is bogus.

[Mladen Gogala]

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


> 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

[onedbguru]

Really, root canals are not all that bad - having had a couple.. I
just tell the dentist wake me up when he gets done. And, No, I do
not use "sedation dentistry" - just plain ole Novocaine. :) lol...

The "script" provided does nothing more than create a csv with a
filename of paymastr3.csv. I suspect that it may get sent to another
system and imported. I have moved data to "load test" systems in this
manner. I have also used it to copy the file to MS-bbbarrrfffSQL-
bbbarrf-serBARRRRFver.

[p.pan...@gmail.com]

Hello

I was using below in a script but still getting the sql query with the output in a spool file.

set feedback off
set heading off
SET TERM OFF
set echo off
set tab off
set serveroutput off
set show off
set trimspool on
set pagesize 0

Please help on this how to fix it.

[ddf]

Showing us just that doesn't allow us to help you. Post the script.


David Fitzjarrell

[anuragk...@gmail.com]

in this script use to_date('30-Dec-11','DD-MON-RR') in condition
also set pagesize 50 as usual