3

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.

David Balažic
  • 1,319
  • 1
  • 23
  • 50
  • Maybe you can leverage the resources used by this function, by what about callers methods? They will be stuck waiting for the information, I'm not sure how it could look like from the resources perspective. – Sebas Oct 29 '14 at 17:39
  • Waiting for an event is quite a common programming pattern, the callers will be fine. – David Balažic Oct 30 '14 at 09:24

3 Answers3

1

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

SkateScout
  • 815
  • 14
  • 24
0
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

Multisync
  • 8,657
  • 1
  • 16
  • 20
0

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).

Gui
  • 296
  • 1
  • 7
  • [DBMS_AQ.LISTEN](http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_aq.htm#ARPLS099) is a standard procedure that waits on the DB, so waiting on the DB should not be a problem. – David Balažic Apr 04 '17 at 13:45