38 Access 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.

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

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

38.3 Example

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

38.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.
  2. Write a method in your application module that accesses the PL/SQL stored procedure.
  3. Generate the service interface for the business component.
  4. Invoke it through a SOAP binding, as described in Orchestrating ADF Business Components Services .

Related Links

The following documents provide additional information related to subjects discussed in this section:

  • For more information about creating a business component with an application module, see the section "Part II: Building Your Business Services" of the Developing Fusion Web Applications with Oracle Application Development Framework.

  • For more information about accessing PL/SQL stored procedures, see the section "Invoking Stored Procedures and Functions" in the chapter "Advanced Business Components Techniques" of the Developing Fusion Web Applications with Oracle Application Development Framework

  • For more information about generating a service interface for a business component, see the chapter "Integrating Web Services Into a Fusion Web Application" in Developing Fusion Web Applications with Oracle Application Development Framework.

38.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 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 Implementing Application User Sessions.

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

38.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.
  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:

Related Links

The following documents provide additional information related to subjects discussed in this section: