Implement Delay or Sleep in Oracle PL SQL Scripts Using DBMS_LOCK.SLEEP?


Oracle provides a DBMS_LOCK.Sleep procedure to suspend the session for given period of time. The argument for the procedure is time in seconds. Seconds can be in decimals also.


See some examples below.

SQL> exec DBMS_LOCK.SLEEP (05);

PL/SQL procedure successfully completed.

SQL> exec DBMS_LOCK.SLEEP (1.999);

PL/SQL procedure successfully completed.

PL/SQL example.
SQL> SET SERVEROUTPUT ON

DECLARE
CUR_DATE VARCHAR2(30);
BEGIN
SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY HH:MI:SS') INTO CUR_DATE FROM DUAL;
DBMS_OUTPUT.PUT_LINE(CUR_DATE);
DBMS_LOCK.SLEEP (05);
SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY HH:MI:SS') INTO CUR_DATE FROM DUAL;
DBMS_OUTPUT.PUT_LINE(CUR_DATE);
END;
/
20-11-2012 09:31:13
20-11-2012 09:31:18

PL/SQL procedure successfully completed.

How to use sleep in PL SQL functions?
SQL> grant execute on DBMS_LOCK to scott;

Grant succeeded.

SQL> conn scott
Enter password:
Connected.

SQL> CREATE OR REPLACE function fn_sleep(seco NUMBER) return number as
x number;
BEGIN
  DBMS_LOCK.SLEEP(seco);
  return null;
END;
/
Function created.

SQL>
SQL> select fn_sleep(1) from dual;

FN_SLEEP(1)
-----------

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google