I have been trying to write a stored procedure which is based on the following concept. The procedure will try to drop a constraint on a table and if in case it detects a deadlock situation, it wait for some time before trying again. The important thing is it should only retry in case of a Deadlock or a NOWAIT error, all other errors are to be handled via exceptions.

Procedure test
is

BEGIN
<<label>>
DROP constraint on a table

if (deadlock(ORA-00060)/Nowait Error (ORA-0054)) detected
then
sleep for 60 seconds
Goto label

exception
when others.

It would be great if any of the experts please help me with this. A similar example would be highly helpful. Thank you for your help.

SQL> begin
  2  loop
  3    begin
  4      execute immediate 'drop table t';
  5      exit;
  6    exception
  7      when others then
  8        if sqlcode = -54 then
  9           dbms_output.put_line('Failed, sleeping');
 10           dbms_lock.sleep(5);
 11        else
 12           raise;
 13        end if;
 14    end;
 15  end loop;
 16  end;
 17  /
Failed, sleeping
Failed, sleeping

PL/SQL procedure successfully completed.