Return value of whenever sqlerror

From: Leon <leond_at_pepkorit.com>
Date: 18 Oct 2006 06:06:26 -0700
Message-ID: <1161176786.733869.48360@b28g2000cwb.googlegroups.com>

Hi

I am executing a unic script whihc in turn executes an Oracle stored proc. I am failing the stored proc and using whenever sqlerror in the unix script to trap success. I cannot understand why the return value from the execution of the stored proc contains the entire file listing of my unix directory on failure and on success the PL/SQL procedure successfully completed.

My unix script is:

#!/bin/ksh
# first set up all relevant environment variables.

 MyResult1=`sqlplus -silent $uid/$passwd <<-sqlEOF

            set serveroutput on
            whenever sqlerror exit failure
            begin
              mms.test_unix_success;
            end;
           /
           exit
           sqlEOF`

 if [ $? = 0 ]; then
  echo 'Success '$MyResult1
 else
  echo 'Failure '$MyResult1
 fi

Success output is :

Success PL/SQL procedure successfully completed. I cannot get rid of the PL/SQL procedure... part

Then on failure the output is:

Failure begin DEADJOE core script_test_unix_ret_value.ksh test.ksh test.ksh~ test.txt test.txt~ test_proc.ksh test_unix.sql test_unix.sql~ ERROR at line 1: ORA-20900: Current job running ORA-06512: at "MMS.TEST_UNIX_FAILURE", line 8 ORA-06512: at line 2

The content of my unix directory precedes the entire failure message generated from my stored proc.

Any ideas on how to get rid of excess message content and only return a valid code from within the stored proc.

Thanks Received on Wed Oct 18 2006 - 08:06:26 CDT