Re: Whenever SQLERROR - does it work?

ET wrote:

...

>>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;

>>

>.....

Or you could try this
#!/bin/ksh

sqlvalue="`sqlplus -s /NOLOG<<EOF
-- Return error on connection
whenever sqlerror exit FAIL;
set heading off
set feedback off
set verify off
set echo off
SET PROMPT OFF
CONNECT sys/$SYS_PASS
select count(sid)
from v\\$session vs
where vs.program like 'OEOSCO';
exit SUCCESS
EOF`"
echo "Sqlvalue=$sqlvalue"

The important thing is to have your error checking set up before you attempt to connect to the database.
If you don't specify /NOLOG on the command line the very first thing sqlplus tries to do is attempt to connect to a database.

Also don't on error don't set the return value to be 0. By default on unix this is the success return value and (I think) is the value set EXIT SUCCESS
. Received on Sun Feb 03 2002 - 22:55:51 CST