Oracle PL/SQL - Handling an Unnamed Exception

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Statement
  4. Exception

Introduction

One situation where you need to refer to an unnamed exception is to detect deadlocks.

Deadlocks occur when two sessions are both waiting for the other to complete.

Oracle can detect deadlocks and it raises the associated exception.

In the following example, the exception is simply logged if a deadlock is detected. 

create or replace procedure p_updateLoc
    (i_deptno_nr NUMBER, i_new_loc_tx VARCHAR2)
is
        v_error_nr NUMBER;
        v_error_tx VARCHAR2(4000);
begin
    update dept set loc=i_new_loc_tx where deptno=i_deptno_nr;
exception
    when others then                         
            v_error_nr :=sqlcode;            
            v_error_tx := sqlerrm;           
            insert into t_LogError(error_tx) 
            values(i_deptno_nr||'-'||
                    v_error_nr||':'|| v_error_tx );
     if sqlcode= -60 then                    -- deadlock error is ORA-00060                   
            null;
     else
         raise;                              
     end if;
end;

Related Topic