Re: How to delay a sql statement in PL/SQL block

Mark Styles wrote:
>
> 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!

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: danielh_at_parallax.co.uk     
_____________________________________________________________
My views and thoughts aren't necessarily those of my company.