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