36 Accessing a PL/SQL Service from a SOA Composite

This chapter describes what a SOA composite application needs to do to access logic implemented as PL/SQL in the database.

When to implement: A SOA composite application needs to access logic implemented as PL/SQL in the database.

Design Pattern Summary: The SOA composite application accesses an ADF Business Components service, which in turn accesses the PL/SQL stored procedure.

Involved components:

• Business component that accesses a PL/SQL stored procedure, and is published as a service.

• SOA composite application which includes a BPEL process service component that accesses the ADF Business Components service.

36.1 Introduction to the Recommended Design Pattern

Oracle Fusion applications may contain stored procedures in the database that a SOA composite application needs to access. The stored procedure must be wrapped by an ADF Business Components service; the BPEL process then accesses the ADF Business Components service.

36.2 Other Approaches

Instead of accessing the stored procedure through an ADF Business Components service, you could use a SOA database binding component. However, this is not allowed because the SOA database binding component does not handle data changes or database schema changes gracefully. In addition, a PL/SQL stored procedure definition cannot be considered a service contract, as there is often some needed extrapolation.

Another alternative is to create a Web service directly on top of PL/SQL. This is not allowed because of security issues in the PL/SQL Web service.

36.3 Example

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

36.4 How to Invoke a PL/SQL Stored Procedure from a SOA Composite Application

Instead of directly accessing the stored procedure, you create a business component that accesses the procedure, you then publish the business component as a SOAP service. The SOA composite application component accesses the ADF Business Components service, which in turn invokes the stored procedure.

To invoke a PL/SQL stored procedure from a SOA composite application:

1. Create a business component, including an application module, as documented in the section "Part II: Building Your Business Services" of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework (Oracle Fusion Applications Edition).

2. Write a method in your application module that accesses the PL/SQL stored procedure. For step-by-step instructions, see the section "Invoking Stored Procedures and Functions" in the chapter "Advanced Business Components Techniques" of theOracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework (Oracle Fusion Applications Edition)

3. Generate the service interface for the business component, as described in "Integrating Web Services Into a Fusion Web Application" in Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework (Oracle Fusion Applications Edition).

4. Invoke it through a SOAP binding, as described in Chapter 34, "Orchestrating ADF Business Components Services."

36.5 Securing the Design Pattern

You secure this design pattern in the same way you secure a pattern with an ADF Business Components service invoked from a SOA composite application. For details, see Section 34.5, "Securing the Design Pattern."

Identity propagation is enabled using Application User Sessions. When the application module initializes, an Application User Session is created with the user who is currently logged in (assuming no such Application User Session yet exists). The Application User Session is pushed to the database, making it accessible from PL/SQL.

For information about accessing the Application User Session, see Section 47, "Implementing Application User Sessions."

For information about securing the design pattern, see Section 50, "Securing Web Services Use Cases."

36.6 Verifying the Deployment

To properly verify this design pattern, test your business component, then deploy and test the SOA composite application.

To verify this design pattern:

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

2. 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.

3. 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.