Oracle Event Delivery Network, subscription of a Database Agent

In the Oracle SOA Suite we can create events by use of the Event Driven Network (EDN).
See my one of my last articles on how to produce such an event from with plsql or how to produce it from bpel.
The next step in the process is to consume these events and execute business logica based on them.

In the SCA composite applications we can use the Mediator to subscribe to the defined events and route them further on to other components in the process (for example bpel).
In the Enterprise Manager we can also subscribe a ‘Database Agent’ to our events.
Go to the soa-infra app > Business Events

Select the event and click the Subscribe button

In the ‘Create Database Subscription’ popup window click the search glass

In the ‘Select: Database Agent’ popup window we will see an overview of the registered database agents.
Click the ‘Create agent’ button.

In the ‘Create Database Agent’ popup windows we can create our agent and point it to our database procedure.

We will create the procedure itself later on.

Creation of the database agent is done.

Go to the ‘Subscriptions’ tab. We will see an overview of all the subscriptions on our events.

At this moment we have 2 subscriptions. One for our database agent and one for the mediator component which we created in the other article.

After these steps the documentation is really lacking of good info. I couldn’t find any details on what the stored procedure interface should be to be able to connect it to the database agent.
First step i did to be able to debug some messages is to put on the edn logging in the database
[sourcecode language=”xml”]
DECLARE
ENABLED NUMBER;
BEGIN
ENABLED := 1;
EDN_ENABLE_LOGGING(
ENABLED => ENABLED);
END;
[/sourcecode]

Now we can query to table ‘EDN_LOG_MESSAGES’ to see the output of the edn process.
In my case i was seeing messages like :
[sourcecode language=”sql”]
488 108 “Error in event subscriber: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘MY_LOG_TABLE_PROCEDURE’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored”
[/sourcecode]

After searching in all the sql object creation files i could track the error message down to the procedure ‘edn_filter_and_deliver_event’.
In here the next part will be executed :
[sourcecode language=”sql”]
execute immediate ‘begin ‘ || subscriber_callback || ‘(:1, :2, :3); end;’
using event.namespace, event.local_name, event;
[/sourcecode]

And the objecttype used for event :
[sourcecode language=”xml”]
create or replace type edn_business_event as object (
namespace varchar2(256),
local_name varchar2(80),
payload sys.xmltype,
compressed_event decimal(10),
decomp_method varchar2(64));
[/sourcecode]

Now we can finally construct the interface of our procedure (MY_LOG_TABLE_PROCEDURE).
It needs 3 parameters : event_namespace (of type varchar2(256), event_local_name (oftype varchar2(80) and event (of type edn_business_event).

Create the stored procedure with the mentioned interface
[sourcecode language=”xml”]
create or replace
PROCEDURE MY_LOG_TABLE_PROCEDURE (namespace in varchar2,local_name in varchar2, my_event in edn_business_event) AS
BEGIN
INSERT INTO MY_LOG_TABLE (log_message) VALUES (my_event.payload.getClobVal());
COMMIT;
END MY_LOG_TABLE_PROCEDURE;
[/sourcecode]

For testing purpose i will only log the event object.
Time to test the database agent.

Trigger an event from the console and check filesystem to see if the process completed.
Now go to the database table and query the ‘MY_LOG_TABLE’ table.

The subscription of the database agent works.
Now we have the opportunity to let components from both the database and middleware subscribe to events.