1. Using the Oracle Database Cloud Service Adapter with Oracle Integration Generation 2
  2. Add the Oracle Database Cloud Service Adapter Connection to an Integration
  3. Table Operation Page

Table Operation Page

You can update or insert multiple records in a single request.

Note:

When you change the structure of a table (for example, you add or delete a column), you must re-import the table by doing a re-edit in the Adapter Endpoint Configuration Wizard. Go to the Import Tables page and re-import the same table, then click OK > Next > Done to complete the wizard. Only then are the table changes reflected in the integration.

Topics:

Import Tables Page

Filter and select the tables to import based on the selected schema. These tables are used to generate a SQL statement based on the operation selected.

You can import the following number of tables:

  • A maximum of three tables for insert, update, and insert or update actions

  • A maximum of five tables for the select - operation on table feature

  • A maximum of five tables for the polling feature

Element Description

Schema

Select the schema to use. The page is refreshed to display the tables available for selection.

Name Filter

Filter the display of tables.

Available

Select the tables on which to insert or update records.

Selected

Displays the selected tables.

Relationships Page

Review the relationships between the selected tables and optionally create, remove, or rename relationships. These relationships are used in the insert or update SQL statements.

Element Description

Relationships Table

Displays the relationships defined on the root database table and any related tables (one-to-one or one-to-many).

Create

Click to create new relationships.

Remove

Click to remove a selected relationship.

Rename

Click to rename a selected relationship.

Create Relationship Page

Specify the parent and child relationships to use in the SQL statement.

Element Description

Parent Table

Select the parent table.

Child Table

Select the child table.

Mapping Type
Select the mapping type (one-to-many, one-to-one, or one-to-one with the foreign key on the child table). For example, if you selected Employees as the parent table and Departments as the child table, the following options are displayed:

  • Employees has a 1:1 Relationship with Departments

  • Employees has a 1:1 Relationship with Departments (Foreign Key on Child table)

  • Employees has a 1:M Relationship with Departments

Parent and Child Table

Associate the foreign key fields to the primary key fields.

Relationship Name

Optionally name the relationship (a default name is generated).

Attribute Filtering Page

Filter out the attributes to exclude.

Element Description

Attributes Tree

Deselect any attributes to exclude from the database query. You cannot exclude primary key attributes.

Advanced Options Page

Provide additional advanced options such as sequencing. This is only valid for the insert and merge operations.

Element Description

Table

Displays the selected table.

Sequence

Specify that the primary key is assigned from a sequence on any insert. Click Search and select a sequence from the list.

The adapter generates sequence numbers in a batch of 50. Configure sequences in increments of 50. This issue only applies to the Oracle Database Adapter.

Operations on Table Page

Select the database tables. To use the bulk extract feature, you must choose the SELECT operation from the Perform an Operation On a Table list on the Basic Info page.

Operations on Table Page

Element Description

Schema

Select the database schema that includes the tables to process.

Table Name

Enter a filter with which to search the schema (for example, %TAB to search for tables with TAB in the name).

Table Type

Specify the table type filter to get a subset of the appropriate database objects, then click Search.

  • ALL

  • MATERIALIZED VIEW

  • MATERIALIZED VIEW LOG

  • SYNONYM

  • TABLE

  • VIEW

Filter By

Enter the initial letters to filter the display of table names.

Table Names

Select the tables to import.

Note: It is recommended that you to import the tables together for the adapter to automatically recognize the relationship. If you import the tables separately, you must explicitly create the table relationship.

Import Tables

Click to import the tables. The page is refreshed for you to select the parent database table.

Select the parent database table

Select the parent (root) table from the list. If using multiple related tables, this is the top-level parent table in the relationship. After making your selection, the page is refreshed for you to view and edit the table relationships.

Add || Remove Tables

Click to add more tables or remove tables no longer in use.

Review and manage parent database table relationships

Click Edit to view and edit the table relationships. The relationships automatically identified by the adapter are displayed. See Table 3-1.

Review and filter columns from selected database tables

Click Edit to view and edit the table attributes. You can deselect any attributes to exclude from the database queries. Primary key attributes cannot be excluded. See Table 3-2.

Review and edit SQL query

Click Edit to view and edit the default SQL query. See Table 3-3.

Note: This field is available for a Select operation on the table.

Review and manage parent database table relationships Option

Table 3-1 - Review and manage parent database table relationships Option

Element Description

Create New

Click to create a new relationship.

Relations

View the existing parent and child table relations automatically created by the adapter.

Review and filter columns from selected database tables Option

Table 3-2 - Review and filter columns from selected database tables Option

Element Description

Attributes Tree

View and deselect attributes automatically created by the adapter.

Review and edit SQL query Option

Note:

This is only applicable for a Select operation on a table.

Table 3-3 - Review and edit SQL query Option

Element Description

SQL Edit

Click to manually edit the query in the SQL Query field.

Edit using Expression Builder

Click to edit the query in the Expression Builder.

  • Add New: Click to add new criteria to the SQL query.

    1. Click Add New.

    2. In the First Argument field, click Edit, and select the argument to add (for example, deptno).

    3. In the Operator field, select the operator to use for the comparison from the dropdown list (for example, =).

    4. In the Second Argument field, select the option to use:

      • Literal: Click to specify a value. If selected, you are prompted to select the data type (for example, integer) and specify the value.

      • Parameter: Click to specify a bind parameter.

      • Query Key: Click to run the comparison against another column in the table.

    New criteria is appended to the SQL query with a WHERE clause. If you add subsequent SQL queries, they are appended to the SQL query with an AND clause

  • Add Nested: Click to add nested criteria to the SQL query.

  • Edit: Click the edit the SQL criteria you specified.

  • Remove: Click the edit the SQL criteria you specified.

Click to edit the query with the Expression Builder.

Maximum Number of Records to be fetched

Select the number of records to fetch with this SQL query.