Home » SQL & PL/SQL » SQL & PL/SQL » Column Heading in The Output
Column Heading in The Output [message #193048] Thu, 14 September 2006 12:26 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,
i have a shell script which runs and the output is kept in a txt file, i don't want column heading, here is what i tried
set head off
set feedback off
set pages 0
set echo off
set show off
set verify off 
SET TRIMSPOOL ON 
HEADING OFF 
sqlplus  -s scott/tiger@dec << END > /export/home/oracle/abd.txt
select distinct count(*) from bat;
spool off

It still shows column heading
set head off
set feedback off
set pages 0
set echo off
set show off
set verify off
 
SET TRIMSPOOL ON HEADING OFF PAGESIZE 100 LINESIZE 10 TAB OFF FEEDBACK OFF
sqlplus  -s r1app60/r1app@neor1l1 << END > /export/home/oracle/abd.txt
select distinct count(*) from bat;
spool off

What am i missing here.

Thanks

[Updated on: Thu, 14 September 2006 12:28]

Report message to a moderator

Re: Column Heading in The Output [message #193049 is a reply to message #193048] Thu, 14 September 2006 12:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
These SET commands will work only inside sqlplus shell.
Not outside.
Re-write the script as
#!/bin/bash
sqlplus -s scott/tiger@dec <<EOF
set head off;
..
..Alltherest
..
this sql query;
spool off
EOF

Re: Column Heading in The Output [message #193050 is a reply to message #193049] Thu, 14 September 2006 12:43 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks a lot Mahesh,

It works but at last after the result it gives as
" not spooling currently " in the txt file.

How can i avoid it.

Thanks Again
Re: Column Heading in The Output [message #193051 is a reply to message #193050] Thu, 14 September 2006 12:46 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You are already directing output to /export/home/oracle/abd.txt, so you don't need the spool off command.
Re: Column Heading in The Output [message #193052 is a reply to message #193050] Thu, 14 September 2006 12:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> " not spooling currently " in the txt file.
Well its true. Because you are not spooling anywhere in that script!. You need to say explicitly where you want to spool the file.
#!/bin/bash
sqlplus -s scott/tiger@dec <<EOF
set head off;
..
..Alltherest
..
spool /thislocation/thisfile.log
this sql query;
spool off
exit;
EOF

and EBrian's method should also work.

[Updated on: Thu, 14 September 2006 12:50]

Report message to a moderator

icon14.gif  Re: Column Heading in The Output [message #193053 is a reply to message #193052] Thu, 14 September 2006 12:48 Go to previous message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Mahesh and Ebrian it worked
Previous Topic: SQL select
Next Topic: Adding dummy value to list of selected values
Goto Forum:
  


Current Time: Thu Dec 08 20:30:54 CST 2016

Total time taken to generate the page: 0.17034 seconds