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.