Check for “errors” or “ORA-”

Shell Programming and Scripting
1

I want to check for "errors" or "ORA-" in Y.if there is an error then exit

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

if [ echo $Y | awk '/ERROR/ || /ORA-/' ] ; then
exit 1
fi

but this doesnt work

2

Try:

case $Y in
  (*ERROR*|*ORA-*) exit 1
esac
3

I would recommend checking inside test_proc with exit :exitcode variable definition inside sql blocks.

Here is a post to related check in shell :
http://www.unix.com/302940804-post8.html

Hope that helps
Regards
Peasant.

4

Hi Haadiya,

sqlplus -s user/passwd <<EOF
whenever sqlerror exit sql.sqlcode;
exec test_Proc;
exit;
EOF

RetCode=$?

#Check the return code from SQL Plus
if [ $RetCode != 0 ]
then
    echo "********************"
    echo "ERROR: The SQL Plus Command Failed. RetCode: $RetCode"
else
    echo "********************"
    echo "SQL Plus Successfully Ran. RetCode: $RetCode"
fi

Thanks
Pravin

5

You can also try the following. Log to a file and use grep to look for errors.

if [ `egrep -c "ORA-|SP2-|PLS-|TNS-|UDI-" sql_log_file_name.log` > 0 ]
then
    echo "********************"
    echo "ERROR: The SQL Plus Command Failed."
    egrep "ORA-|SP2-|PLS-|TNS-|UDI-" sql_log_file_name.log
else
    echo "********************"
    echo "SQL Plus Successfully Ran."
fi
6

�Ay ay ay, caramba! Reading the log file twice?

7

Yep! CPU cycles are cheap, and for me the log files tend to be small.
Now, if I had to pay for every clock cycle.... :wink: