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

	DECLARE
		ENABLED NUMBER;
	BEGIN
		ENABLED := 1;
		EDN_ENABLE_LOGGING(
		ENABLED => ENABLED);
	END;

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 :

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"

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 :

    execute immediate 'begin ' || subscriber_callback || '(:1, :2, :3); end;' 
      using event.namespace, event.local_name, event;

And the objecttype used for event :

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));

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

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;	

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.

Share this Post:
Digg Google Bookmarks reddit Mixx StumbleUpon Technorati Yahoo! Buzz DesignFloat Delicious BlinkList Furl

8 Responses to “Oracle Event Delivery Network, subscription of a Database Agent”

commenter

Thanks Eric for sharing these findings. And for also explaining your approach to the analysis to make up for the lack of documentation. Well done.

Lucas

commenter

Hi Eric,

Its really helpful and thanks for posting.

In which schema the procedure MY_LOG_TABLE_PROCEDURE resides in your example?

Ashok

commenter

I choose the easy way and created it in the soainfra schema. But you could use your own schema and create the grants/synonyms and it should work too

commenter

Marrrrrrvelous!

I wouldn’t go crazy ripping and replacing the DBAdapters in my BPEL process with this ED-SOA approach, though. Make sure your DB use/business case warrants switching from a point-to-point paradigm, to a pub/sub one, first.

commenter

Ik ben per toeval op deze website terecht gekomen. Maar deze website staat nu bij m’n bookmarks. Bewijst maar weer eens dat lang googlen zich altijd uitbetaald…

commenter

Can you give more details on the grants/synonyms we need to change in the schemas? Right now for some reason my business event doesn’t invoke the stored procedure, and I don’t wan’t to change the soainfra schema with my tables and procedures…
Thanks

commenter

Can you try something like this :
create or replace public synonym myproc for myschema.myprocedure
grant execute on myschema.myprocedure to dev_soainfra

haven’t tested it, but it should look a bit like that

commenter

Hi Eric,

It’s a really helpful post and I want to use this but can you tell me if there’s a way to configure the DB Agent /EDN Event subscription without using the SOA interface?
I have been looking for a way to configure via command line or DB inserts but had no luck. Any insights on this?

Thanks and again, great post.

Leave a Reply:

Name (required):
Mail (will not be published) (required):
Website:
Comment (required):
XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>