-- #############################################################################################
--
-- %Purpose:    How to implement "Sleep Function" with PL/SQL ?
--
--              The next example TryToGetSlot tries to get free
--              system resources (e.g. to create an index on a
--              busy table). The function waits for some seconds
--              and then tries to get the resource again. After
--              a counter have reached the maximum, the routine
--              exits.
--
-- #############################################################################################
--
CREATE OR REPLACE PROCEDURE TryToGetSlot IS
  GotIt  BOOLEAN := FALSE;
  Count  NUMBER  := 0;
BEGIN
  WHILE (NOT GotIt AND NOT (Count > 10)) LOOP
    BEGIN
      -- Try to get free slot, if OK, set GotIt = TRUE
      -- else EXCEPTION will automatically fire.
      (Insert Code here)
      GotIt := TRUE;
    EXCEPTION
      WHEN OTHERS THEN
        GotIt := FALSE;
        DBMS_LOCK.SLEEP(10);
        Count := Count + 1;
    END;
  END LOOP;
END;
/