1. Using the Oracle Autonomous Transaction Processing Adapter with Oracle Integration Generation 2
  2. Add the Oracle Autonomous Transaction Processing Adapter Connection to an Integration
  3. Invoke Stored Procedure Page

Invoke Stored Procedure Page

Enter the invoke stored procedure values. The Invoke a Stored Procedure page appears when you select Invoke a Stored Procedure as the operation to perform on the Basic Info page.

You can specify the following values on the Invoke a Stored Procedure page.

  • Select the database schema that includes the data you want to query (for example, you want to query details about an employee based on their employee ID).

  • Select a stored procedure or package from the list that is displayed after you select the database schema.

Note:

  • Stored procedures return binary large objects (for example, BLOB database data types) as base64Binary types in XML. Depending upon the use cases, these can be decoded during transformation using inbuilt functions such as decodeBase64 or can be passed as-is for downstream processing.
  • Adapter input/output parameters are defined based on the stored procedure IN/OUT parameters. The IN parameter corresponds to the request and the OUT parameter is translated as the response. Procedures without parameters are not listed in the Adapter Endpoint Configuration Wizard for database versions 18c and above. You can pass a dummy parameter or create a wrapper procedure with a dummy parameter to list it in the wizard.
  • Stored procedures that contain Boolean or %rowtype as IN/OUT parameters are not supported. You can create wrappers on top of these stored procedures to use them.
Element Description

Select Schema

Select a database schema from the list. This action refreshes the page to display fields for selecting a package or procedure to invoke. Special characters (for example, #) are not supported in schema names. See Special Characters are Not Supported in Schema Names.

Select Package

Select the database package. This action refreshes the page to display the procedures available for the package.

When importing a predefined integration package containing PLS or SQL stored procedures, the wrapper package is not recreated in the target database. To add the wrapper package, confirm JPublisher is installed on the target database and define the original stored procedure. After confirming JPublisher is installed and the stored procedure is defined, open the PL/SQL Wrapper utility and execute the add scripts command to add the scripts included in the exported inventory archives (IAR) file.

Select Procedure

Displays the in (inbound), out (outbound), and in/out (inbound/outbound) parameters for the selected package.

Arguments

Display the in, out, and in/out parameters that are passed with this procedure.