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

Jun wrote:
> 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

You will probably get ten responses from posters asking what your "business case" is (a legitimate question, especially for your post), so I will leave that to them. I am always interested in questions like this, though, because I know there will be one or two people who will respond with something creative that never would have occurred to me.

As far as I know, there is no way to register with a cursor and/or connection (even in 3GL languages) how long a running query has and/or will take(n), at least reliably. What you could do is write the routine in java, and in your data access routine create a threaded object with a callback function that times out after four seconds (posts your running routine). If it does timeout, you know you haven't gotten your data. If you do get data, post the threaded object to stop keeping track. I would think that would be a good deal of overhead, though.

Having said all of that, how are you going to check every second, and spend four seconds checking? Will you have four sessions running the check? Will that present any business data concerns?

HTH, Steve Received on Sat Nov 25 2006 - 15:07:11 CST