PL/SQL wait for update in Oracle

How do I create PL/SQL function whi开发者_如何学JAVAch waits for update on some row for specified timeout and then returns.

What I want to accomplish is - I have long running process which will update it's status to ASYNC_PROCESS table by process_id. I need function which returns with true/false when this process has completed, but also I need this function to wait some time for this process complete, return on timeout or return imediately with true, when process has completed. I don't want to use sleep(1 sec), because in such case I will be having 1 sec lag. I don't want to use sleep(1 msec), because in such case I am spending cpu resources (and 1msec lag).

Is there a good way how experienced programmer would accomplish this?

That function will be called from .NET (So I need minimal lag between DB operation and .NET/UI)

THNX, Beef

---

I think the most sensible thing to do in this case is to use update triggers on that ASYNC_PROCESS table.

You should also look into the DBMS_ALERT package. Here's an edited excerpt from that doc:

Create an alert:

DBMS_ALERT.REGISTER('emp_table_alert');

Create a trigger on your table to fire the alert:

CREATE TRIGGER emptrig AFTER INSERT ON emp
BEGIN 
   DBMS_ALERT.SIGNAL('emp_table_alert', 'message_text'); 
END;

From your .net code, you can the use something that calls this:

DBMS_ALERT.WAITONE('emp_table_alert', :message, :status, :timeout); 

Make sure you read the docs for what :status and :timeout do.

---

You should look at Oracle Advanced Queuing. It offers the kind of functions your looking for.

You'll probably need a separate queue table where a trigger on ASYNC_PROCESS inserts messages. You then use the AQ functions to retrieve (or wait for) the next message in the queue table.

---

If you're doing this in C#.NET, why wouldn't you simply spawn a worker thread to do the update (via ODAC)? Why hand the responsibility over to Oracle when (it seems) you want a .NET process to make the update call (in background) and have the main process be notified of its completion.

See here and here for examples, although there are several approaches in .NET for this (delegates, events, async callbacks, thread pools, etc)