Extending OEDQ to Oracle DB AQ

OEDQ can be characterized as a re-active application. Subscribing or Publishing systems interact through web services. The problem with web services is that they are synchronous. This means that a request is sent and a response is recieved by the same application. There are scenarios when data quality results need to be pulbished to multiple systems, not just the application calling the web service. The below article describes how to publish the results of a request payload to multiple applications through the use of an Advanced Queue (AQ).


Purpose: Configuring OEDQ to publish results to an Oracle Database Advanced Queue (AQ)

Components Needed:
- Oracle Data Base Advanced Queue
- Custom Script Processor
- JDBC connection string to Oracle DB

Steps:
1. Create PL/SQL script to insert into Oracle DB AQ. Script needs to do the following:
     a. Recieve Input as part of call
     b. Create payload with input
     c. Insert payload to AQ
     d. Commit

2. Add OEDQ Script processor
3. Create script using Goovy to do the following:
     a. Import groovy.sql syntax/library
     b. Define input variable
     c. Create new instance to db using JDBC connection
     d. Call PL/SQL package and/or stored procedure
     e. Pass input variable as part of previous step

Example PL/SQL to AQ Script:

create or replace
PROCEDURE ( p_payload IN  CLOB ) as
 o_payload         XXX;
 enq_msgid          RAW(16);
 eopt               dbms_aq.enqueue_options_t;
 mprop              dbms_aq.message_properties_t;
 err_message        varchar2(2000);
 v_payload         VARCHAR2(5000);
 len               BINARY_INTEGER;
Begin
  o_payload := XXX( p_payload  );
  eopt.sequence_deviation := NULL;
  mprop.priority :=1;
  dbms_aq.enqueue( 'AQ Name', eopt, mprop, o_payload  , enq_msgid);
  Commit;
END;


Example Groovy to AQ Script:

#! language : groovy
import groovy.sql.Sql
import groovy.xml.MarkupBuilder
def inputs =
    '<?xml version="1.0" encoding="UTF-8" ?>' + '<Payload xmlns="http.xxx.xxx.com' + "<Root Node>" + "<Node1>" + input1[0] + "</Node1>" + "<Node2>" + input1[1] + "</Node2>"  + ...etc. +"</RootNode>"
sql = Sql.newInstance("jdbc:oracle:thin:@hostname:port:SID, "Username", "Password", "oracle.jdbc.OracleDriver" )
sql = sql.call("call Procedure to insert into AQ('$inputs')")