I have below requirement.
session 1
create table test(status number);
below trigger on test table
CREATE OR REPLACE TRIGGER TEST_TRG AFTER INSERT ON TEST
FOR EACH ROW
DECLARE
l_alert_nm varchar2(1000);
BEGIN
l_alert_nm := 'STATUS';
SBS_ALERT_PKG.signal_alert(l_alert_nm, 'NEW RECORD INSERTED INTO PBA_BLKOUT_QUEUE_T TABLE for this data receipt id :'||:new.STATUS);
END;
DECLARE
l_alert_nm varchar2(4000) := 'STATUS';
l_keeplooking boolean := True;
l_status number;
L_CNT NUMBER;
msg varchar2(1000);
status number;
begin
dbms_alert.register(l_alert_nm);
while l_keeplooking
loop
BEGIN
select status into l_status from test where rownum=1;
L_KEEPLOOKING := False;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
dbms_output.put_line('wait for some time');
dbms_alert.waitone(l_alert_nm,msg,status,300);
END ;
end loop;
dbms_alert.remove(l_alert_nm);
end;
sesseion 2:
i am inserting below statement.
insert into test values (10);
commit;
after inserting in session 2. session 1 waiting for 5 minute .it is not walk up whenever i am inserting record into test table. Please help on this me