Use of DBMS_SESSION or DBMS_LOCK SLEEP Procedure to hold session in PLSQL

Example of DBMS_SESSION or DBMS_LOCK SLEEP Procedure to hold session in PLSQL

DBMS_SESSION.SLEEP is introduced in Oracle 18c.
DBMS_SESSION.SLEEP and DBMS_LOCK.SLEEP is having same functionality to hold the session for particular/defined no of seconds. Both can suspend/hold the session for specified number of seconds as we given.
It is used to hold the session for particular seconds in PL/SQL blocks/package/procedures.

Example of use DBMS_SESSION.SLEEP

SET SERVEROUTPUT ON
DECLARE
v_date varchar2(30);
BEGIN
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') into v_date from dual;
dbms_output.put_line('Before:'||v_date);
-- Hold session for 10 Seconds during runtime
sys.DBMS_SESSION.sleep(10);
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') into v_date from dual;
dbms_output.put_line('After:'||v_date);
END;
/

Before:03-DEC-2020 17:58:58
After:03-DEC-2020 17:59:08


PL/SQL procedure successfully completed.

Example of using the DBMS_LOCK.SLEEP

SET SERVEROUTPUT ON
DECLARE
v_date varchar2(30);
BEGIN
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') into v_date from dual;
dbms_output.put_line('Before:'||v_date);
-- Hold session for 10 Seconds during runtime
sys.DBMS_LOCK.SLEEP(10);
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') into v_date from dual;
dbms_output.put_line('After:'||v_date);
END;
/


Before:03-DEC-2020 18:00:01
After:03-DEC-2020 18:00:11

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.