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 > Configure the module for deployment > Configure the module for outbound processing > Selecting and configuring business objects

Selecting and configuring stored procedures and stored functions

To select and configure business objects that correspond to stored procedures and stored functions in the database, you filter the database objects, and specify the configuration properties for the database object.

To select and configure business objects for stored procedures or stored functions, you require to understand the structure of the data in the database and know what objects the module requires to access. In particular, you require to know the parameters passed to the stored procedures or stored functions that your module requires to access.

This task is performed through the external service wizard. Start in the Find Objects in the Enterprise System window and then work in a Specify the Configuration Properties for 'object' window that is specific to the business object you are configuring.


Procedure

  1. In the Discovered objects list of the Find Objects in the Enterprise System window, expand the node for the schema that contains the stored procedure or stored function you want to work with.
  2. Filter the stored procedures by specifying a valid name or pattern for at least one of the filter fields in the Filter Properties window.

    1. Click Stored Procedures and then click the

      (Create or edit filter) button, located at the top of the Discovered objects pane.

    2. In the Filter Properties window, type a name or pattern in the Object name or pattern field.
      Use the question mark or underscore (? or _ ) to match a single character and the asterisk or percentage (* or %) to match multiple characters. The name is case-sensitive.

    3. In the Catalog name or pattern field, type the name or a pattern. Use the question mark or underscore (? or _ ) to match a single character and the asterisk or percentage (* or %) to match multiple characters.

    4. Click OK. The Stored Procedures node displays all the stored procedures that match the given filter condition.

      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 will be added based on the overload sequence.

  3. Select one or more objects from the Stored Procedures list, and click the > (Add) button to add the object to the Selected objects list.

    Stored procedures that are defined in the PL/SQL packages are displayed in the format SPName( PackageName).

    For example, if the EMP_MGMT package contains the CREATE_DEPT stored procedure, the stored procedure is displayed in the list as CREATE_DEPT(EMP_MGMT).

    The Specify the Configuration Properties for 'object' window lists the attributes of the stored procedure business object, which include the names and data types of the parameters of the stored procedure, and information about the result sets that are returned. If the stored procedure contains any complex data type (such as Array, Object, and Table), the type name and the sub attribute details are also automatically discovered and displayed. If the stored procedure contains the Oracle PL/SQL Record data type, 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 PL/SQL Record data type.

  4. If the stored procedure returns any result set, make sure that the value for the The maximum number of ResultSets returned from the stored procedure field reflects the maximum number expected. The wizard creates the required number of result set business objects to hold the results.

  5. Configure each parameter:

    1. The Data type field displays the data type of the parameter.

    2. In the Sample Value field, type a valid value.

      If the stored procedure or function contains a VArray or an Object attribute with a Date data type, the sample value must be given in a specific format to successfully validate the stored procedure or function. The Date data type can also be mapped to the String data type. Therefore, the format for the sample value depends on the data type to which the Date is mapped. The following table provides the format to be followed for each Date data type mapping.

      Date data type formats
      Data type mapping SP/SF with Date data type VArray/Object in SP/SF with Date data type
      Date to String Format: Day-Month-Year (Example: '01-JAN-0001') Format: Year-Month-Day Hour:Minute:Second.[Millisecond] (Example: '0001-01-01 01:00:00.000000000').
      Date to Date Format:Year-Month-Day (Example: '0001-01-01') Format:Year-Month-Day (Example '0001-01-01')

      • The Year parameter can be specified from a minimum of one digit to a maximum of four digits in all the formats.

      • The Month parameter can be specified from a minimum of one digit to a maximum of two digits in all the formats, except in Date to String mapping of the SP/SF with the Date data type where the Month parameter is specified in three alphabetic characters as specified in the example in the previous table.

      • The Day, Hour, Minute, and Second parameters can be specified from a minimum of one digit to a maximum of two digits in all the formats.

      • The Milliseconds, which is an optional parameter, can be specified from a minimum of one digit to a maximum of nine digits in all the formats.

  6. To validate the syntax of the stored procedure using the sample values, click Validate. The result of the validation is displayed in the Result area.

    Ensure that the number of result sets is correct after you validate the syntax of the stored procedure because the Oracle driver does not always return the expected result set information.

    If the number is not correct after validation, set the correct number, and then click OK to save and close the window. After you close the wizard, you might verify the setting in the MaxNumOfRetRS application-specific parameter for the business object.

    If the Result area displays the Validation failed message, use the error message that is displayed before the Validation failed message, to correct the definition. Ensure that the data type of the parameters and the sample values are correct.

    The OracleMetadataDiscovery.log file in the .metadata folder of your workspace contains the additional information about the problem if the logging level includes a WARNING.

    The following window is displayed after a stored procedure has been validated.

    When you see the message Validation was successful, click OK to save the definition of the stored procedure business object.

    If the stored procedure or stored function returns a result set, do not click OK until the validation succeeds. The wizard uses the results returned during validation to create business objects to hold the result. If the stored procedure validation is not successful, the adapter does not return the result set at run time.

  7. To change the configuration of an object from the Selected objects list, select the object name and then click the

    (Edit) icon.


Results

The business objects you configured for stored procedures and stored functions are listed in the Find Objects in the Enterprise System window.


What to do next

In the Find Objects in the Enterprise System window, continue to select and configure other types of business objects.

When you are finished, click Next to set the global properties.

Selecting and configuring business objects


Related concepts:

Business objects

Database tables with multiple parent tables


Related tasks:

Selecting and configuring tables, views, and synonyms or nicknames for outbound processing

Selecting and configuring query business objects


Related reference:

Business object information