How to wait until a query returns rows?

Question

On Oracle DB, how to do the below logic (that is "wait until at least one row is returned and return a column value form it"), but without the polling (looping, wasting CPU and possibly I/O) but with some wait/block mechanism? So when calling the get_one() function it should not return until it can fetch a row from the table matching some conditions.

function get_one()
return number
is
c1 sys_refcursor;
n number;
begin
    loop
        open c1 for select number_column from t1 where some_conditions;
        fetch c1 into n;
        if not c1%notfound then return n;
        close c1;
        dbms_lock.sleep(1); -- this wait reduces load, but is still polling, also it delays reaction time
    end loop;
end;

The solution should work for external applications (like application servers with J2EE, .NET and similar), so use of triggers would probably not fit.

Answer 1

There are two oracle db features that can meet these requirements:

• Database Change Notification (DCN) Java - Oracle Database Change Notification

• Oracle Advanced Queuing (AQ) Oracle Advanced Queue In Java