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

From: Mark Styles <lambic_at_msn.com>
Date: 1996/11/12
Message-ID: <3288B66B.4A8D@msn.com>#1/1

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! Received on Tue Nov 12 1996 - 00:00:00 CST