How to Sleep (wait) in PL/SQL

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)

grant execute on dbms_lock to john;

Sample Code

SET serveroutput on;

DECLARE
   sleep_time01   NUMBER := 5;
   sleep_time02   NUMBER := 10;
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;

Output

Time is 11:13:44
Sleeping for 5 seconds
Time is 11:13:49
Sleeping again for 10 seconds
Time is 11:13:59

PL/SQL procedure successfully completed.