ops$tkyte%ORA11GR2> create table t ( x int constraint t_pk primary key, y int );

Table created.

Elapsed: 00:00:00.01
ops$tkyte%ORA11GR2> create index t_idx on t(y);

Index created.

Elapsed: 00:00:00.01
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> pause

ops$tkyte%ORA11GR2> set timing on
ops$tkyte%ORA11GR2> BEGIN
  2          execute immediate 'alter session set ddl_lock_timeout = 6';
  3          execute immediate 'alter table ops$tkyte.t drop constraint t_pk';
  4          execute immediate 'drop index t_idx';
  5  END;
  6  /
BEGIN
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at line 3


Elapsed: 00:00:06.05
ops$tkyte%ORA11GR2> 

ops$tkyte%ORA11GR2>  create or replace PROCEDURE test
  2    IS
  3    BEGIN
  4      WHILE true LOOP
  5        BEGIN
  6          IF (1=1)
  7          THEN
  8                          dbms_output.put_line( 1/0 );
  9              EXIT;
 10          END IF;
 11        EXCEPTION
 12          WHEN OTHERS THEN
 13            DBMS_OUTPUT.PUT_LINE('Failed to execute: '||' due to this unexpected error.');
 14            DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
 15            EXIT;
 16        END;
 17        DBMS_OUTPUT.PUT_LINE('Trying again...');
 18        DBMS_LOCK.SLEEP(1);
 19      END LOOP;
 20    END;
 21  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec test
Failed to execute:  due to this unexpected error.
ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^  success!!! 

<b>the caller thinks it works - and has nothing to know otherwise.  We lose the nice error stack.  We make it harder to debug, as opposed to the right way without that nasty when others:</b>


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2>  create or replace PROCEDURE test
  2    IS
  3    BEGIN
  4      WHILE true LOOP
  5        BEGIN
  6          IF (1=1)
  7          THEN
  8                          dbms_output.put_line( 1/0 );
  9              EXIT;
 10          END IF;
 11  /*
 12          EXCEPTION
 13          WHEN OTHERS THEN
 14            DBMS_OUTPUT.PUT_LINE('Failed to execute: '||' due to this unexpected error.');
 15            DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
 16            EXIT;
 17  */
 18        END;
 19        DBMS_OUTPUT.PUT_LINE('Trying again...');
 20        DBMS_LOCK.SLEEP(1);
 21      END LOOP;
 22    END;
 23  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec test
BEGIN test; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "OPS$TKYTE.TEST", line 8
ORA-06512: at line 1

<b>Now we can clearly see the error, the caller cannot ignore the fact that it failed (exception city)</b>