Re: Using SQL output in shell scripts

There are many ways of getting SQL results into a shell...

To answer your question, you want to capture SQL*Plus output into a shell variable, as follows:-

COUNT=$(sqlplus -s orausername <<END
orapassword
SET PAGES 0 FEEDBACK OFF FLUSH OFF VERIFY OFF HEADING OFF ECHO OFF WHENEVER SQLERROR EXIT 12
WHENEVER OSERROR EXIT 13
<your SQL string here>;
EXIT;
END) if [[ ${COUNT} -gt ${THRESHOLD_VALUE} ]] then

   do your paging...
   exit 10
else

   blah blah...
fi

I usually put the SQL string into a variable first to avoid shell substitution problems:-

e.g.

SQL_STR="SELECT username FROM v$session;" #(note the use of a $ which is special

then in my SQL*Plus call, simply:-

COUNT=$(sqlplus -s orausername <<END
orapassword
SET PAGES 0 FEEDBACK OFF FLUSH OFF VERIFY OFF HEADING OFF ECHO OFF WHENEVER SQLERROR EXIT 12
WHENEVER OSERROR EXIT 13
${SQL_STR}
EXIT;
END) Hope this helps. BTW, this is Korn 92.

AB Received on Mon Jan 07 2002 - 14:27:50 CET