1. Using Integrations in Oracle Integration 3
  2. Map Data and Create Lookups
  3. Manage Lookups

Manage Lookups

A lookup associates values used by one application for a specific field with the values used by other applications for the same field. This capability enables you to map values across vocabularies or systems. For example, you can map country codes, city codes, currency codes, and so on.

Topics:

Note:

Specifying a lookup value that includes a special character of # is not supported.

Create a Lookup

Create a lookup to map values between applications.

  1. Decide where to start:
    • Work in a project (see why working with projects is preferred).
      1. In the navigation pane, click Projects.
      2. Select the project name.
      3. Click Integrations Integrations icon.
      4. In the Lookups section, click Add if no lookups currently exist or + if lookups already exist. The Add lookup panel opens.
      5. Click Create. The Create lookup panel opens.
    • Work outside a project.
      1. In the navigation pane, click Design, then Lookups.
      2. In the banner, click Create. The Create lookup panel opens.
  2. Enter the following information.
    Field Description

    Name

    Provide a meaningful name so that others can understand the lookup. You can include English alphabetic characters, numbers, underscores, and dashes in the identifier.

    Keywords Enter keywords (tags) to identify the lookup. You can search for lookups using keywords.

    Description

    Provide a meaningful description so that others can understand the lookup.

  3. Click Create.

Add Adapters or Domain Names to a Lookup

Add adapters or domain names to a lookup to map values between connections.

  1. Click Domain_Name_1.
    A menu with options is displayed.
  2. Select the type to create:

    • Replace with adapter: This selection invokes the Select adapter dialog for choosing the adapter to use. You can change your selection:

      • If you want to change your adapter selection, click the selected adapter type, then select a different adapter.

      • If you want to change from an adapter to a domain name, click the selected adapter type and change to a domain name.

    • Edit domain name: This selection invokes a dialog for entering the domain name to use. There is a 50-character limit. You can change your selection:

      • If you want to change from a domain name to an adapter, click the domain name, then select Replace with adapter.

      • If you want to change the domain name, click the name, then select Edit domain name.

      The adapter or domain name is displayed.

  3. Enter a value in the field after the adapter or domain name. To add more rows for additional values, click the + sign.
  4. Click Domain_Name_2.
  5. Repeat Step 2 to add an adapter or domain name.
  6. Enter a value in the field after the adapter or domain name. To add more rows for additional values, click the + sign.
  7. If you want to add another column, click +.
  8. Repeat to add more adapters and domain names to the lookup.
  9. Click Save when complete.

Clone a Lookup

You can clone a copy of an existing lookup in Oracle Integration. Cloning provides a quick way to create a new lookup with similar information. Lookups with adapters and domain names can be cloned.

  1. Decide where to start:
    • Work in a project.
      1. In the navigation pane, click Projects.
      2. Select the project name.
      3. Click Integrations Integrations icon.
      4. In the Lookups section, hover over the lookup to clone.
    • Work outside a project.
      1. In the navigation pane, click Design, then Lookups.
      2. Hover over the lookup to clone.
  2. Click Actions Actions icon, then select Clone.
    The Clone lookup panel opens.
  3. Enter a name and an optional keyword and description.
  4. Click Clone.

Delete a Lookup

You can delete lookups in Oracle Integration that are not currently in use by integrations. Attempting to delete a lookup in use would invalidate the integration and cause an active integration to stop running. You must first remove the lookup from the integration and then delete the lookup.

  1. Decide where to start:
    • Work in a project.
      1. In the navigation pane, click Projects.
      2. Select the project name.
      3. Click Integrations Integrations icon.
      4. In the Lookups section, hover over the lookup to delete.
    • Work outside a project.
      1. In the navigation pane, click Design, then Lookups.
      2. Hover over the lookup to delete.
  2. Click Actions Actions icon, then select Delete.
    The Delete lookup dialog is displayed.

  3. Click Delete when prompted to confirm.

Edit a Lookup

You can edit a lookup.

  1. Decide where to start:
    • Work in a project.
      1. In the navigation pane, click Projects.
      2. Select the project name.
      3. Click Integrations Integrations icon.
      4. In the Lookups section, find the lookup to edit.
    • Work outside a project.
      1. In the navigation pane, click Design, then Lookups.
      2. Hover over the lookup to edit.
  2. Click Actions Actions icon and select Edit.

Update Lookups in Active Integrations

Lookups are not deployed as part of integration activation. Therefore, changes that you make to lookups already used in active integrations typically take effect immediately. There is no need to re-activate integrations using a changed lookup for the new lookup value to take effect.

There can be instances in which updates to the runtime environment are slow because it is not in real-time and queuing can occur at runtime. For those cases, updates can occur in a couple of minutes.

Export a Lookup

Once you create a lookup, you can export that lookup for use in other Oracle Integration environments.

  1. Decide where to start:
    • Work in a project.
      1. In the navigation pane, click Projects.
      2. Select the project name.
      3. Click Integrations Integrations icon.
      4. In the Lookups section, click the name of the lookup to export.
    • Work outside a project.
      1. In the navigation pane, click Design, then Lookups.
      2. Hover over the lookup to export.
  2. Click Actions Actions icon, then select Export to CVS.
The file is saved as a CSV file with the same name as the lookup.

Import a Lookup

You can import lookups that were previously exported from Oracle Integration.

The file to import must have the following for the first row, where table_name is the name of the table as you want it to appear in Oracle Integration. This name cannot contain spaces.
DVM,table_name
  1. Locate the CSV file containing the lookup table you want to import.
  2. Decide where to start:
    • Work in a project.
      1. In the navigation pane, click Projects.
      2. Select the project name.
      3. Click Integrations Integrations icon.
      4. In the Lookups section, click Add if no lookups currently exist or + if lookups already exist. The Add lookup panel opens.
      5. Click Import. The Import lookup panel opens.
    • Work outside a project.
      1. In the navigation pane, click Design, then Lookups.
      2. In the banner, click Import. The Import lookup panel opens.
  3. Click Drag and Drop to select a lookup or drag one to this field.
  4. Click Import.
    If a lookup already exists with the same identifier and version, you must confirm whether to overwrite the existing lookup.
The imported lookup appears in the Lookups list. You can customize or activate it, if it is ready.

Create the lookupValue Function

You can create the parameter values for the lookupValue function with the Map Lookup Value Wizard. This wizard enables you to define the lookup table, source column, target column, and default value to use in the function. For these parameter values to be selectable in the wizard, you must have already created a lookup on the Lookups page.

Topics:

Access the Build Lookup Function Wizard

The Map Lookup Value Wizard for creating the lookupValue function parameter values is accessible from the mapper or the Expression Builder in actions that support functions in Oracle Integration.

s

Note:

You must already have created lookups to use this wizard.

From the Mapper

  1. In the upper right corner, click Toggle functions Mapper functions icon to launch the Components panel.
  2. Type lookupValue in the Search field, and click Search.
  3. Drag the function onto the target element node.

    The Map Lookup Value Wizard is displayed.


    The Map Lookup Value panel shows the wizard steps at the top: Select Lookup, Select Columns, Select Default, and Summary. After this are the lookup names, the number of columns, and the information icons.

From the Expression Builder in an Action that Supports Functions

You can access the Map Lookup Value Wizard in the Expression Builder in an action that supports functions, such as a switch, stitch, or others.

  1. Open an integration for editing.

  2. Open an action that supports functions in the integration (for this example, a switch action is shown, but other actions such as a stitch can be used).

    The Configure route panel opens.

  3. Manually enter a value, select a value from the drop-down list, or drag a value from the Sources tree to the first Value field. For this example, tracking_var_1 is selected.
  4. Select an operator in the Operator field.

  5. Click Functions, and browse or search for lookupValue.

  6. Click lookupValue.
  7. View the description after the function for information about the supported parameters. You define values for these parameters in the Map Lookup Value Wizard.

    • dvm-location

    • src-column

    • src-value

    • target-column

    • default-value

  8. Click Switch to Developer View Switch view icon in the second Value field.

  9. Drag the lookupValue function into the second Value field.


    The lookupValue function is dragged to the second Value field in the Configure route panel.

    The Map Lookup Value Wizard is displayed.


    The Map Lookup Value panel shows the wizard steps at the top: Select Lookup, Select Columns, Select Default, and Summary. After this are the lookup names, the number of columns, and the information icons.

    Create the lookupValue function parameter values.

Select the Lookup

You can select the lookup to use in the lookupValue function. You must already have created a lookup. Otherwise, no lookups are displayed for selection.

  1. Select the lookup to use in the function. You can view the lookup description by clicking the information icon in the row. This can guide you in selecting the required lookup. The number of columns defined in the lookup is also displayed.

  2. Click Next >.

Select the Source and Target Columns

You can select the source and target columns to use in the lookupValue function. The lookupValue function requires one source column and one target column. When you select the source and target columns, the values available with the columns are displayed.

  1. Select the source and target columns to use in the lookupValue function.
    Element Description

    Select Source

    Click the source column header to select from a list of available columns for this lookup table. The data included with the selected column is displayed. Both adapter and domain name columns are displayed.

    Select Target

    Click the target column header to select from a list of available columns for this lookup table. The data included with the selected column is displayed. Both adapter and domain name columns are displayed.
  2. Click Next >.

Specify the Default Value

You must specify a default value to use.

  1. Enter the default value to use in the lookupValue function if no match is found. If there is no match that satisfies all the search values, the lookup fails and the default value is returned (for example, an actual default value to use or an error message such as No Value Found).
  2. Click Next >.

Review Your Lookup Table Selections

You can review the lookup table values to use in the lookupValue function on the Summary page.

  1. Review the lookup table values. The Summary page is the final wizard page after you complete your configuration.
    Element Description

    Parameter and Value Table

    Displays a summary of the parameters and values you defined on previous pages of the wizard.

    To return to a previous page to update any values, click the appropriate tab in the left panel or click Back Back icon.

    Resulting Expression

    Displays the expression you defined on the previous pages of the wizard. The lookupValue function takes the following format: 

     lookupValue(dvm-location, src-column, src-value, target-column, default-value)

    Where:

    • Lookup Table: The lookup table selected on the Select lookup page.

    • Source Column: The source column selected on the Select columns page.

    • Source Value: The source value you enter in the mapper or Expression Builder after completing this wizard. Complete this wizard, then define the srcValue parameter value.

    • Target Column: The target column selected on the Select columns page.

    • Default Value: The default value entered on the Default value page.

    For example, a defined lookupValue function after you have completed the wizard and defined the srcValue parameter value in the Expression Builder can look as follows:
    dvm:lookupValue('countrycode', 'countrycode', srcValuePlaceHolder, 'name',
    'No data found')

    For example:


    The Select Lookup, Select Columns, Select Default, and Summary icons are shown. Below this is the Summary section. Below this is the lookup selected (countrycode) and the parameter and value assigned. Below this is the resulting expression. Below this are Cancel and Done buttons.

  2. Click Done.
  3. In the Sources tree, drag a value to replace srcValuePlaceHolder in the Value field.


    The Input Sources tab (selected) and Functions tab are shown. The $tracking_var_1 element is selected. On the right side is the Value field, Operator field, and Value field. In the second value field, $tracking_var_1 is being dropped on srcValuePlaceHolder in the expression.

  4. Click Save.

    You can use the configured function in the mapper. See Add Functions, Operators, and XSLT Statements in Using the Oracle Mapper with Oracle Integration 3.