SOA 12c - DB Adapter (Call a Stored Procedure or Function)

Here we go!
Information that you need to know:

The Adapter Configuration Stored Procedures is used to generate an adapter service WSDL and the necessary XSD. The adapter service WSDL encapsulates the underlying stored procedure or function as a Web Service with a WSIF JCA binding. The XSD file describes the procedure or function, including all the parameters and their types. This XSD provides the definition used to create instance XML that is submitted to the Oracle Database Adapter at run time. For more detailed information read the following URL: https://docs.oracle.com/html/E10231_03/adptr_db.htm#CHDFBBCD

In this post, we will learn how create a SOA Project with Database Adapter - Operation Type: Call a Stored Procedure or Function

The next images are the SOA Project execution:

The first step is create a database package with a stored procedure.

Note: The next procedure is used just to make an arithmetic operation and assign the operation result to out variable.

For example:

Once created the package in the Oracle Database, we are going to open JDeveloper IDE and click on File menu > New > Application.

For example:

On the Application window, set the next values, and click on next button.

Application Name	DatabaseAdapter
Directory	C:\JDeveloper\mywork\DatabaseAdapter
Application Package Prefix

For example:

On the Project window, set the next values, and click on next button.

Project Name	CallStoredProcedure
Directory	C:\JDeveloper\mywork\DatabaseAdapter\CallStoredProcedure

For example:

On the Configure SOA settings window, set the next values, and click on finish button.

Standard Composite

Empty Composite

For example:

After clicking on finish button, the JDeveloper IDE has the next appearance:

Then the next step, on External References section right click > Insert > Database, then enter the dbCallStoredProcedure value on Service Name, then click on next button.

For Example:

Select the Oracle JDBC connection, then click on next.

Note: If you need additional information about how create a Oracle JDBC connection click on the next URL: Create Oracle DB Connection

For Example:

Then on Operation Type window, chose the Call a Stored Procedure or Function option, then click on next button.

Then on Specify Stored Procedure window, chose the Stored Procedure that you created, in my case I chose the XXISCFGG_DATABASE_ADAPTER_PKG package > CallStoredProcedure procedure, then click on ok button, then next button.

For Example:

Then on Advanced Options window, we keep the same values settings by default, then click on next button.

For Example:

Then on JCA Endpoint Properties window, we keep the same values settings by default, then click on next button.

For Example:

Then on Finish window, click on finish button.

For Example:

Then the next step, we need to select the BPEL Process component, this component is located on the right side, on Components section, when you found this, select it and drag it to Components section on work area.

For Example:

On the Create BPEL Process window, set the next values, and click on ok button.

BPEL Specification	BPEL 2.0 Specification
Name	BPELCallStoredProcedure
Namespace	http://xmlns.oracle.com/DatabaseAdapter/CallStoredProcedure/BPELCallStoredProcedure
Directory	C:\JDeveloper\mywork\DatabaseAdapter\CallStoredProcedure\SOA\BPEL
Template Type	WebService
Template	Asynchronous BPEL Process
Service Name	bpelcallstoredprocedure_client
Expose as a SOAP Service	Checked
Delivery	async.persist
Transaction	async.persist
Input	{http://xmlns.oracle.com/DatabaseAdapter/CallStoredProcedure/BPELCallStoredProcedure}process
Output	{http://xmlns.oracle.com/DatabaseAdapter/CallStoredProcedure/BPELCallStoredProcedure}processResponse

For example:

Now we need drag to add a new Reference, with this action we will have available the Partner Links on .bpel file.

For example:

Now we are going to do double click on component: BPELCallStoredProcedure, then on BPELCallStoredProcedure.bpel file, we need to select the Invoke activity and drag to work area, just in middle of receiveInput and callBackClient and change the name from Invoke1 to InvokeCallStoredProcedure

For example:

Then we need to drag onto a Partner Link to make a Web Service connection. On the Edit Invoke window, set the next values, and click on ok button.

Input Variable	dbCallStoredProcedureIn
Type	{http://xmlns.oracle.com/pcbpel/adapter/db/DatabaseAdapter/CallStoredProcedure/dbCallStoredProcedure}args_in_msg
Output Variable	dbCallStoredProcedureOut
Type	{http://xmlns.oracle.com/pcbpel/adapter/db/DatabaseAdapter/CallStoredProcedure/dbCallStoredProcedure}args_out_msg

For example:

Then click on Apply button, next click on Ok button,then click on Save All button.

Now in this moment we have the Invoke activity to Database Web Service, nevertheless the call don't has any parameters values, having saying that, the next step is assign two values for X and Y variables.
We need to drag the Assign activity to work area between receiveInput and InvokeCallStoredProcedure components.

For example:

Then change the activity assign name from Assign1 to AssignCallStoredProcedureIn.

For example:

Now we need to double click on AssignCallStoredProcedureIn activity.

On the Edit Assign window, set the next values, and click on Ok button.

$dbCallStoredProcedureIn.InputParameters/ns2:X	3
$dbCallStoredProcedureIn.InputParameters/ns2:Y	4

For example:

The next step is assign the Z variable value to processResponse variable.
We need to drag the Assign activity to work area between InvokeCallStoredProcedure and callBackClient components.

For example:

Then change the activity assign name from Assign1 to AssignCallStoredProcedureOut.

For example:

Now we need to double click on AssignCallStoredProcedureOut activity.

On the Edit Assign window, set the next values, and click on Ok button, then click on save all.

$dbCallStoredProcedureOut.OutputParameters/ns2:Z

$outputVariable.payload/client:result

For example:

Now the next step is create a jar file to can deploy it on WebLogic Server, we need to select the CallStoredProcedure project and righ click, then chose the CallStoredProcedure... option.