35 Initiate a SOA Composite from a PL/SQL Stored Procedure

Oracle Fusion applications may contain stored procedures that need to invoke a component within a SOA composite application, such as a BPEL process service component. A stored procedure can use the Event Delivery Network database API to publish an event whose payload is xmltype. An Oracle Mediator service component subscribes to the event by event name or by using a XPath expression on the event payload. The.edl file (event definition file) for the event can be supplied in the composite or deployed separately in a MAR (metadata archive). When the stored procedure publishes the event, the subscribed Oracle Mediator service component forwards the payload to the BPEL process service component.

This chapter explains how to implement the recommended approach.

The sample code for this use case can be downloaded from Oracle SOA Suite samples.

1. Create a SOA composite application with an Oracle Mediator component.
2. Configure Oracle Mediator to subscribe to a new event (with a name of your choosing).

   The event filter can be by event name or using an XPath expression on the event payload and the EDL for the event can either be supplied in the composite.xml or deployed separately in a MAR.

3. Create the SOA composite application component that will be invoked (for example, a BPEL process service component), and create a wire between the Oracle Mediator component reference and the component service.
4. From a PL/SQL stored procedure, call the EDN-DB API method publish_event with the event namespace and the event payload as a CLOB type. An example is shown in Example 35-1.

DECLARE
  NAMESPACE VARCHAR2(200);
  LOCAL_NAME VARCHAR2(200);
  PAYLOAD CLOB;
BEGIN
  NAMESPACE := 'http://xmlns.oracle.com/SubEventMediator/EventDefinition1';
  LOCAL_NAME := 'CustomerEvent';
  PAYLOAD := to_clob('<eb:business-event xmlns:eb=
   "http://oracle.com/fabric/businessEvent"
   xmlns:ob="http://xmlns.oracle.com/SubEventMediator/EventDefinition1">
   <eb:name>ob:CustomerEvent</eb:name><eb:content><CU:CustomerData
   xmlns:CU="http://xmlns.oracle.com/Esb/CustomerData"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <CustomerId>A22-9AXC2</CustomerId><CustomerName>
   Deserae International</CustomerName><Type>Gold</Type><Description>Accounting
   Outsourcing Partner</Description><Address>3228 Massilon Blvd</Address>
   <City>Juniper</City><State>Massachusetts</State><Zip>01854</Zip><Country>US
   </Country><Phone>877-555-9876</Phone><Status>Active</Status>
   <CreditRating>5</CreditRating><Discount>0</Discount><Terms>30n4</Terms>
   <EnrollDate>01/1/01</EnrollDate><LastOrderDate>05/05/05/</LastOrderDate>
   <Currency>USD</Currency><ContactName>Jan Forester</ContactName><ContactTitle>VP
   Finance</ContactTitle><ContactPhone>877-555-9000</ContactPhone><AccountRep>
   Geoff Seattle</AccountRep><CampaignRating>2</CampaignRating>
   <ReferedBy>Houston America Taxco</ReferedBy>
   </CU:CustomerData></eb:content></eb:business-event>');
 
  EDN_PUBLISH_EVENT( NAMESPACE => NAMESPACE, LOCAL_NAME => LOCAL_NAME, PAYLOAD => PAYLOAD);
END;

Secure Oracle Mediator by configuring the property runAsRoles=$publisher.

When the database connection is established from the middle tier so as to invoke the PL/SQL stored procedure, a session is established with the appropriate identity. This identity is propagated through EDN back to the middle tier for the subscription. The subscription runs as the identity of the publisher.

Verifying the deployment involves the following:

• Testing and Deploying the Use Case

• Verifying the SOA Composite Deployment Using

Following are tips that may help resolve common issues that arise when developing or running this use case.

• Enable logging for Oracle Mediator using logging.xml.

• For the events functionality, use the Event Delivery Network database log page at http://host:port/soa-infra/events/edn-db-log. The EDN schema name is FUSION_EDN.

Before you implement these design patterns, be aware of the following:

• Run the sample provided before implementing your own version of this use case. Running the sample ensures that the EDN database queue works as expected.

Following are known issues:

• Event publishing is an asynchronous action, there is no support for synchronous event publishing.