UNIX for Dummies Questions & Answers

Hi,
I currently have a shell script where it captures in the value in to 'TOTAL' and outputs to a file, i want to capture another value of a query in to another variable and output it.

Current script:

sqlplus -s $user <<EOD | read TOTAL
set heading off
set pages 0
set feedback off
set verify off
set trimout on
set colsep ""
set tab off
select count(*) from staging;
quit;
EOD

echo "Total recs came in the file - ($TOTAL) \n" >> $count


I am trying the following:

sqlplus -s $user <<EOD | read TOTAL TOTAL__AMT
set heading off
set pages 0
set feedback off
set verify off
set trimout on
set colsep ""
set tab off
select count(*) from staging;
select sum(amt) from staging;
quit;
EOD

echo "Total recs came in the file - ($TOTAL) - ($TOTAL_AMT)\n" >> $count

-----------------

My idea is to read the figure in to the new variable as we are already invoking sql plus, Unable to achieve the above, can someone tell me if its even possible, if its not, then the alternative is to write all the chunk again for capturing it in to new variable(TOTAL__AMT)....any suggestion would be appreciated.


thanks,
R

Why not use sql to do what you want:

Code:

echo "
set pages 0
set feedback off
set verify off
set trimout on
set colsep ""
set tab off
SELECT 
(select count(*) from staging ) - 
(select sum(amt) from staging) 
FROM DUAL;
exit
" | sqlplus -s user/pwsd@somedb | read TOTAL