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