Re: Return value of whenever sqlerror

From: Radoulov, Dimitre <_at_>
Date: Wed, 18 Oct 2006 15:25:42 +0200
Message-ID: <45362b55$0$49202$14726298@news.sunsite.dk>

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

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

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

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

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

1. Set feed off to get rid of "PL/SQL procedure successfully completed"
2. Use double quotes for shell variables

Regards
Dimitre Received on Wed Oct 18 2006 - 08:25:42 CDT