Re: Using SQL output in shell scripts

I appreciate it. That's exactly what I've been looking for.

...Mike

"billiauk" <billiauk_at_yahoo.co.uk> wrote in message news:dee17a9f.0201070527.322ed687_at_posting.google.com...
> 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 - 15:30:38 CET