Cfrtjryk

In the shell script, I am calling a PL/SQL stored procedure.

sqlplus -s <<EOF

$v_fcp_login

set head off feed off serverout on size 1000000

exec XXIRIS_TEST_K.XXIRIS_TEST('$v_1',$v_2,$v_3,'$v_4',$v_5);

exit

EOF

But when there is an error in the PL/SQL block, I need to error out shell script, which is running as a host concurrent program in Oracle, but it is not doing so.

First, you need to get SQL*Plus to error out if a SQL error occurs. You can do this by adding:

WHENEVER SQLERROR EXIT FAILURE

to your SQL script (probably up top). You can also give different codes (small non-negative integers; non-zero = failure) in place of the word FAILURE.

These will come back to your shell script in $?. So you can then have your shell script react to it. Altogether, it'd look something like this:

sqlplus -s <<EOF

$v_fcp_login

set head off feed off serverout on size 1000000

WHENEVER SQLERROR EXIT FAILURE

exec XXIRIS_TEST_K.XXIRIS_TEST('$v_1',$v_2,$v_3,'$v_4',$v_5);

exit

EOF

if [ 0 -ne "$?" ]; then

    echo "Stored proc blew up." >&2

    exit 1

fi

Of course, you can use different exit codes to indicate different things (e.g., if you were calling multiple stored procedures, you could exit 1 for the first one blowing up, exit 2 for the second, etc.)