1

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.

  • if that block is normal shell code, then you need to use eval or run the script in a different manner — exec will 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 || exit at 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. – lynxlynxlynx Sep 30 '13 at 20:27
  • Just an FYI, when dealing with usernames/passwords with sqlplus you should be using Oracle Wallets: docs.oracle.com/cd/E25054_01/network.1111/e16543/… – slm Oct 1 '13 at 0:02
1

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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.