Shell script output

[atltechie]

Question,

How do I prevent my shell script from printing the results of a sql statement from on the screen. the following is my script

#!/bin/ksh
sqlplus -s user/password << EOF
@/var/test1.sql
@/var/test2.sql
exit
EOF

In the sql files I am spooling to a file.

Thanks

 

[SamBones]

Try this...

   #!/bin/ksh
   sqlplus -s user/password << EOF
   @/var/test1.sql
   @/var/test2.sql
   exit
   EOF > myscript.log 2>&1

...or...

   #!/bin/ksh

   LOGFILE=myscript_$(date +'%Y%m%d%H%M').log

   # Redirect stdout and stderr to log file
   exec 1> ${LOGFILE}
   exec 2>&1

   sqlplus -s user/password << EOF
   @/var/test1.sql
   @/var/test2.sql
   exit
   EOF

Hope this helps.

 

[atltechie]

Thanks....it worked!!!!! I had another question can I add the date and time to the log file the sql file is spooling to?

spool products;

SELECT * FROM PRODUCTS;

spool off;

 

[SamBones]

Yeah, you'd have to do it something like...

   #!/bin/ksh

   TIMESTAMP=$(date +'%Y%m%d%H%M')
   export LOGFILE=myscript_${TIMESTAMP}.log
   export LISFILE=myscript_${TIMESTAMP}.lis

   # Redirect stdout and stderr to log file
   exec 1> ${LOGFILE}
   exec 2>&1

   sqlplus -s user/password <<-SQLCMDS
      spool ${LISFILE};

      @/var/test1.sql
      @/var/test2.sql

      spool off
      exit
   SQLCMDS

The [tt]spool[/tt] command would need to be done in this script, and not the ones called with the &quot;@&quot;. SQLPlus doesn't pass the environment variable to the called SQL script.

Also notice I added a dash (&quot;-&quot to the &quot;<<-SQLCMDS&quot;. This lets you indent the following lines for readability, without breaking it. I also changed the &quot;EOF&quot; to &quot;SQLCMDS&quot; because EOF is kind of ambiguous. Sorry if I'm beeing too picky, but, well, that's just me.

Hope this helps.

 

[atltechie]

Thanks for your help!!

 

[atltechie]

In the above script is it possible have separate list files for each SQL output?

 

[Salem]

A guess

   sqlplus -s user/password <<-SQLCMDS
      spool ${LISFILE}_1;
      @/var/test1.sql
      spool off
      spool ${LISFILE}_2;
      @/var/test2.sql
      spool off
      exit
   SQLCMDS

 

[SamBones]

Salem's got it!

Go Salem!