Sleep function in ORACLE
I need execute an SQL query in ORACLE it takes a certain amount of time. So I wrote this function:
CREATE OR REPLACE FUNCTION MYSCHEMA.TEST_SLEEP
(
TIME_ IN NUMBER
)
RETURN INTEGER IS
BEGIN
DBMS_LOCK.sleep(seconds => TIME_);
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RAISE;
RETURN 1;
END TEST_SLEEP;
and I call in this way
SELECT TEST_SLEEP(10.5) FROM DUAL
but to work I need set grant of DBMS_LOCK
to the owner of the procedure.
How I can rewrite this function without using the DBMS_LOCK.sleep
function?
Short of granting access to DBMS_LOCK.sleep
, this will work but it's a horrible hack:
IN_TIME INT; --num seconds
v_now DATE;
-- 1) Get the date & time
SELECT SYSDATE
INTO v_now
FROM DUAL;
-- 2) Loop until the original timestamp plus the amount of seconds <= current date
LOOP
EXIT WHEN v_now + (IN_TIME * (1/86400)) <= SYSDATE;
END LOOP;
Create a procedure which just does your lock and install it into a different user, who is "trusted" with dbms_lock ( USERA ), grant USERA access to dbms_lock.
Then just grant USERB access to this function. They then wont need to be able to access DBMS_LOCK
( make sure you don't have usera and userb in your system before running this )
Connect as a user with grant privs for dbms_lock, and can create users
drop user usera cascade;
drop user userb cascade;
create user usera default tablespace users identified by abc123;
grant create session to usera;
grant resource to usera;
grant execute on dbms_lock to usera;
create user userb default tablespace users identified by abc123;
grant create session to userb;
grant resource to useb
connect usera/abc123;
create or replace function usera.f_sleep( in_time number ) return number is
begin
dbms_lock.sleep(in_time);
return 1;
end;
/
grant execute on usera.f_sleep to userb;
connect userb/abc123;
/* About to sleep as userb */
select usera.f_sleep(5) from dual;
/* Finished sleeping as userb */
/* Attempt to access dbms_lock as userb.. Should fail */
begin
dbms_lock.sleep(5);
end;
/
/* Finished */
From Oracle 18c you could use DBMS_SESSION.SLEEP procedure:
This procedure suspends the session for a specified period of time.
DBMS_SESSION.SLEEP (seconds IN NUMBER)
DBMS_SESSION.sleep
is available to all sessions with no additional grants needed.
Please note that DBMS_LOCK.sleep
is deprecated.
If you need simple query sleep you could use WITH FUNCTION
:
WITH FUNCTION my_sleep(i NUMBER)
RETURN NUMBER
BEGIN
DBMS_SESSION.sleep(i);
RETURN i;
END;
SELECT my_sleep(3) FROM dual;
There is a good article on this topic: PL/SQL: Sleep without using DBMS_LOCK that helped me out. I used Option 2 wrapped in a custom package. Proposed solutions are:
Option 1: APEX_UTIL.sleep
If APEX is installed you can use the procedure “PAUSE” from the publicly available package APEX_UTIL.
Example – “Wait 5 seconds”:
SET SERVEROUTPUT ON ;
BEGIN
DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
APEX_UTIL.PAUSE(5);
DBMS_OUTPUT.PUT_LINE('End ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/
Option 2: java.lang.Thread.sleep
An other option is the use of the method “sleep” from the Java class “Thread”, which you can easily use through providing a simple PL/SQL wrapper procedure:
Note: Please remember, that “Thread.sleep” uses milliseconds!
--- create ---
CREATE OR REPLACE PROCEDURE SLEEP (P_MILLI_SECONDS IN NUMBER)
AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';
--- use ---
SET SERVEROUTPUT ON ;
BEGIN
DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
SLEEP(5 * 1000);
DBMS_OUTPUT.PUT_LINE('End ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/
If executed within "sqlplus", you can execute a host operating system command "sleep" :
!sleep 1
or
host sleep 1