IBM BPM, V8.0.1, All platforms > Authoring services in Integration Designer > Services and service-related functions > Access external services with adapters > Configure and using adapters > IBM WebSphere Adapters > Oracle E-Business Suite > Overview of IBM WebSphere Adapter for Oracle E-Business Suite > Technical overview
Stored procedure business object overview
You can create a stored procedure business object that corresponds to a stored procedure or stored function in the database.
You can then use the Execute operation to run the stored procedure against the data in the database.
The external service wizard helps you to build stored procedure business objects that run a stored procedure or stored function. The wizard examines the stored procedure or stored function in the database to create the business object. A stored procedure business object has an attribute for each parameter.
For validating the stored procedure attributes, a sample value parameter is provided with each attribute. The sample value parameter is provided for both simple and complex data type attributes. The wizard uses the sample values that you provide to validate the stored procedure before saving it. The adapter uses the result that the stored procedure returns to validate the parameters, to obtain the maximum number of result sets returned, and to use the metadata of these result sets to generate child business objects. The wizard generates the hierarchy for stored procedure business objects automatically if you validate the stored procedure business object.
For both simple and complex data type attributes, the adapter automatically discovers and displays the data type and type name for each attribute of the stored procedure. If the stored procedure has input/output parameters or returns value parameters that are of complex data types such as the Struct or Array, the data type and the corresponding user-defined type name are saved in the SPComplexParameterTypeName property.
If the stored procedure contains the Oracle PL/SQL data type such as Record, the adapter creates a wrapper package with a wrapper stored procedure which converts the Record data type to Object data type so that the Oracle E-Business Suite adapter can support the Oracle PL/SQL Record data type. The names of the wrapper packages and stored procedures created for this purpose comply with the Oracle database object naming conventions along with the appropriate suffixes to differentiate them from the Oracle database object names. The names of the wrapper package and wrapper stored procedure consists of both the original package and stored procedure names along with the appropriate suffixes such as “_WPKG” and “_W” (for example, PKGA_PROC7_REC_TAB_WPKG.PKGA_PROC7_REC_TAB_W, where “PKGA” is the original package name, “PROC7_REC_TAB” is the original stored procedure name, “_WPKG” is the suffix for the package, and “_W” is the suffix for stored procedure). For each selected overloaded stored procedure and function which has PL/SQL Record type parameters, the adapter creates wrapper stored procedure in specific wrapper package with two different number tag (for example, "XXXX_WPKG01 and XXXX_WPKG02").
In the generated Wrapper for SP/SF with Record type parameter, if you select the boolean type parameter as TRUE or False during runtime, the adapter processes this data type automatically.
WebSphere Adapter for Oracle E-Business Suite distinguishes the original SP/SF from the overloaded ones by a number tag that corresponds to an overload sequence in the Oracle database. These corresponding parameters for the selected SP/SF is added based on the overload sequence.
If the stored procedure returns a result set, you need to set the number of result sets returned from this stored procedure in the MaxNumberOfResultSets property. This value represents the maximum number of result sets that are handled by the adapter run time.
During discovery and at run time, the WebSphere Adapter for Oracle E-Business Suite expects the returned result set from the stored procedure execution to contain columns with names. Some stored procedures return result set with unnamed columns.
For example, a stored procedure with the SQL statements like the examples that follow return result set with unnamed columns:
SELECT COUNT(*) FROM EMPLOYEE;
SELECT 111,222,333 FROM CUSTOMER;
Oracle processes such SQL SELECT statements by assigning "dummy" names to the table columns in the returned result set- like count(*) or d1, d2, d3 for the respective select statement examples as shown.
If the returned result set contains table columns with no names (because the database did not assign dummy names), the adapter creates dummy names for such columns.
Dummy column names, generated by either the database or by the adapter, are assigned to the attributes of the stored procedure business object.
The behavior (by the adapter or by the database) of assigning dummy names to unnamed table columns ensures that the stored procedure runs successfully during discovery and at run time.
For stored procedure business objects, the wizard supports nested Struct and Array objects, and can support any number of layers of nested hierarchy. The wizard can generate corresponding child business objects for all these nested Struct and Array objects.
| Property name | Type | Description |
|---|---|---|
| SPComplexParameterType | String | Value can be one of:
Array |
| SPComplexParameterTypeName | String | The name of the user-defined type. This property is required when the value of SPComplexParameterType is Struct or Array. |
| MaxNumberOfResultSets | Integer | The maximum number of returned result sets to be handled by the Adapter for Oracle E-Business Suite run time. |
Technical overview of IBM WebSphere Adapter for Oracle E-Business Suite