views:

580

answers:

4

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?

Thanks in advance.

A: 

If Java is installed on your 11G then you can do it in a java class and call it from your PL/SQL, but I am not sure that it does not require also a specific grant to call java.

Matthieu BROUILLARD
+1  A: 

Seems the java procedure/function could work. But why don't you compile your function under a user like the application schema or a admin account that has this grant and just grant your developer account execute on it. That way the definer rights are used.

StarShip3000
+1  A: 

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;
OMG Ponies
Thanks very much. works perfect. +1000
Salvador
@Salvador: There's likely to be better answers - this one works, but I wouldn't expect the performance to be that great.
OMG Ponies
@OMG Ponies: while it works il will consume **A LOT** of CPU.
Vincent Malgrat
I agree with vincent.. this will work, but its a horrible horrible idea.
Matthew Watson
+3  A: 

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 */
Matthew Watson
This is definitely the right approach to take. Apart from granting resource and create session to USERA; this account needs neither privilege.
APC
Good point @APC
Matthew Watson