Hi Tom,
I have a situation, where we call a shell sscript from a third party scheduling tool. The shell script further calls a DB procedure. We put an exception in DB procedure to return sqlerrm with raise_application_error function. When we run procedure through shell script, if it fails, we get return code ($?) success, thus scheduling toll indicates - process completed succussfully. Is there any way, when a procedure fails, I get return status <> 0 in the calling shell script?
Thanks

ops$tkyte@ORA10GR1> whenever
Usage: WHENEVER SQLERROR
     { CONTINUE  [ COMMIT | ROLLBACK | NONE ]
     | EXIT  [ SUCCESS | FAILURE | WARNING | n | <variable> | :<bindvariable> ]
             [ COMMIT | ROLLBACK ] }
 
Usage: WHENEVER OSERROR
     { CONTINUE  [ COMMIT | ROLLBACK | NONE ]  |  EXIT [ SUCCESS | FAILURE |
         WARNING | n | <variable> | :<bindvariable> | OSCODE ]
             [ COMMIT | ROLLBACK ] }
ops$tkyte@ORA10GR1>


<b>
SQL> whenever sqlerror exit FAILURE
</b>

 

Hi Tom,
I always like knowing new stuff from this site.

Unix variables can be used in PL/SQL as follows,
Example : t1.ksh
##
numeric_parameter=100
$ORACLE_HOME/bin/sqlplus /nolog << EOF
  connect $SCRIPT_ORA_USER/$DW$ORACLE_DB;
  set serveroutput on
  spool aa.txt
  declare
    v_oracle_home VARCHAR2(100) := '$ORACLE_HOME';
    v_numeric_parameter number := $numeric_parameter;
  begin
    dbms_output.put_line('oracle_home->'||v_oracle_home);
    dbms_output.put_line('numeric_parameter->'||v_numeric_parameter);
  end;
/
  spool off
exit
EOF
##

When executed : ksh t1.ksh 

----The output of this script will be similar to 
----the following :
oracle_home->/oracle/app/oracle/product/9.2.0
numeric_parameter->100

SQL>   spool off
----

Thanks.

Debjit