0

I am using a shell script to 'spool' a query. Here is a toy version:

#!/bin/sh
sqlplus -s userid/pass@SID << EOF

  set echo off
  set term off
  set trims on
  set pages 0
  set feedback off
  set linesize 1000
  set colsep "|"

SPOOL $2
SELECT 'HEADER1|HEADER2|HEADER3' FROM DUAL
UNION ALL
SELECT
COLUMN1||'|'||
COLUMN2||'|'||
COLUMN3
FROM $1;
SPOOL OFF

EXIT 0;
EOF

And submitting using 

nohup sh sqlquery.sh intable outtable > log &

The query runs fine and is formatted exactly how I would like, but the rows returned by the query are written to both the spool file and the log... I thought 'set echo off' would take care of this, but I am obviously missing something.

Any ideas?

mlegge
  • 6,763
  • 3
  • 40
  • 67
  • 1
    Spool just saves a *copy* to the log file, it'll still output the results on screen as well (which is why it's showing up twice). `set echo off` just hides the SQL commands from showing, not the results. – Mr. Llama Jun 18 '14 at 20:17
  • You might want to check [this](http://stackoverflow.com/questions/6813210/oracle-sqlplus-saving-output-in-a-file-but-dont-show-on-screen) out. – vadimbog Jun 18 '14 at 20:20
  • @vadimbog I have 'set term off' which is an abbreviation: SET TERM[OUT] {ON | OFF} - Controls the display of output generated by commands executed from a script. From [here](http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm#i2698970) – mlegge Jun 18 '14 at 20:31
  • Another suggestion to the script you have: make sure parameter #2 is provided and also add `;` after `SPOOL $2` as well after `FROM $1` and after `SPOOL OFF` – vadimbog Jun 18 '14 at 21:06

0 Answers0