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')")
