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

How to delay a sql statement in PL/SQL block

2,137 views
Skip to first unread message

L. Tseng

unread,
Nov 7, 1996, 3:00:00 AM11/7/96
to

I'd like to do the following:

LOOP

delay 5 seconds;

select...;
delete ...;
update...;
END LOOP;

Does anyone know how to implement this? I noticed 'DELAY'
is a reserved word in PL/SQL but could not find any info
about it. Is this the right one to use and how?
Thanks,

Leslie

Thomas J. Kyte

unread,
Nov 7, 1996, 3:00:00 AM11/7/96
to

dbms_lock.sleep(5) will do this for you

Thomas Kyte
Oracle Government
tk...@us.oracle.com

http://govt.us.oracle.com

---- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...


-------------------
statements and opinions are mine and do not necessarily
reflect the opinions of Oracle Corporation

Scott Urman

unread,
Nov 7, 1996, 3:00:00 AM11/7/96
to L. Tseng

In article <55t5pe$5...@nntp1.u.washington.edu>, les...@u.washington.edu (L. Tseng) writes:
|> I'd like to do the following:
|>
|> LOOP
|>
|> delay 5 seconds;
|>
|> select...;
|> delete ...;
|> update...;
|> END LOOP;
|>
|> Does anyone know how to implement this? I noticed 'DELAY'
|> is a reserved word in PL/SQL but could not find any info
|> about it. Is this the right one to use and how?

Use DBMS_LOCK.SLEEP. It takes the number of seconds to sleep as an argument.
You can specify a resolution down to 1/100th of a second.

|> Thanks,
|>
|> Leslie
------------------------------------------------------------------------
Scott Urman Oracle Corporation sur...@us.oracle.com
------------------------------------------------------------------------
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2
Published by Oracle Press - http://www.osborne.com/oracle/index.htm
------------------------------------------------------------------------
"The opinions expressed here are my own, and are not necessarily that of
Oracle Corporation"
------------------------------------------------------------------------

Mark Styles

unread,
Nov 12, 1996, 3:00:00 AM11/12/96
to

L. Tseng wrote:
> I'd like to do the following:
>
> LOOP
>
> delay 5 seconds;
>
> select...;
> delete ...;
> update...;
> END LOOP;
>
> Does anyone know how to implement this? I noticed 'DELAY'
> is a reserved word in PL/SQL but could not find any info
> about it. Is this the right one to use and how?

(newsgroup list trimmed)

I don't know why DELAY is a reserved word, it doesn't seem to
be a valid command, but there are ways to implement a delay, you
could just do a tight loop counting to a high number, but the
length of this delay would not be accurate. Another way would
be to use something like the DBMS_PIPE package, if you read from
a pipe that does not exist, your procecure will hang until the
specified timeout is exceeded, so something like:

BEGIN
DBMS_PIPE.RECEIVE_MESSAGE('NON_EXISTANT_PIPE',300);
select...
delete...
update...
END;

should do the job. Of course, if someone starts writing to a pipe
called 'NON_EXISTANT_PIPE', then this will go wrong!

Danny Healy

unread,
Nov 13, 1996, 3:00:00 AM11/13/96
to

The SLEEP function in the DBMS_LOCK package will put a procedure to
sleep for a specified number of seconds (up to hundredth second
accuracy).

Regards,
Danny
--

__ Daniel Healy _____________________________________________
Parallax Solutions Ltd, Middlemarch,Coventry, CV3 4FJ,
UK.
Phone: 01203 514400 x 4526 Fax: 01203 514401
EMail: dan...@parallax.co.uk
_____________________________________________________________
My views and thoughts aren't necessarily those of my company.

0 new messages