Skip to Main Content

Accessible SLEEP function

William RobertsonMay 4 2015 — edited Mar 9 2018

Due no doubt to some accident of history, PL/SQL's sleep() function resides within DBMS_LOCK, a package with nothing to do with timing that happened to need it to implement lock timeouts.

We have probably all put core functions in whatever package we happened to be working on because it seemed less effort at the time, but the effect of this particular design decision is that every account needing to use this basic function within stored PL/SQL must be granted direct execute privilege on a package for managing user-defined locks (or we have to implement a wrapper package in a privileged schema, often hard to justify in a production system). If you are connecting with a read-only production account - for monitoring performance for example - you have to implement it some other way (SQL*Plus PAUSE command, host out to an OS utility etc).

Please can we have a SLEEP function in DBMS_UTILITY or a similar lower-privileged package.

(And in case it's not fixed in 12.1, this time around can we have it coded by someone who knows how many seconds there are in 1000 seconds.)

Oracle have accepted the idea and raised ER: 23557076

Update 2017-05-22: DBMS_SESSION will have a publicly available SLEEP function.

Update 2018-03-09: DBMS_SESSION.SLEEP() is now part of Oracle 18c

Post Details
Added on May 4 2015