Sleep function in ORACLE

Question

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?

Answer 1

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; 

Answer 2

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 */