Skip to content
accelerando

Oracle “sleep” procedure: DBMS_LOCK.SLEEP

There’s a nice little “sleep” procedure in Oracle: A procedure that stops the execution of the current thread for n seconds.

Strangely, this method can be called in SQL*Plus like so:

EXEC dbms_lock.sleep(10);

but not in another stored procedure or function like so

CREATE OR REPLACE PROCEDURE FOOBAR AS
BEGIN
  DBMS_LOCK.SLEEP(1);
END;
/

To use “sleep” in your procedures or functions, login as administrator to your database (or ask you admin to to so) and create the following objects:

CREATE OR REPLACE PROCEDURE sleep(seconds NUMBER) AS
BEGIN
  DBMS_LOCK.SLEEP(seconds);
END;
/
 
CREATE OR REPLACE public synonym sleep FOR sleep;
 
GRANT EXECUTE ON sleep TO public;

and in your procedure just use “sleep”.

7 Comments

  1. Marc wrote:

    Man you saved my day! Had exactly that darn problem and couldn’t figure out why. Thanks a lot! :-)

    Posted on 21-Oct-10 at 3:28 pm | Permalink
  2. Michael wrote:

    You’re welcome, Marc.

    Nice Blog, btw!

    Posted on 21-Oct-10 at 3:49 pm | Permalink
  3. Erik Hymel wrote:

    You can call it from a procedure. I suspect you have execute on dbms_lock through a role, but to call the procedure the permission needs to be granted directly to the user.

    Posted on 26-Apr-11 at 5:47 pm | Permalink
  4. Amin Adatia wrote:

    It seems that in order to use DBMS_LOCK you need to be granted EXECUTE on DBMS_LOCK by SYS (or SYSTEM?). Wht the package is just not publicly available out-of-the-box is unknown. No idea what is so terrible in the package.

    Posted on 19-Feb-12 at 6:57 pm | Permalink
  5. Michael wrote:

    Exactly that didn’t work for me. I had to create this superfluous alias, for whatever reason. You see i have grant on this as well.

    Posted on 19-Feb-12 at 7:16 pm | Permalink
  6. Muneer Nawaz wrote:

    This is very useful, thnx for sharing this information.

    Posted on 30-May-13 at 11:06 am | Permalink
  7. Michael wrote:

    You’re welcome Muneer.

    Posted on 31-May-13 at 10:22 am | Permalink

Post a Comment

Your email is never published nor shared. Required fields are marked *
*
*

*