UNIX for Advanced & Expert Users

SQL> help whenever

 WHENEVER OSERROR
 ----------------

 Performs the specified action (exits SQL*Plus by default) if an
 operating system error occurs (such as a file writing error).
 In iSQL*Plus, performs the specified action (stops the current
 script by default) and returns focus to the Workspace if an
 operating system error occurs.

 WHENEVER OSERROR {EXIT [SUCCESS|FAILURE|n|variable|:BindVariable]
                   [COMMIT|ROLLBACK] | CONTINUE [COMMIT|ROLLBACK|NONE]}


 WHENEVER SQLERROR
 -----------------

 Performs the specified action (exits SQL*Plus by default) if a
 SQL command or PL/SQL block generates an error.
 In iSQL*Plus, performs the specified action (stops the current
 script by default) and returns focus to the Workspace if a SQL
 command or PL/SQL block generates an error.

 WHENEVER SQLERROR {EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
                    [COMMIT|ROLLBACK] | CONTINUE [COMMIT|ROLLBACK|NONE]}

$ cat somesql.sh
sqlplus -s scott/tiger << !
  whenever sqlerror exit 2;
  select 1 from blah;
!

echo Exited with $?

$ ./somesql.sh

  select 1 from blah
                *
ERROR at line 1:
ORA-00942: table or view does not exist


Exited with 2

# Set some stuff up:
$ sqlplus -s scott/tiger

SQL> create table test1( a number(10));

Table created.

SQL> insert into test1 values( 1 ); -- (***) note the value!

1 row created.

SQL> commit;

Commit complete.

create or replace procedure testproc1 is
  X number;
begin
  select a into X from test1 where a = 1; -- (***)
exception
  when no_data_found then
    raise;
end;
/
create or replace procedure testproc2 is
  X number;
begin
  select a into X from test1 where a = 3; -- (xxx)
exception
  when no_data_found then
    raise;
end;
/
create or replace procedure testproc3 is
  X number;
begin
  select a into X from test1 where a = 4;
exception
  when no_data_found then
    raise;
end;
/

^D

$ cat SomeTest
sqlplus -s scott/tiger << !
whenever sqlerror exit 3;
select 'Test 1' from dual;
exec testproc1;               -- This works (*1 - it selects where value = 1 (***))
select 'Test 2' from dual;
exec testproc2;               -- This fails (*2) (xxx)
select 'Test 3' from dual;
exec testproc3;               -- This is never executed (*3)
!

echo Returned $?



$ ./SomeTest
'TEST1
------
Test 1


PL/SQL procedure successfully completed. (*1)


'TEST2
------
Test 2

BEGIN testproc2; END;

*
ERROR at line 1: (*2)
ORA-01403: no data found
ORA-06512: at "SCOTT.TESTPROC2", line 8
ORA-06512: at line 1

(*3) Note that TESTPROC3 is not executed

Returned 3

   grep 'ORA-[0-9][0-9][0-9][0-9][0-9]:' ${STANDARD_OUTPUT_MESSAGES_FILE} | egrep -vi "testing|ORA-01034: ORACLE not available|SYS.DBMS_METADATA|OUT bind variable bound to an IN position|unexpected error from call to export_string|ORA-01918|ORA-31684|MGMT_METRICS_RAW|ORA-06512: at line|ORA-01917: user or role |ORA-39082|LibraryCacheNotEmptyOnClose" >>$ORACLE_ERROR_MESSAGES_FILE

   grep 'SP2-[0-9][0-9][0-9][0-9][0-9]:' ${STANDARD_OUTPUT_MESSAGES_FILE} >>$ORACLE_ERROR_MESSAGES_FILE

   grep 'PLS-[0-9][0-9][0-9][0-9][0-9]:' ${STANDARD_OUTPUT_MESSAGES_FILE} >>$ORACLE_ERROR_MESSAGES_FILE

   grep 'UDE-[0-9][0-9][0-9][0-9][0-9]:' ${STANDARD_OUTPUT_MESSAGES_FILE}  >>$ORACLE_ERROR_MESSAGES_FILE

   grep 'LRM-[0-9][0-9][0-9][0-9][0-9]:' ${STANDARD_OUTPUT_MESSAGES_FILE}  >>$ORACLE_ERROR_MESSAGES_FILE

   grep 'Job "SYS"."SYS_IMPORT_SCHEMA_[0-9][0-9]" stopped due to fatal error at' ${STANDARD_OUTPUT_MESSAGES_FILE}  >>$ORACLE_ERROR_MESSAGES_FILE

   grep -i ' not found in ' ${STANDARD_OUTPUT_MESSAGES_FILE} | egrep -vi "ARA_FIL.OPS_PART_TRACKING_LOG" >>$ORACLE_ERROR_MESSAGES_FILE