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.)
evalor run the script in a different manner —execwill substitute that shell with the call, so exit can never be reached. And to only exit on error, assuming plsql has sane exit codes, use|| exitat the end of the call. It will still probably be moot, since it will only exit the current shell and you seem to want one more.