Handling ORA-00060 Deadlock Detected Error in PL/SQL

==============================
General
==============================
Applicative example of Handling ORA-00060 Deadlock Detected Error in PL/SQL.

Consider following scenario:
There are two independent PL/SQL processes running on scheduler.
It might happen that these two process would update same table.
As a result one of the processes is killed by Oracle, and "ORA-00060 deadlock detected" exception is thrown.

The solution would be to catch the ORA-00060 exception, let the process sleep for 20 seconds, as so the other process would have a chance to commit, then rerun the same code. 

If the second run fails as well, rollback the transaction and throw an applicative exception.


First Step - Grant EXECUTE ON SYS.DBMS_LOCK to the user.
As SYSTEM
SQL> GRANT EXECUTE ON SYS.DBMS_LOCK TO DBA_USER WITH GRANT OPTION;
Grant succeeded.

As DBA_USER
SQL> GRANT EXECUTE ON SYS.DBMS_LOCK TO MY_USER
Grant succeeded.


Second Step - Define the ORA-00060 Exception in the Package Header
Define the ORA-00060 Exception in the Package Specifications using PRAGMA EXCEPTION_INIT

--CONSTANTS
  C_FIRST_RUN                 CONSTANT INTEGER := 1;
  C_SECOND_RUN                CONSTANT INTEGER := 2; 

--EXCEPTIONS
  EXP_ORA_DEADLOCK            EXCEPTION;
  PRAGMA EXCEPTION_INIT(EXP_ORA_DEADLOCK,-60);

Third Step - Implement the solution in the code.

PROCEDURE markTransactions(pDateOfCall IN VARCHAR2,  
                           pCustomerId IN VARCHAR2, 
                           pRunTime IN NUMBER) IS

  vModuleName    VARCHAR2(100) := 'markTransactions';

BEGIN

   UPDATE  MY_TABLE_A MY_TABLE
   SET MY_TABLE.is_processed = 1
   WHERE date_of_call = pDateOfCall
     AND customer_id = pCustomerId;

   UTIL.writeTrace(vModuleName, 'Finish update MY_TABLE_A');

   UPDATE  MY_TABLE_B MY_TABLE
   SET MY_TABLE.is_processed = 1
   WHERE date_of_call = pDateOfCall
     AND customer_id = pCustomerId;

   UTIL.writeTrace(vModuleName, 'Finish update MY_TABLE_B');


EXCEPTION
   WHEN EXP_ORA_DEADLOCK THEN      
      IF pRunTime=C_FIRST_RUN THEN
          DBMS_LOCK.sleep(20);
          Util.writeTrace(vModuleName, 'Encountered ORA-00060: deadlock detected Error. Attempting Rerun');
          markTransactions(pDateOfCall, pMinDateOfcall ,pOriginGateId,C_SECOND_RUN);
      ELSE  
       ROLLBACK;
          UTIL.writeTrace(vModuleName, 'Encountered ORA-00060: deadlock detected Error. Rerun Failed');
          UTIL.writeTrace(vModuleName, SQLERRM);
          RAISE Util.E_LOGGED_EXP;
      END IF;  
   WHEN OTHERS THEN
     ROLLBACK;
          UTIL.writeTrace(vModuleName, SQLERRM);
          RAISE Util.E_LOGGED_EXP;
END markTransactions;