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 procedures used in place of or in addition to operations
You can specify that the adapter use a stored procedure in the database in place of, before, or after the SQL statements that the adapter uses to perform an operation. Each business object can have a different set of stored procedures used with each operation.
The adapter can use simple SQL statements for Create, Update, Delete, Retrieve, or RetrieveAll operations. The column names used in the SQL statements are derived from an attribute application-specific information. The WHERE clause is constructed using key values specified in the business object. Each query spans one table only, unless posted to a view. However, you can replace or enhance the SQL statement provided by the adapter using stored procedures and stored functions.
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.
The adapter can call a stored procedure or stored function in the following circumstances:
- Before processing a business object, to perform preparatory operational processes
- After processing a business object, to perform actions after the operation
- To perform a set of operations on a business object, instead of using a simple Create, Update, Delete, Retrieve, or RetrieveAll statement.
In a hierarchical business object, if you want the stored procedure to be performed for each business object in the hierarchy, you must separately associate a stored procedure with the top-level business object and each child business object or an array of business objects. If you associate a stored procedure with the top-level business object but do not associate it with each child business object, then the top-level business object is processed with the stored procedure, but the child business objects are processed using the standard SQL query.
Table 1 lists the application-specific information elements for a stored procedure and describes their purpose and use. A complete description of each element is provided in the sections that follow the table. A screen showing the stored procedure definition for a business object is shown in View of business object with stored procedure definition.
| Descriptive name | Element name | Purpose |
|---|---|---|
| Stored procedure type | StoredProcedureType | The stored procedure type defines the type of stored procedure to be used and determines when the stored
procedure is called. For example, before processing a business object. |
| Stored procedure name | StoredProcedureName | The name of the stored procedure that is associated with the appropriate StoredProcedureType. |
| Result set | ResultSet | This value specifies whether the stored procedure returns a result set. If the result set is returned, a multiple-cardinality child for the current business object is created using the values returned in the result set rows. |
| Parameters | Parameters | Each Parameters element describes one parameter for a stored procedure or stored function. |
| Return value | ReturnValue | A value that indicates it is a function call, not a procedure call, because the value is returned by the stored procedure. |
Stored procedure type
The stored procedure type defines the type of stored procedure to be used and determines when the stored procedure is called.
For example, before processing a business object.
| Required | Yes |
|---|---|
| Default | None |
| Possible values | Can be one of:
Operation specifies one of the operation names: Create, Update, Delete, Retrieve, or RetrieveAll. |
| Bidirectional transformation supported | No |
| Property type | String |
| Usage notes | Stored procedure types associated with RetrieveAll apply to top-level business objects only.
You can remove any selected application-specific information from the StoredProcedureType property. All the corresponding operation application-specific information property groups are also removed. |
| Examples |
|
Stored procedure name
The name of the stored procedure that is associated with the appropriate StoredProcedureType.
| Required | Yes |
|---|---|
| Default | None |
| Bidirectional transformation supported | Yes |
| Property type | String |
Result set
This value determines whether the stored procedure returns a result or not. If the result set is returned, a multiple-cardinality child for the current business object is created using the values returned in the result set rows.
| Required | Yes |
|---|---|
| Default | None |
| Possible values |
True |
| Bidirectional transformation supported | No |
| Property type | Boolean |
| Usage notes | If your stored procedure returns a result set, use the business object editor after finishing the external service wizard to verify that this attribute is set to true. The Oracle JDBC driver does not always return this value correctly. |
Parameters
There is one Parameters element for each parameter for a stored procedure or stored function. Each Parameters element defines the name and type of one parameter.
| Required | Yes |
|---|---|
| Default | None |
| Contents | Each Parameters element specifies
the following information:
|
| Bidirectional transformation supported | No |
| Property type | String |
| Usage notes |
A result set can be returned only as an output parameter. In that case, one of the parameters must have the type RS, to indicate a result set. |
Return value
A value that indicates it is a function call, not a procedure call, because a value is returned.
| Required | No |
|---|---|
| Default | None |
| Possible values | Can be RS or the name of a business object attribute or child business object. |
| Bidirectional transformation supported | No |
| Property type | String |
| Usage notes | If the returned value is RS, the returned value is a result set and is used to create the multiple-cardinality
container corresponding to this business object. If the returned value is the name of an attribute, the value is assigned to that particular attribute in the business object. If the attribute is another child
business object, the adapter returns an error.
When you associate a stored procedure with a business object that is generated from a table or view, and if the stored procedure is a function, a value is returned from this stored procedure. One ReturnValue application-specific information value is added to the operation application-specific information. The existence of this application-specific information implies that it is a function call and not a procedure call, because a value is being retuned by the function. If the value of this application-specific information is a business object attribute name, the returned value is assigned to that particular attribute in the business object. If the value of this application-specific information is another child business object, the adapter run time returns an error. In summary, if the returned value is of a simple data type, the wizard enables you to bind one business object attribute to it, and the value of this application-specific information is set to the name of that business object attribute. But if the returned value is a result set, the wizard sets the value of this application-specific information to RS. A result set can be returned as an output parameter or as a returned value if it is a stored function. The type of the output parameter is set to RS to indicate that this parameter is used to return a result set. |
View of business object with stored procedure definition
The following Properties view screen shows the customer business object that has the associated stored procedure information for RetrieveSP and AfterRetrieveSP for the Retrieve operation. The adapter runs the RTASSER.RETR_CUSTNAME stored procedure in place of the standard SQL to retrieve a table business object. After the business object is retrieved, the adapter runs the RTASSER.RETR_CUSTINFO stored procedure.
Technical overview of IBM WebSphere Adapter for Oracle E-Business Suite