Oracle API

You can use the Oracle API activity to call the PL/SQL APIs that are listed in Oracle Integration Repository. This activity can inspect the Integration Repository for parameter input and output information, and present this information in a user interface for configuration.

Note: A comparison between Oracle API and Custom API is provided in Oracle API and Custom API Comparison.

The Oracle API activity provides the following functions:

  • At design time

    • Loads the PL/SQL procedure information from Oracle Integration Repository.
    • Saves this information to a file.

  • At run time

    • Loads the PL/SQL procedure information from the file.
    • Generates a calling SQL statement.
    • Calls the PL/SQL procedure.
Note: Oracle Integration Repository is shipped with Oracle E-Business Suite 12.0.x, 12.1.x, and 12.2.x; therefore, Oracle API can be used in these versions. See Introduction to Oracle Integration Repository for more details.

General

In the General tab, you can establish a connection to an Oracle E-Business Suite server, specify the API package name and procedure name, and set the timeout time for calling the procedure.

When searching for the API package name, API procedure name, wrapper package name, and wrapper procedure name, it is good practice to enter the name prefixes or as much as you know about the names in the name fields before clicking the search buttons to reduce the search time.

Note: The API Procedure Name label in the General tab refers to the API procedure or function. It is labeled as API Procedure Name because of the limited space available.

The General tab contains the following fields:

Field Module Property? Description
Name No The name of the resource.

The default is OracleAPI.

Shared Connection Yes A shared connection resource containing the Oracle E-Business Suite connection information.

To select a shared connection resource, click the Choose/Create Default Resource icon. All usable connections are listed in the opened dialog. Select one to apply to your current activity. If no matching connection resource is found, click Create Shared Resource to create one. See Oracle E-Business Suite Connection for more details.

Note: For Oracle E-Business Suite 12.2.x, the apps user is used to run the Oracle API activities at run time; therefore, before running the Oracle API activity, ensure that the apps user name that you specified in the Shared Connection is correct. For Oracle E-Business Suite 12.1.x and 12.0.x, the plug-in user is used to run the Oracle API activities at run time; therefore, before running the Oracle API activity, ensure that the plug-in user name that you specified in the Shared Connection is correct.
API Package Name No A business service interface that is exposed by Oracle E-Business Suite.

Click Search Package or the text field. Then, enter a part of the package name in the opened dialog or leave the text field empty, and click Search. Select a package in the opened dialog, and click OK.

API Procedure Name No A procedure from the API Package.

Click Search Procedure or the text field. Then, enter a part of the procedure name in the opened dialog or leave the text field empty, and click Search. Select a procedure in the opened dialog, and click OK.

Wrapper Package Name No

The name given to a package of wrapper procedures or functions.

This field is displayed only when a wrapper package is required.

Click Search Package or the text field. Then, enter a part of the package name in the opened dialog or leave the text field empty, and click Search. Select a package in the opened dialog, and click OK.

Some business objects in the Oracle database cannot be accessed through the Oracle JDBC driver directly. In this case, you must create the wrapper package first by using Oracle JPublisher. See Introduction to Oracle Database JPublisher for details about how to create a wrapper package by using JPublisher.

Wrapper Procedure Name No The name given to a wrapper procedure or function that is used to delegate an API procedure or function.

This field is displayed only when a wrapper procedure or function is required.

Click Search Procedure or the text field. Then, enter a part of the procedure name in the opened dialog or leave the text field empty, and click Search. Select a procedure in the opened dialog, and click OK.

Call Procedure Timeout(s) Yes The maximum waiting time for calling the procedure.

If the procedure call exceeds this limit, the attempt is stopped.

The default is 60. Entering 0 results in an unlimited waiting time.

Generate SQL Script No After configuring the Oracle API activity, click Generate SQL Script. The scripts to be generated are displayed in the opened dialog. Click Generate, and the following two SQL scripts are created in the TIBCO_HOME\bw\palettes\oracleebs\version_number\sql directory:
  • ProcessName_ActivityName_APIPackageName.APIProcedureName.sql
  • ProcessName_ActivityName_APIPackageName.APIProcedureName_undo.sql

Run the ProcessName_ActivityName_APIPackageName.APIProcedureName.sql script in the connected Oracle database before starting the process.

For Oracle E-Business Suite 12.2.x, the plug-in uses the apps user to run the Oracle API activity at run time; therefore, you do not have to generate and run the SQL scripts.

For Oracle E-Business Suite 12.1.x and 12.0.x, the plug-in uses the plug-in user to run the Oracle API activity at run time; therefore, you must generate and run the ProcessName_ActivityName_APIPackageName.APIProcedureName.sql script to grant privileges to the plug-in user so that the plug-in user can access the objects being configured. The plug-in uses the apps user entered in the Oracle E-Business Suite Connection resource to get the information to generate this script. After this script is executed, the plug-in user can access the tables and procedures configured for the activity.

The script with the _undo suffix reverses the granted privileges and undoes the changes to the database. With this operation, you can cleanse the privileges for objects that are no longer used by obsolete or unwanted activities.

Description

In the Description tab, you can enter a short description for the Oracle API activity.

Prerequisite

The Prerequisite tab is used to call some Set Client Info and Initialization procedures before calling the main procedure selected in the General tab. You can add new procedure calls and their corresponding package information in the Prerequisite tab. When the procedure must be wrapped, you must enter a wrapper package, and select a procedure for the wrapper package.

Note: If the procedure name for the wrapper package contains a single dollar sign ($), the $ in the procedure name is converted to a hyphen (-) in the Input and Output tabs.

PLSQL API Details

In the PLSQL API Details tab, you can view the details of the API procedure and wrapper procedure (if wrapper procedure exists), which are configured in the General tab. The details are listed in the Parameter Name, Parameter Type, and Parameter Direction columns.

  • The following figure shows the PLSQL API details without a wrapper package:

  • The following figure shows the PLSQL API details with a wrapper package:

If the API procedure contains parameters of PLSQL data types, such as PLSQL record type, PLSQL table type which is defined in the PLSQL package, and BOOLEAN type, the plug-in requires the related wrapper package and procedure generated from Oracle JPublisher. You must execute the wrapper SQL generated by JPublisher under the apps user.

The Parameter Direction column shows whether the parameter is an input, output, or both for the API procedure.

The Return parameter in the PLSQL API Details tab specifies that GL_JOURNAL_IMPORT_PKG.GET_LAST_SQL is an Oracle database function, and the return value can be used as an output.

Input

In the Input tab, you can view the input data for the Oracle API activity. All parameters of the API procedure or wrapper procedure with the Parameter Direction IN or IN/OUT in the PLSQL API Details tab are listed in the OracleAPI-input column.

Note: The parameters listed in the OracleAPI-input column can be configured manually or automatically by defining a module property. For how to define module properties, see "Using Process and Module Properties" in TIBCO ActiveMatrix BusinessWorks Samples.

Output

In the Output tab, the Arguments node lists all parameters of the API procedure or wrapper procedure with the Parameter Direction OUT or IN/OUT in the PLSQL API Details tab, and the Error_Messages node lists the FND (Oracle Application Object Library) error messages when the API encounters errors in the Oracle E-Business Suite system.

Fault

In the Fault tab, you can find the error messages of the Oracle API activity.

See Plug-in Error Codes for more information about error messages and corrective actions to take.

The Fault tab lists the following error messages:

Fault Thrown When ...
OracleEBSPaletteAQConnectionException The AQ Connection does not initialize.
OracleEBSPaletteConnectionNotFoundException The connection cannot be created.
OracleEBSPaletteSQLException An SQL Exception occurs.
OracleEBSPaletteTimeoutException Calling the procedure for PLSQL API.
Contents
Search Results