Help supressing spool output from screen when calling sqlplus from script

Shell Programming and Scripting
1

I'm calling an embedded sql from my shell script file. This sql does simple task of spooling out the contents of the table (see below my sample code) into a spool file that I specify. So far so good, but the problem is that the output is also displayed on screen which I do NOT want.

How can I suppress the output from being displayed on screen while ensuring it still goes to the spool file.

...
...
sqlplus -s ${ORA_CONN_STR} <<EOF

SET TERMOUT OFF
SET HEADING OFF
SET PAGESIZE 50000
SET LINESIZE 500
SET TRIMSPOOL OFF
SET WRAP OFF
SET FEEDBACK OFF
SET ECHO OFF

SPOOL ${CSV_DIR_FILE}

SELECT 'My header text here'
   FROM dual;
   
SELECT TRIM (field1) || ',' ||
       TRIM (field1) || ',' ||
       TRIM (field2) || ',' ||
       TRIM (field3) || ',' ||
       TRIM (field4)
       ....
   FROM <tablename>;
   
EOF
...
...
2

U can use :

sqlplus -s ${ORA_CONN_STR} <<EOF >/dev/null
1 Like
3

Bulls eye .. Thanks a lot!

4

Alternatively, you could redirect to your log file instead of /dev/null.
And not use the SPOOL command explicitly.
Like so -

$
$
$ cat tst.sh
#!/usr/bin/bash
ORA_CONN_STR="test/test"
CSV_DIR_FILE="tst.log"
sqlplus -s ${ORA_CONN_STR} <<EOF >${CSV_DIR_FILE}
SET TERMOUT OFF
SET HEADING OFF
SET PAGESIZE 50000
SET LINESIZE 500
SET TRIMSPOOL OFF
SET WRAP OFF
SET FEEDBACK OFF
SET ECHO OFF
SELECT 'My header text here'
  FROM dual;
SELECT c1 || ',' ||
       c1 || ',' ||
       c2 || ',' ||
       c3
   FROM t;
EXIT
EOF
$
$ ./tst.sh
$
$ cat tst.log
 
My header text here
 
1,1,10,a
2,2,20,b
3,3,30,c
4,4,40,d
5,5,50,e
$
$

tyler_durden

1 Like