PL/SQL: Sleep without using DBMS_LOCK

If you want to wait in your PL/SQL program (“sleep”) you may want to use the procedure “SLEEP” from the package “DBMS_LOCK“. While this function does exactly what you want, your DBA may not grant you access to this package, because it contains some other mighty and dangerous procedures.

But there is help! Here are some valid alternatives:

Option 1: APEX_UTIL.sleep

If APEX is installed you can use the procedure “PAUSE” from the publicly available package APEX_UTIL.

Example – “Wait 5 seconds”:

SET SERVEROUTPUT ON ;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    APEX_UTIL.PAUSE(5);
    DBMS_OUTPUT.PUT_LINE('End   ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/

Option 2: java.lang.Thread.sleep

An other option is the use of the method “sleep” from the Java class “Thread”, which you can easily use through providing a simple PL/SQL wrapper procedure:

CREATE OR REPLACE PROCEDURE SLEEP (P_MILLI_SECONDS IN NUMBER) 
AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';

Note: Please remember, that “Thread.sleep” uses milli seconds!

Example – “Wait 5 seconds”:

SET SERVEROUTPUT ON ;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    SLEEP(5 * 1000);
    DBMS_OUTPUT.PUT_LINE('End   ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/

 

I will speak at the “DOAG 2015 Conference + Exhibition” (“DOAG 2015 Konferenz + Ausstellung”)

I will be on the “DOAG 2015 Conference + Exhibition” in Nürnberg (Nuremberg), Germany from November 17th to 19th.

I will speak at November 18th, at 10 am, room “Konferenzraum EG”:
Was Sie bei modernen Datenbank-Systemen anders machen müssen
(English: “What you need to do differently in modern database systems”)

The DOAG is the largest German Oracle user group with over 6000 members.

DOAG 2015 Konferenz + Ausstellung

DOAG 2015 Conference + Exhibition

Scripts for my DOAG 2014 presentation “1000 mal schneller – Praxisgeschichten aus der Oracle-Datenbankwelt”

Thank you very much for attending my presentation “1000 mal schneller – Praxisgeschichten aus der Oracle-Datenbankwelt”!
(English: “1000 times faster – Real life stories from the world of Oracle Databases”)

You can download the scripts i used to simulate the shown cases here:

I will speak at the “DOAG 2014 Conference + Exhibition” (“DOAG 2014 Konferenz + Ausstellung”)

I will be on the “DOAG 2014 Conference + Exhibition” in Nürnberg (Nuremberg), Germany from November 18th to 20th.

I will speak at November 19th, at 4 pm at room “Shanghai”:

1000 mal schneller – Praxisgeschichten aus der Oracle-Datenbankwelt

(English: “1000 times faster – Real life stories from the world of Oracle Databases”)

 

The DOAG is the largest German Oracle user group with over 5000 members.

2014-08-04-DOAG-2014-Konferenz-Banner-Speaker-Highlighnen-180x150