33 Initiating a SOA Composite from a PL/SQL Stored Procedure

This chapter describes what a PL/SQL stored procedure needs to do to initiate a SOA composite application.

When to implement:.When a PL/SQL stored procedure needs to initiate a SOA composite application.

Design Pattern Summary: A PL/SQL stored procedure raises an event through the Event Delivery Network within the database. A mediator in the SOA composite application subscribes to the event and routes it as appropriate.

Involved components:

PL/SQL stored procedures
Event Delivery Network database
SOA composite application that includes Oracle Mediator and other components as needed.

33.1 Introduction to the Recommended Design Pattern

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.

33.2 Other Approaches

Instead of using an event to invoke an Oracle Mediator service component from a PL/SQL stored procedure, you could use one of the following implementations.

Invoke Oracle Mediator directly using UTL_HTTP or any other PL/SQL-based APIs.

WARNING:

This approach is prohibited.

33.3 Example

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

33.4 How to Invoke a SOA Composite Application Component Using PL/SQL

To invoke a SOA composite application component from a stored procedure, you must first create the event within the SOA composite application. The stored procedure must then raise the event and pass any required data via the EDN database API.

To invoke a SOA composite application component using PL/SQL:

Create a SOA composite application with an Oracle Mediator component.
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.
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.

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 33-1.

Example 33-1 Calling the publish_event Method

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;

33.5 Securing the Design Pattern

Secure Oracle Mediator by configuring the property runAsRoles=$publisher. For details on securing the Oracle Mediator, see Section 32.6, "Securing the Design Pattern."

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.

To secure this pattern, follow the instructions described in Chapter 50, "Securing Web Services Use Cases."

33.6 Verifying the Deployment

Verifying the deployment involves the following:

Section 33.6.1, "Testing and Deploying the Use Case"
Section 33.6.2, "Verifying the SOA Composite Deployment Using Oracle Enterprise Manager Fusion Middleware Control Console"

33.6.1 Testing and Deploying the Use Case

Testing and deploying the use case involves the following main steps:

Test your Oracle ADF application using various testing and debugging methods described in the chapter "Testing and Debugging ADF Components" of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework (Oracle Fusion Applications Edition). For information about testing the ADF Business Components service, see the chapter "Integrating Web Services Into a Fusion Web Application" in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework (Oracle Fusion Applications Edition).
Deploy the SOA composite application to the standalone WLS where the SOA infrastructure has been installed. Because you created a published event from the SOA composite application to the ADF Business Components service, the ADF Business Components service need not to also be deployed to the SOA infrastructure.
Test the deployed SOA composite service using Oracle Enterprise Manager Fusion Middleware Control Console. Every deployed service has its own test page, so you can quickly test that the service functions as you expect. For more information about using the Fusion Middleware Control Console to test deployed SOA composite applications, see the following chapter:

"Automating Testing SOA Composite Applications" in the Oracle Fusion Middleware Developer's Guide for Oracle SOA Suite.

33.6.2 Verifying the SOA Composite Deployment Using Oracle Enterprise Manager Fusion Middleware Control Console

You can use Oracle Enterprise Manager Fusion Middleware Control Console to verify that the SOA composite was successfully deployed. In Oracle Enterprise Manager Fusion Middleware Control Console, you can select the SOA composite instance and display the result of the event.

Using Oracle Enterprise Manager Fusion Middleware Control Console, you can:

Verify the deployment of the SOA composite.
Test the SOA composite.
Verify the SOA composite test results.

To verify that the SOA composite was successfully deployed and the event was received:

Using a web browser, access the Oracle Enterprise Manager Fusion Middleware Control Console using a URL such as the following:
```
http://<host name>:<port number>/em
```
From the list of applications, expand the PLSQLEvent composite.
In the Last 5 Instances pane, click the most recent instance as shown in Figure 33-1.

Figure 33-1 Finding the Latest PLSQLEvent Composite Instance
In the Flow Trace window that displays, click the Oracle Mediator component, as shown in Figure 33-2.

Figure 33-2 The Flow Trace Window

A window displays, showing the event results, as shown in Figure 33-3.

Figure 33-3 Displaying the Event

33.7 Troubleshooting the Use Case

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. See the troubleshooting section in the chapter "Deploying SOA Composite Applications" of the Oracle SOA Suite Developer's Guide for more information.
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.

33.8 What You May Need to Know About Initiating a SOA Composite from a PL/SQL Stored Procedure

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

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

33.9 Known Issues and Workarounds

Following are known issues:

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