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”.

Share This

Post a comment — Trackback URI RSS 2.0 feed for these comments This entry (permalink) was posted on Tuesday, September 7, 2010, at 11:00 am by Michael, tagged with Code Snippets, DBMS_LOCK, PL/SQL and categorized in English posts, Oracle.
The following post could be of some interest: Put your mac to rest via ssh, Safe Sleep: Sicherer Ruhezustand, Mac OS X Lion loses network connection after sleep, JDBC: Autogenerated keys revisited., JDBC: Get autogenerated keys on a Oracle DB, Oracle XE environment variables on Linux, On writing binary data from within Oracle Forms 6i, Installing Oracle 11g Database on OEL5, Feeling dizzy…, Negative Erlebnisse

3 Comments

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
Michael wrote:

You’re welcome, Marc.

Nice Blog, btw!

Posted on 21-Oct-10 at 3:49 pm | Permalink
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

info.michael-simons.eu

Oracle “sleep” procedure: DBMS_LOCK.SLEEP

3 Comments

Post a Comment