Take the 2-minute tour ×

I need execute an sql query in ORACLE it takes a certain amount of time.

so i wrote this function

   DBMS_LOCK.sleep(seconds => TIME_);
   RETURN 1;

and i call in this way


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.

share|improve this question
stupid question, but what is the behaviour expected (or necessity) of such a thing on the DB? When do you need that? –  Matthieu BROUILLARD Apr 1 '10 at 15:51
FYI.. Your exception block is pointless. The RETURN 1 in the block will never be reached, and if the block was completely missing... the exception would raise anyway. –  Matthew Watson Apr 2 '10 at 1:13
I needed a sleep function on one occasion. In this app. an external program calls a database function that writes data to a file. The external program then accesses this file. On one particular system we needed a sleep at the end of the function because otherwise the external program would try to access the file before it was completely written. On most systems it worked without any built in delay. –  Rene Apr 2 '10 at 10:49
So the external program isn't waiting for the function to return - in which case the sleep might not help? Or the function is returning before it's finished writing the file? If the latter then is it not flushing the data or explicitly closing the file - can't get my head around how this can be happening otherwise, maybe I'm missing something... –  Alex Poole Apr 2 '10 at 15:34
Why don't you want to use the DBMS_LOCK.SLEEP() function? So many database applications are sabotaged by needless re-inventions of nuilt-in functionality. –  APC Apr 4 '10 at 19:18

7 Answers 7

up vote 13 down vote accepted

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 
  INTO v_now

-- 2) Loop until the original timestamp plus the amount of seconds = current date
  EXIT WHEN v_now + (IN_TIME * (1/86400)) = SYSDATE
share|improve this answer
Thanks very much. works perfect. +1000 –  Salvador Apr 1 '10 at 16:24
@Salvador: There's likely to be better answers - this one works, but I wouldn't expect the performance to be that great. –  OMG Ponies Apr 1 '10 at 16:31
while it works il will consume A LOT of CPU. –  Vincent Malgrat Apr 1 '10 at 17:07
I agree with vincent.. this will work, but its a horrible horrible idea. –  Matthew Watson Apr 2 '10 at 8:38

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
 return 1;

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


/* Finished */
share|improve this answer
This is definitely the right approach to take. Apart from granting resource and create session to USERA; this account needs neither privilege. –  APC Apr 4 '10 at 19:16
Good point @APC –  Matthew Watson Apr 5 '10 at 2:00

If executed within "sqlplus", you can execute a host operating system command "sleep" :

!sleep 1


host sleep 1
share|improve this answer
On window you may want to use one of the commands from here: stackoverflow.com/q/1672338/409172 –  Jon Heller Jul 10 '13 at 22:31

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.

share|improve this answer

It would be better to implement a synchronization mechanism. The easiest is to write a file after the first file is complete. So you have a sentinel file.

So the external programs looks for the sentinel file to exist. When it does it knows that it can safely use the data in the real file.

Another way to do this, which is similar to how some browsers do it when downloading files, is to have the file named base-name_part until the file is completely downloaded and then at the end rename the file to base-name. This way the external program can't "see" the file until it is complete. This way wouldn't require rewrite of the external program. Which might make it best for this situation.

share|improve this answer

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.

share|improve this answer

You can use DBMS_PIPE.SEND_MESSAGE with a message that is too large for the pipe, for example for a 5 second delay write XXX to a pipe that can only accept one byte using a 5 second timeout as below

dummy:=dbms_pipe.send_message('TEST_PIPE', 5, 1);

But then that requires a grant for DBMS_PIPE so perhaps no better.

share|improve this answer

Your Answer


