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

Thanks a lot
Armin

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

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

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

TOM,
Your answer to this question was in 2001.

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


Thanks

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...

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

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

Thanks

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
 

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

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. 

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.

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

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!

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;

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.

Thanks, very helpful. Very helful and fery useful.

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.