Over the last couple of releases, Oracle has added several handy PL/SQL packages and procedures you might not know about. So, I put together a short blog series highlighting some of my favorites. First up, DBMS_SESSION.SLEEP().
Oracle has always enabled you to add a sleep command to your stored procedures to suspend a session for a specified number of seconds, as shown in the code below.
DECLARE v_start TIMESTAMP; v_end TIMESTAMP; BEGIN v_start := SYSTIMESTAMP; -- Sleep for 10 seconds DBMS_LOCK.SLEEP(10); v_end := SYSTIMESTAMP; DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start); DBMS_OUTPUT.PUT_LINE('This procedure ended at ' ||v_end); END; / This PROCEDURE started AT 10-SEP-22 12.39.40.587041 AM This PROCEDURE ended AT 10-SEP-22 12.39.50.637738 AM PL/SQL PROCEDURE successfully completed. Elapsed: 00:00:10.02
However, the sleep function was part of the DBMS_LOCK package, which is not granted to PUBLIC, by default, due to the other more powerful functions inside that package. That means you had to beg the DBA or the security team to give you access to this package just to put your session to sleep for a few minutes.
DBMS_SESSION.SLEEP()
Things got a lot easier starting in Oracle Database 18c, as the sleep function is now available in the DBMS_SESSION package, which is granted to PUBLIC by default. That means you can call the function without any additional privileges. Plus, the function code in DBMS_SESSION.SLEEP is identical to DBMS_LOCK.SLEEP, so you can do a simple find and replace in your code!
DECLARE v_start TIMESTAMP; v_end TIMESTAMP; BEGIN v_start := SYSTIMESTAMP; -- Sleep for 10 seconds DBMS_SESSION.SLEEP(10); v_end := SYSTIMESTAMP; DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start); DBMS_OUTPUT.PUT_LINE('This procedure ended at ' ||v_end); END; / This PROCEDURE started AT 10-SEP-22 12.39.40.587041 AM This PROCEDURE ended AT 10-SEP-22 12.39.50.637738 AM PL/SQL PROCEDURE successfully completed. Elapsed: 00:00:10.02
