Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Sleep in PL/SQL?

159 views
Skip to first unread message

Andre Rusanoff

unread,
Mar 14, 1996, 3:00:00 AM3/14/96
to
Chuck Hamilton wrote:
>
> Is there any way to tell PL/SQL to sleep for X seconds before
> proceeding to the next line of code?
> --
> Chuck Hamilton
> chu...@ix.netcom.com
>
> If at first you don't succeed, skydiving isn't for you.

If your PL/SQL is within Forms 4.5, you can use TIMER to do this.

If not, just enter a loop within your PL/SQL block and keep selecting
to_char(sysdate,'SS') until the difference between first and last results
is equal to X and then exit the loop.

AER.

Robert C. Nix

unread,
Mar 14, 1996, 3:00:00 AM3/14/96
to Chuck Hamilton
declare
X integer;
begin
.
.
.
-- assume that X has been assigned a positive integer.
dbms_lock.sleep(X);
.
.
.
end;
/

Chuck Hamilton wrote:
>
> Is there any way to tell PL/SQL to sleep for X seconds before
> proceeding to the next line of code?
> --
> Chuck Hamilton
> chu...@ix.netcom.com
>
> If at first you don't succeed, skydiving isn't for you.

--
_________________________________________________
Robert C. Nix
Oracle Tools Support
mailto:rn...@us.oracle.com

The thoughts, opinions, remarks, statements, ...
expressed here are my own and do not necessarily
represent those of Oracle Corporation.

Lee Levy

unread,
Mar 14, 1996, 3:00:00 AM3/14/96
to
In article <31485a70...@nntp.ix.netcom.com>, chu...@ix.netcom.com (Chuck Hamilton) says:
>
>Is there any way to tell PL/SQL to sleep for X seconds before
>proceeding to the next line of code?
>--
>Chuck Hamilton
>chu...@ix.netcom.com
>
>If at first you don't succeed, skydiving isn't for you.

Not that I have been able to find explicitly, but you can fake it
by putting in a for loop, which has nothing (except a Null) in it.
Some experimentation would be necessary to do the timing.
Actually, how about writing a procedure containig a while loop
that checks sysdate constantly - high overhead, but would work...

If done inside Forms, then you could have a timer running for X seconds,
and constantly check if it is still running (use find_timer)

I'm not aware of any built-in etc that will do it for you, and I have looked.

HIH
Lee
----------------+---------------------------------------------------
This | Lee Levy, ISSD Systems Development, Del Code (34)
space | BHP Information Technology, ACN 006 476 213
left blank | PO Box 261, Warrawong, NSW 2502, Australia
intentionally | PH: +61 42 75-5485 Fax: -5500 Tie: 8855-
in mourning | Internet : levy....@bhp.com.au
----------------+---------------------------------------------------
Opinions expressed are mostly my own, so give me some credit.

Chuck Hamilton

unread,
Mar 14, 1996, 3:00:00 AM3/14/96
to

Gerard H. Pille

unread,
Mar 14, 1996, 3:00:00 AM3/14/96
to
In article <31485a70...@nntp.ix.netcom.com>, Chuck Hamilton
(chu...@ix.netcom.com) says...

I vaguely remember something about timers ...

HEY! WAKE UP!

Oracle 7 Server Application Developers Guide, pag. A-14

dbms_lock.sleep(seconds)

I like that book very much, one keeps learning new things.
Why do you need it, does your program run too fast?
--
Kind reGards
\ / |
X |
/ \ s
Gerard


Klaus Eichler

unread,
Mar 15, 1996, 3:00:00 AM3/15/96
to chu...@ix.netcom.com
>In article <31485a70...@nntp.ix.netcom.com>, chu...@ix.netcom.com (Chuck
Hamilton) says:
>>
>>Is there any way to tell PL/SQL to sleep for X seconds before
>>proceeding to the next line of code?
>>--
>>Chuck Hamilton
>>chu...@ix.netcom.com
>>

DBMS_UTILITY.SLEEP(X);
should do the job

Regards, Klaus
--
Klaus Eichler | Haus 2, 3C7.1 | email ek2...@m30x.nbg.scn.de
Siemens AG, AUT 652 | Moorenbrunn |
Postfach 4848 | Gleiwitzer Str. 555 | Tel +49 911 895-2918
D-90327 Nuernberg | D-90475 Nuernberg | Fax +49 911 895-4802


Vikram Goel

unread,
Mar 15, 1996, 3:00:00 AM3/15/96
to chu...@ix.netcom.com
Chuck,

You can use the dbms_lock.slep(nn) where nn is the time in seconds
to force the execution to sleep.

Hope this helps.

--
Vikram Goel Motorola email: vg...@pts.mot.com
Sr. Oracle DBA - Consultant
Aerotek Inc. My email: vg...@emi.net

Motorola Info:
Mail Stop 39, Room S1014
1500 Gateway Blvd,
Boynton Beach, FL 33426


Scott Urman

unread,
Mar 15, 1996, 3:00:00 AM3/15/96
to
In article <31485a70...@nntp.ix.netcom.com>, chu...@ix.netcom.com (Chuck Hamilton) writes:
|> Is there any way to tell PL/SQL to sleep for X seconds before
|> proceeding to the next line of code?
|> --
|> Chuck Hamilton
|> chu...@ix.netcom.com
|>
|> If at first you don't succeed, skydiving isn't for you.
Yep. Use the DBMS_LOCK.SLEEP procedure. It takes the number of seconds as an
argument. I believe that you can even specify fractions of a second.
--
Scott Urman
"The opinions expressed here are my own, and are not necessarily that of
Oracle Corporation"

Klaus Eichler

unread,
Mar 15, 1996, 3:00:00 AM3/15/96
to chu...@ix.netcom.com
Sorry, try
DBMS_LOCK.SLEEP(X);

Austin Moseley

unread,
Mar 23, 1996, 3:00:00 AM3/23/96
to
Andre Rusanoff <arus...@maxwell.attmail.com> wrote:

>Chuck Hamilton wrote:
>>
>> Is there any way to tell PL/SQL to sleep for X seconds before
>> proceeding to the next line of code?

>If not, just enter a loop within your PL/SQL block and keep selecting

>to_char(sysdate,'SS') until the difference between first and last results
>is equal to X and then exit the loop.


The DBMS sleep call is best. The first thing I'd do when tuning a problem
system is look for these loops, both in the 3gl and the SQL. During peak
loads they'll eat your CPU down, while I/O is cool. Call your hibernate
routine in the 3gl.

-Austin


0 new messages