Env: Oracle 12c
I am new to Oracle Advanced Queues (AQ) and it looks like it's supposed to be the best approach to use instead of polling.
Based on this, I want to utilise AQ to be used based on the following trigger:
CREATE OR REPLACE TRIGGER MY_TRG
AFTER UPDATE OF STATUS ON "MY_TABLE"
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
v_status INTEGER;
begin
if :OLD.status = 'ERROR' and (:NEW.status = 'OK' or :NEW.status = 'ERROR') then
--
-- do some Advanced Queue processing here ?
--
end if;
end;
So instead of polling when the STATUS column is updated, is it possible to have some type of CALLBACK feature using AQs?
I basically need a means of knowing when the STATUS column is updated in order to perform some other operation when this occurs.
FYI, I have taken the links/information provided from the comments as well as other sites to base my solution using Oracle Advanced Queues.
Another link that was provided to me for this very purpose was:
https://markhoxey.wordpress.com/2016/03/01/asynchronous-processing-using-aq-callback/
To better answer this, I used the sample code that Mark Hoxey provided within his article, specifically the PL/SQL CALLBACK routine that was invoked via my table trigger, during a STATUS update.
You can see all available code here:
https://onedrive.live.com/?id=B48A94826582EA7D%2158434&cid=B48A94826582EA7D
This is by far the best option to use when it comes to asynchronous processing instead of polling tables with scheduled jobs.