Monday, May 9, 2011

EDN via PL/SQL

Scenario:

Raise an event on an insert to a DB table. Event is then consumed by a SOA composite.
In this case, this is a viable alternative to using a DB adapter(polling) as our table, in this scenario, only gets a couple of inserts per day.

XSD used



Create a sample ORDERS table in SCOTT's schema



Create a SOA app/project in JDev

Create an Event Definition in the project



Import the XSD



Here is the edl file



Create a PLSQL stored procedure that will call the following –



Our parameters, taken in part from the edl above, are as follows –
NAMESPACE – http://www.example.org
LOCAL_NAME - anything you like e.g. Our Event Name - NewOrder
PAYLOAD – This is our order





PRIORITY – DEFAULT is 5 so we don’t need to enter this.
However there is some “wrapper” overhead.



eb:business-event xmlns:eb=http://oracle.com/fabric/businessEvent
This is the default, so no need to change.

xmlns:ob="http://schemas.oracle.com/events/edl/NewOrder">
Set to the namespace from our edl.

eb:name ob:NewOrder
This is set to the EventName e.g. NewOrder

eb:content
Contains the actual payload i.e. our Order

Test the PLSQL
There should be an entry in the dev_soainfra.EDN_EVENT_QUEUE_TABLE



Now back to the SOA app...

Create a Mediator that subscribes to the event



Then create a File Adapter(Write) using the same xsd



Deploy and test
- You can execute the procedure directly in JDev
- View the result in em



View the output file



Now we need to include the PLSQL code in the POST-INSERT trigger logic.
Create a POST-Insert trigger on the nc_orders table



Insert a row

3 comments:

EyeMen said...

Thank you for this article

Niall Commiskey said...

My pleasure

Murugesh said...

Thanks Niall for all the BPM related articles like Error handling, notifctions,etc.,. Expecting much more BPM posts.