Use of Sleep in Oracle

ORACLE “SLEEP” PROCEDURE: DBMS_LOCK.SLEEP

There’s a “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”.