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 query business objects

Select and configure query business objects for user-defined SELECT statements for use in your module.

To configure query business objects, you must know the structure of the data in your database, including the tables and views. You require to know the name and data type of the columns that your module requires to access. You must also be able to write the SQL SELECT statements.

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 Query Statements node. This node contains an object template, named Select Statement n, for each query business object you requested in the Specify the Query Properties window.

    For example, if you specified a count of two query business objects in that window, the Discovered objects list contains two object templates, as illustrated in the following figure.

  2. Select one or more of the object templates and click the > (Add) button to add the objects to the Selected objects list. The following figure displays the Specify the Configuration Properties for 'object' window that opens when you click > (Add) for a query business object.

  3. In Name of the business object, type a name for the business object. The name can contain spaces and national language characters.

  4. In Select statement, type the SELECT statement you want to run. Indicate each parameter with a question mark (?). The following sample SELECT statements illustrate the flexibility of the query business object:

    • select * from customer where ccode=?
    • select * from customer where id=? and age=?
    • select * from customer where lname like ?
    • select C.pkey, C.fname, A.city from customer C, address A WHERE (C.pkey = A.custid) AND (C.fname like ?)

    As you type each ?, the window expands to display an area where you define the WHERE clause for that parameter. The following figure displays the Specify the Configuration Properties for 'object' window for a query business object that has a single parameter.

  5. In Where clause parameter n, provide information about each parameter in the SELECT statement.

    1. In Parameter type, select the data type of the parameter. For Oracle databases, the adapter does not support the complex types such as array, table, structure, or nested structure as parameters in the query business objects.

    2. In Sample value, type a sample value for the parameter.

    For example, for a parameter corresponding to a column containing the family name of the customer, you might select string as the data type and provide a sample value of Smith.

  6. Click the Validate button to validate the syntax of the select statement using the sample values. Result displays the result of the validation.

    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.

  7. To specify the data type mapping for each column in the result set returned by the select statement:

    1. Click Advanced.
    2. Expand Result set returned by the Select statement. For each column in the result set, the default data type mapping is displayed. For Oracle databases, if the query result contains any complex data type, such as an array, structure, nested structure or table, the type name and the child attribute details are also automatically discovered and displayed.
    3. Review the mapping and make the required changes.

  8. Click OK to save the definition of the query business object.


Results

The query business objects you defined are now 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:

Query business object overview


Related tasks:

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

Selecting and configuring stored procedures and stored functions