current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems. It's 100% free, no registration required.
up vote 1 down vote favorite

In the shell script, I am calling a PLSQL 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 plsql block, I need to error out shell script which is running as a host concurrent program in oracle but it is not doing so.

I am new to the shell script and any help is really appreciated.

share|improve this question
    
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 Answer 1

up vote 1 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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