Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how to loop table every 1 second for no more than 4 seconds

Re: how to loop table every 1 second for no more than 4 seconds

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 27 Nov 2006 12:13:12 -0000
Message-ID: <bs-dnd5XhMPLS_fYRVnytQ@bt.com>


"Jun" <frankjunli_at_gmail.com> wrote in message news:1164485417.289976.104760_at_j44g2000cwa.googlegroups.com...
>I have a problem with PL/SQL stored procedure.
>
> loop, checking the table every 1 second for no more than 4 seconds
> if response that one specifid field in table is changed, return a
> message
> if no response is found in 4 seconds, return a timeout error
>
>
> Thanks in advance for any help
>

Like everyone else, I'd be interested to hear why this is important.

As an outline:

declare a cursor for the query

loop

    use dbms_lock.sleep to wait for the one second     (or the amount of time outstanding)

    get timer value from v$timer

    open cursor
    loop

        get timer value from v$time
        if 4 seconds is up exit
        else
            fetch a row
            process row

    end fetch loop
    close cursor

end timed loop

For better efficiency, you could do array fetching with a LIMIT on the cursor loop.

As a completely different strategy, you could investigate the dbms_alert package. Create a trigger on the target table that sends an alert when the critical data changes. Have the monitoring session simply waiting on the alert. This MAY be adequate for your requirements - and could be a lot less resource-intensive.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Mon Nov 27 2006 - 06:13:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US