Re: Return value of whenever sqlerror

Leon wrote:
> 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

Your UNIX script should be:

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

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

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

 status=$?
 if [ $status -eq 0 ]; then
  echo "Success $MyResult1"
 else
  echo "Failure: exit status $status: $MyResult1"  fi

"=" is not a valid operator for numeric comparisons with test ( the [] construct) as that is a string comparison operator. The numeric operators are:

-eq               equal to
-ne               not equal
-gt                greater rhan
-lt                 less than
-ge               greater than or equal to
-le                less than or equal to

Also, $? is reset after a test; to use the value again assign it to a variable then use the variable in the test construct as shown.

The script should work as you expect, and you shouldn't see any feedback from Oracle outside of error messages.

David Fitzjarrell Received on Wed Oct 18 2006 - 10:05:41 CDT