Need to capture Connect error 1

[prismtx]

I have a script which calls SQLPLUS and passes the login information. It is working great, but if the database is down or if it passes a bad parameter, the connect does not work, but I still get a return code of zero. Is there a way to capture when there is a connect error and exit with a return code?

This is what i have now:

set WHENEVER SQLERROR exit 1
set WHENEVER OSERROR exit 1
set serveroutput on size 100000
sqlplus /nolog <<EOF
connect $USERNAME/$PASSWORD@$1
select * from table1;
exit
EOF
if [ $? != 0 ]
then
  echo "NOT CONNECTED TO SQL"
  exit 1
fi
echo "Return Code = $?"

But I get these messages:

SQL> ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
SQL> SQL> Return Code = 0

 

[hoinz]

It seems you are mixing Unix shell and PL/SQL syntax.

Put your three lines 'set ...' between 'sqlplus ...' and 'connect ...',
and replace 'set WHENEVER' by 'WHENEVER'

hope this helps

 

[prismtx]

Thanks hoinz, moving those lines after sqlplus worked (and removing the set on the WHENEVER's)

It is a sqlplus call embedded in a unix script and now I am capturing the error as I had hoped.