Hi all,
I am executing a Oracle SQL statement in a shell script and spooling the output of the query into a File with spool command.
I want to ensure that only output of the query appears in file excluding the SQL statement but even set echo off command is not working.
Please help

Here's what I do...

sqlplus -s /nolog <<-EOF
   whenever oserror exit failure
   whenever sqlerror exit failure rollback
   set heading off pagesize 0 feedback off linesize 400
   set trimout on trimspool on termout off echo off sqlprompt ''

   connect $SQLUSER/$SQLPWD@$DB

   spool $spoolfile

   select * from mytable;

   spool off

   exit

EOF

Hi Jsmithstl,
I am using spool command to write the output of the query in a file
But Query appears in the file along with the Query output.
I just want the output of the query in the file.
Ex: this is what currently apperas in the file test.txt=>
SELECT COUNT(*) FROM USER_TAB_COLUMNS RET WHERE RET.COLUMN_NAME ='EMPLOYEE_ID';

4

spool off;

But what is want the file to contain is just the output of the above written query i.e: 4
How do i go about it using spool command as even the statement echo off doesnt seem to work

Please post the code you're using.

i tried the script attached by you.
And it worked thanks a lot!!!!!!!!
By the way Jsmithstl i would be highly obliged if you can tell me what part of the code provided by you prevented the query from being printed in the spooled file.
Please do reply to this ping cause it would be really helpful

It works for me:

CODE:

$ cat tsql.ksh
#!/usr/bin/ksh

. ~/.profile

SQLUSER=xxxxxxx
SQLPWD=xxxxxxx
DB=mydb

SPOOLFILE=blah.out

sqlplus -s /nolog <<-EOF
   whenever oserror exit failure
   whenever sqlerror exit failure rollback
   set heading off pagesize 0 feedback off linesize 400
   set trimout on trimspool on termout off echo off sqlprompt ''

   connect $SQLUSER/$SQLPWD@$DB

   spool $SPOOLFILE

   select 'I only see my query output' from dual;

   spool off

   exit

EOF

exit

$ ./tsql.ksh
I only see my query output

$ cat blah.out
I only see my query output

$

   set heading off pagesize 0 feedback off linesize 400
   set trimout on trimspool on termout off echo off sqlprompt ''

Hi Smith,
Sorry for the late response.
First things first i need to thank you for the timely help.
Yes you are write it works and the reason for the same is cause sqlplus runs in silent mode i.e. sqlplus -S[ilent] which printing of all command execution steps are supressed though one thing still baffles me why doesnt set echo off command work????

Again thanks for your quick response and hope that i would prove to you all of some help in future.