Jump to Navigation

237 - How to sleep in PL/SQL

The DBMS_LOCK package provides an interface to Oracle Lock Management services. You can request a lock of a specific mode,
give it a unique name recognizable in another procedure in the same or another instance, change the lock mode, and release it.

When you writing code in PL/SQL , you some times need to have logics for the application to sleep
before the next processing.   In PL/SQL you can use the below syntax  for this Syntax
dbms_lock.sleep(no_of_seconds) :

Conditions :
User should be granted with execute priviledge to dbms_lock package directly by SYS (Not via role)

SQL> grant execute on dbms_lock to user01;

Ex.  PL/SQL :

SET serveroutput on;
DECLARE
   sleep_time01   NUMBER := 10;
   sleep_time02   NUMBER := 15;
BEGIN
   DBMS_OUTPUT.put_line ('Time is ' || TO_CHAR (SYSDATE, 'HH:MI:SS'));
   DBMS_OUTPUT.put_line ('Sleeping for ' || sleep_time01 || ' seconds');
   DBMS_LOCK.sleep (sleep_time01);
   DBMS_OUTPUT.put_line ('Time is ' || TO_CHAR (SYSDATE, 'HH:MI:SS'));
   DBMS_OUTPUT.put_line ('Sleeping again for ' || sleep_time02 || ' seconds');
   DBMS_LOCK.sleep (sleep_time02);
   DBMS_OUTPUT.put_line ('Time is ' || TO_CHAR (SYSDATE, 'HH:MI:SS'));
END;
/

 

Oracle:


Main menu 2

Story | by Dr. Radut