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.
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.
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.
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:
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.
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 .
To properly verify this design pattern, test your business component, then deploy and test the SOA composite application.
To verify this design pattern:
Related Links
The following documents provide additional information related to subjects discussed in this section:
For more information about testing and debugging Oracle ADF applications, see the chapter "Testing and Debugging ADF Components" of the Developing Fusion Web Applications with Oracle Application Development Framework.
For information about testing the ADF Business Components service, see the chapter "Integrating Web Services Into a Fusion Web Application" in the Developing Fusion Web Applications with Oracle Application Development Framework.
For more information about testing and debugging, see the chapter "Automating Testing SOA Composite Applications" in the Developing SOA Applications with Oracle SOA Suite.