Re: Whenever SQLERROR - does it work?

Kirt Thomas <kremovethisspamthingthomas_at_gfsiinc.com> wrote in message news:<p7do4u4h9emg16cjh8t1jsbsleo2ul567a_at_4ax.com>...
> I have a shell script that checks for a given process, and if it's
> running does some stuff - here's a bare outline
>
> #!/bin/ksh
>
> sqlvalue="`sqlplus -s<<EOF
> sys/$SYS_PASS
> set heading off
> set feedback off
> set verify off
> set echo off
> select count(sid)
> from v\\$session vs
> where vs.program like 'OEOSCO';
> exit
> EOF`"
> echo "Sqlvalue=$sqlvalue"
>
> This all works just fine, and I get a count of the number of
> processes, and can do more stuff based on this (it ends up paging
> someone). The problem I have is that during our weekly db
> maintenance, this script (which is crontab'd to run every 10 minutes)
> returns tons of verbiage to the sqlvalue because the db is down.
>
> So I figured I'd just add a line
>
> whenever sqlerror exit 0;
>
> to force an exit with a value of zero on any sqlerror. Since it would
> not be good for my continuted employment to test this by bringing the
> db down, I just dropped the 'n' from v$session - to force an sql
> error. Instead of echoing '0' as expected I get the following ....
>
> Sqlvalue=from v$sessio vs
> *
> ERROR at line 2:
> ORA-00942: table or view does not exist
>
> Soooo, what gives? That is definately an sql error. Am I doing
> something wrong, or does whenever sqlerror not really work as I think
> it should. Thanks.

Try this,

#!/bin/sh

sqlplus << EOF > /dev/null
scott/tiger
whenever sqlerror exit 1
select 1 from no_such_table;
EOF sqlvalue=$?
echo $sqlvalue

Thanks,
Mike Received on Mon Jan 21 2002 - 14:24:09 CST