Need to return fail or pass from shell script on the basis of pl/sql code execution

Shell Programming and Scripting
1

Hi guys,

I am quite new in shell scripting.

I am tring to promote some oracle jobs into control-M. In control-M, I am calling a script which establishes a connection with database and execute some procedures. Now I want if that PL/sql Block got failed [any ora error] script should return failure to control-M.

Y=`sqlplus -s $user/$password@$database <<EOF
WHENEVER SQLERROR EXIT FAILURE
exec TEST_WRITEs;
EXIT;
EOF`
if [ $Y -eq 1 ]
then
exit 1 #Return Failure
else
exit 0 #Return Success
fi

I wrote the above script but it's not working. Could you please suggest how could I achieve the same.

Thanks in advance.

Regars,
ALok

2

Y can't u simply grep for ERROR or *ORA* in Y.

Something like this

Y=`sqlplus -s jvsh/jvsh <<  EOF
exec test_Proc;
exit;
EOF`

echo $Y | awk '/ERROR/ { print "Errror" } '
3

Hi Panyam,

Thanks for your prompt reply. You are right it's working fine .

Thanks,
Alok