oracle - How to wait until a query returns rows? -

oracle - How to wait until a query returns rows? -

on oracle db, how below logic (that "wait until @ to the lowest degree 1 row returned , homecoming column value form it"), without polling (looping, wasting cpu , perchance i/o) wait/block mechanism? when calling get_one() function should not homecoming until can fetch row table matching conditions.

function get_one() homecoming number c1 sys_refcursor; n number; begin loop open c1 select number_column t1 some_conditions; fetch c1 n; if not c1%notfound homecoming n; close c1; dbms_lock.sleep(1); -- wait reduces load, still polling, delays reaction time end loop; end;

the solution should work external applications (like application servers j2ee, .net , similar), utilize of triggers not fit.

function get_one() homecoming number n number; begin loop select (select number_column t1 some_conditions) n dual; if n null dbms_lock.sleep(1); -- wait 1 sec else homecoming n; end if; end loop; end;

the dbms_lock bundle provides interface oracle lock management services. can request lock of specific mode, give unique name recognizable in procedure in same or instance, alter lock mode, , release it.

you may need grants execute oracle package

oracle