Define a Select Operation on Database Tables

You can define a SELECT operation to perform against database tables. This section provides a high level overview of creating an integration in which an Oracle Database Adapter is configured as an invoke connection to retrieve table records from the Oracle Database.

To define a SELECT operation on database tables:

1. Configure SOAP Adapter and Oracle Database Adapter connections.
2. Select App Driven Orchestration in the Create Integration - Select a Style dialog.
3. Add and configure the SOAP Adapter as a trigger connection in the integration.
   The SOAP Adapter is configured to accept an input and return the response received from the invoke connection.
4. Add the Oracle Database Adapter as an invoke connection in the integration.
   This invokes the Adapter Endpoint Configuration Wizard.
5. On the Basic Info page, select Perform an Operation On a Table as the type of operation to perform and Select as the operation to perform on the table.
6. On the Operate On Table page, specify the schema and tables to import, and click Import Tables. For this example, the following values are specified.

   • Schema: HR

   • Table Type: TABLE

   • Table Name: %TAB

   • Selected Tables: EMPLOYEE_TAB and DEPARTMENT_TAB. The tables are imported together for the Oracle Database Adapter to recognize the relation between the tables.

   
   
   Description of the illustration oradatabase1.png
   
   The page is refreshed for you to select the parent (root) database table.
7. Select the parent table (for this example, DEPARTMENT_TAB is selected).
   This page enables you to:

   • View the automatically created table relationships and create new ones.

   • View and deselect attributes.

   • View and edit the automatically created SQL query.

   
   
   Description of the illustration oradatabase_tables.png
   
8. If you want to edit the automatically created SQL query, click Edit to the right of Review and edit SQL Query.
   1. Click Edit using Expression Builder. You can also manually edit the SQL query by clicking SQL Edit.
   2. Click Add New to add new criteria to the SQL query. The automatically created SQL query is displayed below the link.
      
      
   3. Specify values for the following fields, and click OK.

      • First Argument

      • Operator

      • Second Argument

      For example:
      
      Description of the illustration oradatabase_sql6.png
      

      The criteria you specify are appended to the existing SQL query as part of a WHERE clause. Any additional SQL query criteria you specify are appended as part of an AND clause. For example:
      
      Description of the illustration oradatabase_sql7.png
      

   4. Click OK.
9. Click Next.
10. View your selections on the Summary page. Links to the tables you selected to import and SQL query you specified are provided.
    
    Description of the illustration oradatabase_summary.png
    
11. Click Done to exit the Adapter Endpoint Configuration Wizard.
12. Complete the integration by performing mapping and tracking tasks.
13. Activate the integration.
14. Copy the link to invoke the integration from under the How to Run link.
15. Invoke the integration from a tool such as the SOAP UI.
16. Review the values returned by the Oracle Database Adapter.