Oracle and change data notification to keep BigMemory cache in-sync

Provide DB user the ability to execute DBMS AQ package

-- Execute the following using sysdba to use the DBMS_AQ package
--GRANT EXECUTE ON dbms_aq TO scott;
--GRANT aq_administrator_role TO scott;

EXECUTE dbms_aqadm.grant_system_privilege('ENQUEUE_ANY', 'scott', TRUE);
EXECUTE dbms_aqadm.grant_system_privilege('DEQUEUE_ANY', 'scott', TRUE);

Now define a queue that can capture near real time updates to our table of interest

EXECUTE dbms_aqadm.stop_queue(queue_name => 'accounts_queue');
EXECUTE dbms_aqadm.drop_queue(queue_name => 'accounts_queue');
EXECUTE DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'accounts_queue_table');
EXECUTE dbms_aqadm.create_queue_table(queue_table => 'accounts_queue_table',queue_payload_type => 'sys.aq$_jms_text_message',multiple_consumers => false);
EXECUTE dbms_aqadm.create_queue(queue_name => 'accounts_queue', queue_table =>'accounts_queue_table', queue_type => DBMS_AQADM.NORMAL_QUEUE, retention_time => 0, max_retries => 5, retry_delay => 60);
EXECUTE dbms_aqadm.start_queue(queue_name => 'accounts_queue');

-- helper procedure
SET SERVEROUTPUT ON
CREATE OR REPLACE
PROCEDURE enqueueAccount(accountID VARCHAR2)
AS
  msg SYS.AQ$_JMS_TEXT_MESSAGE;
  queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
  msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
  msg_id RAW(16);
BEGIN
  msg := SYS.AQ$_JMS_TEXT_MESSAGE(NULL,NULL,NULL,NULL); 
  msg.text_vc  := accountID;
  msg.text_len := LENGTH(msg.text_vc);
  DBMS_AQ.ENQUEUE(queue_name => 'accounts_queue' , enqueue_options => queue_options , message_properties => msg_props, payload => msg , msgid => msg_id);
END;
/
-- trigger that enqueues messages
CREATE OR REPLACE TRIGGER AccountsTableTrigger 
AFTER INSERT OR UPDATE ON ACCOUNTS
FOR EACH row 
BEGIN
    enqueueAccount(:new.ACCOUNTNUMBER);
END;
/

-- lets now insert a record into our table
insert into ACCOUNTS values('a1','jim',100);
commit;


Now we can simply use Oracle AQ java libraries to dequeue messages infact using JMS APIs.

Note that you will need install the following Oracle jars within your local maven repo manually

<dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc</artifactId>
            <version>11g</version>
            <type>jar</type>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>aqapi</artifactId>
            <version>11g</version>
            <type>jar</type>
        </dependency>
Advertisements