CODEWITHSUNDEEP
oracle-database
David Balažic
David Balažic

Reputation: 1474

How to wait until a query returns rows?

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.

Upvotes: 3

Views: 2752

Answers (3)

SkateScout
SkateScout

Reputation: 870

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

Upvotes: 2

Gui
Gui

Reputation: 296

I am not in favour of implementing code that keeps waiting or polling directly on Oracle. That might skew the Oracle statistics like DB Time and wait times.

In order to implement server code that needs to act upon a certain set of rows being created or modified you can resort to:

  1. A schedule job that wakes up in a predetermined interval and query for the rows. If the rows are present, then it call the procedure that act on the new rows.

  2. Triggers Depending on what it is that is being inserted, you can have a trigger that is called upon the creation of the rows. Beware of the mutant object errors that might arise if you try to modify the original row that has the trigger.

If it is client application that calls "get_one", you might as well have the client application polling it every few seconds based on a timer (no client or DB CPU wasted in between calls).

Upvotes: 0

Multisync
Multisync

Reputation: 8787

function get_one()
return number
is
n number;
begin
    loop
        select (select number_column from t1 where some_conditions) into n from dual;
        if n is null then
          dbms_lock.sleep(1); -- wait 1 second
        else 
          return n;
        end if;   
    end loop;
end;

The DBMS_LOCK package provides an interface to Oracle Lock Management services. You can request a lock of a specific mode, give it a unique name recognizable in another procedure in the same or another instance, change the lock mode, and release it.

You may need some grants to execute this Oracle package

Upvotes: 0

Related Questions