Sample of DBMS_LOCK.SLEEP

• Create a Table

Create Table T_Sleep_Test(
S_No Number,
Dateandtime Date);

• Create a Procedure

CREATE OR REPLACE PROCEDURE XXTEST_PROCEDURE

IS

   l_cnt   NUMBER;

BEGIN

   l_cnt := 0;

   EXECUTE IMMEDIATE 'Truncate Table Xxsleep_table';

   FOR l_cnt IN 1 .. 5

   LOOP

      INSERT INTO xxsleep_table

           VALUES (l_cnt, SYSDATE);

      COMMIT;

      DBMS_LOCK.sleep (10);

   END LOOP;

END;

• Values get inserted into the table created above in an interval of 10 seconds

Exec XXTEST_PROCEDURE;

• Check once the execution of the procedure gets completed.

Select S_No, To_Char(Dateandtime,'DD-MON-RRRR HH24:MI:SS') Ace From   Xxsleep_table;