CODEWITHSUNDEEP
oracle-databaseplsqloracle12cadvanced-queuing
tonyf
tonyf

Reputation: 35557

Using Oracle Advanced Queues to monitor when a database table column is updated

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.

Upvotes: 0

Views: 1228

Answers (2)

Alexo Po.
Alexo Po.

Reputation: 127

Not very clear question. Is "MY_TABLE" is a table for AQ Queue? If so, then look towards AQ "native" callbacks (just as you ask).

Not exhaustive example, see docs for full variations.

declare
   p$queue varchar2(30) := 'YOU_QUEUE';
   p$call_back_procedure_name varchar2(30) := 'you_cb_proc';
   p$consumer varchar2(30) := 'YOU_CONS'; 
   m$reg   SYS.AQ$_REG_INFO_LIST;
begin
    m$reg := SYS.AQ$_REG_INFO_LIST();

    m$reg.extend();
    m$reg(1) := SYS.AQ$_REG_INFO(
        name => p$queue || ':' || p$consumer,
        namespace => DBMS_AQ.NAMESPACE_AQ,
        callback => 'plsql://' || p$call_back_procedure_name || '?PR=1',
        context => null,
        qosflags => DBMS_AQ.NTFN_QOS_RELIABLE,
        timeout => 0
    );

    dbms_aq.register(m$reg, 1);
end;
/

More in docs: https://docs.oracle.com/database/121/ARPLS/d_aq.htm#ARPLS100

Upvotes: 0

tonyf
tonyf

Reputation: 35557

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.

Upvotes: 0

Related Questions