Sometimes a developer needs a "sleep" function - this is not only true in Java or C/C++, but also
in SQL and PL/SQL programming environments. Looking into the Oracle documentation one will quickly
find the procedure SLEEP in the DBMS_LOCK package.

But most developers do not have these. And DBMS_LOCK is a package which should not
be granted to everyone - it's far too mighty. So a developer would at least have
a discussion with his DBA before getting access to DBMS_LOCK. But there are some
alternatives ...

When APEX has been installed into the database there is the package APEX_UTIL.
It
contains a procedure PAUSE which does exactly the same as DBMS_LOCK.SLEEP with
one exception: The sleeping time is being capped at 120 seconds, which we can see by
examining the package spec of APEX_UTIL.

Since APEX_UTIL is a public package it can be used
without the need for special privileges. If APEX is not present in the database I'd recommend
to take this appraoch as a template: The DBA could build an own public SLEEP funktion and
make this available to all developers. Here is an example implemention:

As a result, we have a special sleep function which is available to everyone in the
database. Of course, it would also be possible to grant execution privilege to a special
developer role instead of PUBLIC. The limits (here: 5 minutes) and the violation rule (APEX: quiet capping, here: raise an exception) can
be implemented according to individual needs.
Personally I'd strongly recommend an approach like this: The DBA keeps control, since it is his own
procedure. He can control its behaviour and he can (via DBA_DEPENDENCIES) always see where is is being used.

But that's not all - we have another alternative: Imagine, we have no access to DBMS_LOCK, there is no "global" SLEEP
funktion as described above, APEX is not installed and we need a sleep funcktion.
The solution: Use Java in the Database.

Java has the method sleep() within the class java.lang.Thread. Java in the database
supports the complete multithreading API (there is no "real" multithreading, since all threads are
being serialized).
Therefore we can use java.lang.Thread.sleep() to let the database
session sleep for the given amount of milliseconds. Summarized: Thread.sleep() does the same
as DBMS_LOCK.SLEEP. But opposed to that, no special privileges are needed and
every database user with the CREATE PROCEDURE privilege can use it.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.