Sqlplus in silent mode is not silent

Question

I'm using sqlplus in shell script and it works(sqlplus without silent mode) but I can see the output in the terminal. I tried running it in silent mode with different combinations but it didn't work -

sqlplus -s "$DBUSER/$DBPWD@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=$DBHOST)(Port=$DBPORT))(CONNECT_DATA=(SID=$DBSID)))"  @$SCRHOME/getCsv$sqlFile.sql

<Btw the -s is in yellow and not black, like the echo command in a shell script>

It works but I can see the output on console. I also tried:

sqlplus \-s "....
sqlplus \-s \ "...
sqplus -S
sqlplus -s \ << EOF "...

I tried removing the double quotes, but then in that case even the sqlplus command doesn't work. I'm using this command in a shell script.

Answer 1

The correct format is:

sqlplus -S LOGIN_INFO @SCRIPT_TO_RUN

The "silent" mode doesn't prevent terminal output. All it does is:

-S             Sets silent mode which suppresses the display of
               the SQL*Plus banner, prompts, and echoing of
               commands.

If you want to suppress all terminal output, then you'll need to do something like:
sqlplus ... > /dev/null 2>&1

Answer 2

If you want to suppress the terminal output, it is possible by using spool in your SQLPlus script and diverting the terminal output to /dev/null. Here's how I did it:

Firstly the SQLPlus:

spool ${HOME}/output.dat;
select
... <your query here> ...
;
spool off;

Suppose this is placed in the file $HOME/my.sql. This could be invoked in a bash script as follows:

echo "$(sqlplus -s /nolog << EOF
CONNECT <your DB connnect string>
whenever sqlerror exit sql.sqlcode;
set echo off
@${HOME}/my.sql
exit;
EOF) " > /dev/null

The output of the query will be sent to ${HOME}/output.dat and no SQLPlus output will go to your terminal (or log file as it was in my case).