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

advertisements

_____________________________________________________________________________________________________________________

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)

-----------

_____________________________________________________________________________________________________________________

Website Stats