Skip to Main Content
  • Questions
  • PL/SQL equivalent of unix sleep command.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rowan.

Asked: June 20, 2001 - 9:08 pm UTC

Last updated: March 25, 2013 - 2:25 pm UTC

Version: 8.0.6

Viewed 100K+ times! This question is

You Asked

Is there any PL/SQL procedure, function etc where you can delay processing for a nominated time.

and we said...

dbms_lock.sleep( Number_of_seconds );


See
</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

for why SYS will need to grant you EXECUTE on this package directly (not via a role) if you want to create a stored procedure that uses it.


Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Delay processing

Armin Bollmann, October 25, 2001 - 3:53 pm UTC

Very helpful like almost the most of your hints and answers.

Thanks a lot
Armin

Regarding dbms_lock.sleep

Priti, February 20, 2002 - 6:22 am UTC

This was most helpful as we were stuck as to how to delay the further processing after a point. Has saved a lot of efforts.

Thnax a million

Good for regression test

David Penington, May 02, 2002 - 8:21 pm UTC

I was looking for this for regression testing. Looking up asktom was a lot quicker than trawling the manuals usually is.

Quick and easy

Michely Rivas, August 05, 2004 - 6:28 pm UTC

Definily this web page is the most quick and great way to look for a answer. Thanks a lot

anything else

Asim Naveed, September 28, 2005 - 3:59 pm UTC

TOM,
Your answer to this question was in 2001.

Is there any other/better/alternative way now in 9i or
10g release 2.


Thanks

Tom Kyte
September 28, 2005 - 5:37 pm UTC

nope, this is still an awesome way to do it?

is there some issue you are having?

Use jobs as a replacement?

Tim..., September 29, 2005 - 3:54 am UTC

If you want the sleep to last a long time you might want to consider splitting your work into two sections. At the end of the first section automatically schedule a job to run the second section.

10g R2 makes this even easier by implementing job chains and event-based scheduling.

I guess for most the dbms_lock.sleep procedure is more than sufficient though :)

Cheers

Tim...

privilege

Asim Naveed, September 29, 2005 - 4:07 am UTC

Actually SLEEP functionality is implemented
as a function in almost every language.

So shouldn't sleep in PL/SQL should be like simple
SUBSTR() or RPAD() functions in PL/SQL, I mean which
do not require any privileges to be granted.

Also if dbms_lock.sleep , is a pre-defined packaged by pl/sql,
it must be using PL/SQL language inside it, so how
is basicaly it delaying the no. of seconds.

Actualy i wanted to use it in developer2000 forms 6i's
PRE-INSERT Trigger on block level. What I want
is to try LOCKING a table after every 3 seconds until
it is successfully locked or 10 failed trys are done, because :-

There is no such command like LOCK TABLE t1 IN EXCLUSIVE MODE KEEP TRYING FOR 10 SECONDS BEFORE GIVING UP.

OR

There is no such command like LOCK TABLE t1 IN EXCLUSIVE MODE KEEP TRYING FOR 10 TIMES EVERY 3 SECONDS BEFORE GIVING UP.



Thanks



Tom Kyte
September 29, 2005 - 7:08 am UTC

that dbms_lock is "a plsql spec" does not mean it is in plsql at all. it is in the kernel.

In forms, you could use an alarm as well.

ok

Asim Naveed, September 29, 2005 - 7:46 am UTC

Ok but what stopped PL/SQL Language writers (or oracle)
from making dbms_lock.sleep a simple function like
substr or rpad

Thanks

Tom Kyte
September 30, 2005 - 7:19 am UTC

I don't know, you'd have to ask them.

Here, I tell you "how it is", I cannot always say "why" - no one can.

Good for optimization projects, too!!

Greg, September 29, 2005 - 1:57 pm UTC

Just had one of our (smart-ass) developers point out that it might work well for optimization code as well ... but we just wanted to confirm if it worked or not .. :\

BEGIN
  FOR lrt_emp IN (SELECT * FROM emp)
  LOOP
    dbms_lock.sleep( -100 );
    p_process(lrt_emp);
  END LOOP;
END;
/

It seems to work fine, in any case ... 

SQL> set timing on
SQL> execute dbms_lock.sleep ( -100 );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

------
But I don't think the timing via "set timing on" is behaving ... 

;)

(yes, I think we may have found that all-elusive: FAST=TRUE switch!!!)

(sorry - couldn't help myself ... )  :p
 

One more thing

Asim Naveed, September 30, 2005 - 3:51 am UTC

I have searched for ALARM in developer 2000 6i but
couldnt find anything related to it.
also
EXECUTE dbms_lock.sleep(3);
OR
dbms_lock.sleep(3);
is not getting compiled in Froms 6i.

Thanks

Tom Kyte
September 30, 2005 - 9:32 am UTC

you need to have been granted access to dbms_lock.

I believe it is called a timer or something in forms (you might try otn.oracle.com -> discussion forums) it has been over a decade since my last form.

For Asim

Muhammad Riaz Shahid, September 30, 2005 - 7:05 am UTC

Asim,

Please see Tom's comments above.

You need execute on DBMS_LOCK to be granted DIRECTLY (not via role) through SYS user.

open SQL*PLUS and do the following:

SQL> set role none;
SQL> desc dbms_lock

If you can describe the above package after "Set role none" it means you can use it in PL/SQL. 

THANKS

Asim Naveed, September 30, 2005 - 1:40 pm UTC

Thank you Riaz Shahid for reminding me about privilege.

I granted the privilege to the role on dbms_lock package,
and then assigned that role to a user, and then compiled
the form and it compiled successfully.

What I understand that the restriction of directly
granting to the user is when you are writing this
dbms_lock in a stored procedure.

Tom,
You said about using alarm in forms. Couldnt
find any such thing in forms.



Tom Kyte
September 30, 2005 - 2:25 pm UTC

timer -- I believe it is called a "timer"

Extremely helpful

Corporal Sassy Pants, September 15, 2006 - 2:52 pm UTC

It was difficult to find the answer on this. Most people say it can't be done at all. Now I can build delays in my code all over and look like a super-hero when I fix them.

Yeah, I'm just kidding... lol thanks

Great esp. the tip about granting directly

A reader, November 27, 2006 - 12:08 pm UTC

As very often oracle is not helpfull in providing an exact error message. I was compiling a package which contained a call to DBMS_LOCK.sleep, and it would not compile, telling me that DBMS_LOCK.sleep is not defined and I should define it. Anonymous PL/SQL worked fine.

So what was lacking: Assign Priveledge directly. Thanks!

workaround in pl/sql without dbms_lock

Martin, March 23, 2013 - 8:41 pm UTC

Let says your DBA wont grant you dbms_lock ... hide this function somewhere ... :o)

CREATE OR REPLACE FUNCTION WaitPLSQL(p_howLongInSec IN NUMBER) RETURN VARCHAR2 IS
l_start_datetime DATE;
l_now_datetime DATE;
l_nb_loop NUMBER:=0;
-- add "0.000011574074074074074" to any date to add 1 sec.
FUNCTION f_get_seconde(p_nb_sec IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN((0.000011574074074074074)*p_nb_sec);
END;
BEGIN
dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
l_start_datetime := sysdate;
l_now_datetime := sysdate;
LOOP
-- for security check, in case of a problem with sysdate
l_nb_loop := l_nb_loop + 1;
-- each time the sysdate get 1 more second we log the date/time + nb loop count during the last second elapse using dbms_output
IF to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') != to_char(l_now_datetime, 'yyyy-mm-dd hh24:mi:ss') THEN
dbms_output.put_line(to_char(l_now_datetime, 'yyyy-mm-dd hh24:mi:ss')||' - '||l_nb_loop||' loops.');
l_now_datetime := sysdate;
l_nb_loop := 0;
END IF;
-- exit when the number of seconds reached or when the security count is over 10 X Normal loops/second
-- to get the normal nb of loop/second, put the "OR condition" in comment, execute the function
-- for 20 second and see how many loops the function do per second ... depends on server cpu speed
EXIT when (
(sysdate >= l_start_datetime + f_get_seconde(p_nb_sec => p_howLongInSec))
OR
(l_nb_loop >= 1000000)
);
END LOOP;
dbms_output.put_line(to_char(l_now_datetime, 'yyyy-mm-dd hh24:mi:ss')||' - '||l_nb_loop||' loops.');
dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
RETURN('Waiting planned for '||p_howLongInSec||' seconds - Enter at : '||to_char(l_start_datetime, 'yyyy-mm-dd hh24:mi:ss')||' exit at : '||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
END;
Tom Kyte
March 25, 2013 - 2:25 pm UTC

oh
my
gosh


seriously? why not through in a few ln() calls just to burn more cpu?

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set echo on
ops$tkyte%ORA11GR2> variable n number
ops$tkyte%ORA11GR2> exec :n := dbms_utility.get_cpu_time

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec dbms_output.put_line( waitplsql( 5 ));
2013-03-25 15:19:54
2013-03-25 15:19:54 - 149364 loops.
2013-03-25 15:19:55 - 186162 loops.
2013-03-25 15:19:56 - 187690 loops.
2013-03-25 15:19:57 - 178504 loops.
2013-03-25 15:19:58 - 185861 loops.
2013-03-25 15:19:59 - 0 loops.
2013-03-25 15:19:59
Waiting planned for 5 seconds - Enter at : 2013-03-25 15:19:54  exit at : 2013-

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec dbms_output.put_line( 'cpu time used: ' || (dbms_utility.get_cpu_time - :n) );
cpu time used: 471

PL/SQL procedure successfully completed.




maybe, just maybe, you can use that code to threaten your dba with - but please do not consider using it. please....


I know, as a shareholder - I should be begging you to use it - think of how many CPU's you'd need to purchase extra - just to "sleep"


Please - the better approach is to bring up in your code review that

a) we have a need to sleep, it is definitely a valid good thing for us to be doing.

b) there is a native way to do it in the database

c) you are being prevented for no scientifically valid reason from using it

d) the alternative is to just burn cpu (but pose this as a joke)


if the "dba" (who is not a dba in my opinion in this case) is afraid of you abusing the other API's for some reason - tell them to create a wrapper that just calls SLEEP so you can access dbms_lock.sleep and nothing else.


but please - don't seriously consider using this function - this is a really bad idea.


Thanks

Carlos, May 30, 2016 - 2:50 pm UTC

Thanks, very helpful. Very helful and fery useful.

other options

Joseph Keller, October 11, 2016 - 7:16 pm UTC

I usually don't post to boards like this, but I was able to shell to unix and perform the /bin/sleep command with the number of seconds I wanted to sleep. Depending on whether you are on windows or unix this should work.

More to Explore

DBMS_LOCK

More on PL/SQL routine DBMS_LOCK here