Help with storing the output of multiple sql queries to a file

Hi All,

I have a file queries.txt as follows :

SELECT COLUMN1 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN1 FROM SCDEMA2.TABLE2;
SELECT COLUMN2 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN2 FROM SCDEMA2.TABLE2;
SELECT COLUMN3 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN3 FROM SCDEMA2.TABLE2;
SELECT COLUMN4 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN4 FROM SCDEMA2.TABLE2;

Now I need to run all the queries in queries.txt and store the output of them in a file result.txt in the following format :

SELECT COLUMN1 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN1 FROM SCDEMA2.TABLE2; 0
SELECT COLUMN2 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN2 FROM SCDEMA2.TABLE2; 0
SELECT COLUMN3 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN3 FROM SCDEMA2.TABLE2; 0
SELECT COLUMN4 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN4 FROM SCDEMA2.TABLE2; 0

Now, as all the queries are minus queries (expected result : No Rows Returned), if now rows are returned, I should be able to write '0' instead of the actual output (which I haven't figured out yet)

This is what I have tried so far :

while read line; do
sqlplus $user/$pass << EOF >> $LOGDIR/$0.log
...
...
SET DEFINE OFF;
...
spool $SCRIPTDIR/result.txt
$line
spool off;
EXIT;
EOF
done < /ogc23788/scripts/sql_input.txt 

When I open the result.txt, it is as follows :

 
02:34:04 SQL> SELECT 1+1 FROM DUAL;
       1+1
----------
         2
Elapsed: 00:00:00.01
02:34:04 SQL> spool off;

Note : I tried with a sample dummy query.

Now my concern is, is there any way, at all, with which I can save the ouput as '0' when 'No Rows Returned' and '1' when some rows returned?
Also how to append multiple outputs to a single file(in a format I have mentioned above)

Cheers.

I'm not sure how the SELECT 1+1 FROM DUAL; example illustrates the issue. What I might suggest is that you use a -S flag on your sqlplus command to get rid of much of the output that would be written to $0.log and then within the SQL code, put in some definitions such as:-

SET PAGESIZE 0
SET HEADING OFF
SET TIMING OFF

....which will minimise the output. You may be better running the queries in a shell loop calling sqlplus several times and writing the output to a file, then manipulating the result, something like this:-

while read line
do
      sqlplus -S << EOF >> $0.log
      $user/$pass
      ...
      ...
      SET DEFINE OFF;
      ...
      spool /tmp/result.txt
      $line
      spool off;
      EXIT;
      EOF

      read value < /tmp/result.txt
      if [ "$value" = "no rows returned" ]
      then
         value=0
      fi

      print "$line $value"
      rm /tmp/results.txt
done < /ogc23788/scripts/sql_input.txt > $SCRIPTDIR/result.txt

Does that help, or have I missed the point completely?

If you are getting confusing output, can you run it with just one input line and don't remove /tmp/results.txt? If you can then share what is in that temporary file, then we can work on cleaning it up.

Robin
Lancashire, UK

Your advice on definitions helped a lot! I used it in my code and the following code got me what exactly I wanted :

#!/...
output=$PATH/output.txt
...
echo "QUERY|RESULT"> $output
while read line; do
count=$(sqlplus -s $user/$pass << EOS
set heading off
set feedback off
whenever sqlerror exit 1
SELECT COUNT(*) FROM ($line);
quit;
EOS)
echo "$query|$count" >> $output
done < /path/input_queries.txt

Thanks for taking the time out for wonderfully explaining the solution.

Cheers.