Spool file requirement

Shell Programming and Scripting
1

Hi,
I have a requirement of

  1. Connecting to sqlplus from unix
  2. Execute the package. The output of package is stored in a table
  3. Need to query the table and move to txt file.

The problem iam facing is, when I try to spool the file. I get the sql query also along with the output. Spooled file looks like below:

 SQL> select col1 || ',' || col2 from test;
  
 ab,cd
 ef,gh
  
 SQL> spool off;

I don't want to see these sql statements. I used set trimout on, set echo off. But sql statement is not removed. Kindly help

2

You would want to try the following:-

set feedback off
set heading off

How are you calling the sqlplus command? Can you share your script? Please wrap it in CODE tags. I fancy there is a flag required on the sqlplus call itself.

Robin

3

Is that oracle? Or what? Why shouldn't set echo off work?

4

Not sure how you are calling sqlplus, but sqlplus -s will put it in silent mode and remove everything apart from the result

5

my code is as below:

 sqlplus username/pwd@sid <<EOF
 exec pkgname(in parameter);
 set heading off
 set echo off
 spool test_output.txt
 select col1 || ',' || grp_cd from test;
 spool off;
 exit
 EOF

---------- Post updated at 11:27 AM ---------- Previous update was at 11:21 AM ----------

I tried with sqlplus -s and it removed the sql commands

however output now is as below

 ab,cd
 ef,gh
  
 2 rows selected

****I don't want this 2 rows selected to come, how do I avoid this.

6

Please wrap your code fragments or data samples in code tags

Try this:-

sqlplus -s username/pwd@sid << EOF
exec pkgname(in parameter);
set echo off head off feed off pagesize 0 trimspool on linesize 1000
spool test_output.txt
select col1 || ',' || grp_cd from test;
spool off;
exit
EOF
7

Thank you very much. It worked finally :slight_smile: