CODEWITHSUNDEEP
oracle-databaseplsqloracle12c
Auguster
Auguster

Reputation: 375

Oracle Pl/SQL loop to return only when record gets inserted into a table

My requirement is to write a block that finishes execution only when record gets inserted in a table typically what I would do is below 

set serveroutput on;
DECLARE
l_output NUMBER;
BEGIN
select count(*)  INTO l_output from TABLE_NAME where COLUMNNAME = 'DUMMY' and COLUMNNAME1= 'DUMMY1';
FOR i IN 0..l_output 
LOOP
DBMS_OUTPUT.PUT_LINE(l_output);
EXIT WHEN l_output=1;
END LOOP;
END;

But this code block will stop executing if the SQL statement returns no record I want this block to execute until count(*)>1 becomes true again I know this can cause this block to wait indefinitely until record gets inserted into this table.Idea is if count(*)>1 condition is not met the block should keep executing or wait until count(*)>1 condition becomes true.

Upvotes: 0

Views: 1430

Answers (1)

Popeye
Popeye

Reputation: 35910

You need LOOP with EXIT and WAIT as following:

set serveroutput on;
DECLARE
l_output NUMBER;
BEGIN
LOOP
select count(1)  INTO l_output from TABLE_NAME where COLUMNNAME = 'DUMMY' and COLUMNNAME1= 'DUMMY1';
DBMS_OUTPUT.PUT_LINE(l_output);
EXIT WHEN l_output>=1; -- >= is used to avoid infinite loop 
DBMS_LOCK.sleep(1); -- sleep for 1 second, You can change the wait time accordingly
END LOOP;
END;

Cheers!!

Upvotes: 1

Related Questions