41 Oracle Data Integrator

This chapter describes issues associated with Oracle Data Integrator. It includes the following topics:

41.1 Configuration Issues and Workarounds

This section describes the following issues and their workarounds:

41.1.1 Generated Templates Including a Datasource With an Oracle RAC URL Fail to Deploy

Oracle WebLogic Configuration Wizard does not support Oracle RAC URLs for datasources. When a datasource is included in a Java EE Agent template that has been generated using Oracle Data Integrator Studio, this template fails to deploy, and raises the following exception is raised:

com.oracle.cie.wizard.WizardController - Uncaught Exception

As a workaround, do not include this datasource definition in the generated template and deploy the datasource from Oracle Data Integrator Studio.

41.1.2 Generated Templates Including Datasources Fail to Deploy

As the Oracle WebLogic Configuration Wizard uses specific code for parsing the URL and exposing its parameters in a user friendly format, it only supports a predefined set of JDBC URLs. If a JavaEE Agent template that has been generated using Oracle Data Integrator Studio contains a datasource with a URL or driver not included in this set, the Wizard fails to use this template with the following exception:

com.oracle.cie.wizard.WizardController - Uncaught Exception

As a workaround, do not include this datasource definition in the generated template, and deploy the datasource from Oracle Data Integrator Studio.

41.1.3 Template Generation Fails When Using the Default Path on Windows

When generating an agent template from Oracle Data Integrator Studio running on a Windows platform, the default path for the target template is set to C:/Documents and Settings/<username>/My Documents/agent_template.jar.

If the template is generated to this default path, the following exception appears:

Exception in thread "main" org.apache.commons.vfs.FileSystemException: Could not create file...

This exception appears because this folder is read-only.

As a workaround, specify another writable target path for the generated template.

41.1.4 Setting Test Queries on WLS Datasources

The Clean Stale Sessions feature of the Agent relies on the stale or dead connections of the connection pool to be automatically cleaned up either through expiration or connection validations tests done by the connection pool implementation.

If the stale connections are not removed out by the connection pool, then the Clean Stale Sessions feature does not work even after the database has been restarted. None of the other requests of the agent will work after the database restart if the agent itself is not restarted.

In a WebLogic Server container, connections are validated every few seconds using a test query. It is recommended to verify and provide the table name used by the database to run the test query: select count(*) from <test_table_name>.

For Master Repositories and Work Repositories, datasources use SNP_LOC_REP and SNP_LOC_REPW as the test tables.

Alternatively, you can use the DUAL table for an Oracle repository.

41.1.5 DBA Credentials Required for Creating Repository

The repository creation (using ODI Studio, Java SDK, or RCU) now requires a dba user and dba password to create or update the Oracle Data Integrator schema version registry entry.

41.1.6 Oracle Web Service Manager (OWSM) Integration with ODI Web Services Not Available on IBM WebSphere Platform

The ability to view policies attached to an ODI Web service on IBM WebSphere through Fusion Middleware Control is not supported in this release.

41.2 Design-Time Environment Issues and Workarounds

This section describes the following issues and their workarounds:

41.2.1 For File Datastores, the View Data operation always looks for global context

Within ODI Studio, when viewing data from within a data store, there must be a global context defined for the datastore.

com.sunopsis.core.SnpsInexistantSchemaException: ODI-17523: There is no connection for this logical schema / context pair: lg1 / GLOBAL.

As a workaround, define a global context for the datastore.

41.2.2 Quick Edit Editor deletes incorrect Joins when sorted

Within ODI Studio Quick Edit, when deleting a join, the incorrect join may be deleted if a join is selected and then the list of joins is sorted.As a workaround sort the join list prior to deleting the join with Quick Edit.

41.2.3 Quick Edit Editor displays a Source as removed regardless of the Selection made in the Warning Dialog

Within ODI Studio Quick Edit, when deleting a source, the source will appear as removed even if "No" is selected from the warning before the deletion.

As a workaround, refresh ODI Studio and open Quick Edit, the source will still be available.

41.2.4 Inconsistent Behavior when Refreshing Load Plans

Within ODI Studio, when refreshing Load Plan to pick latest Scenario reference in the Load Plan, the latest scenario is determined by the Scenario Creation timestamp. During ODI agent execution, the latest scenario is determined by alphabetical ascending order of the Scenario Version string value.

41.2.5 Unable to Overwrite and Refresh Variables in Load Plan Exception Steps

It is not possible to overwrite or refresh the value of a variable in Load Plan Exception steps.

There is no generic workaround at this time.

41.2.6 Generate DDL Does not Show a Constraint Defined in the Model but not in the Database

In certain cases, constraints that are defined in the data model but not in the database, are not displayed in the Generate DDL Wizard. The conditions for showing constraints in the wizard have changed in ODI and above depending on the constraint:

  • Keys: The "Defined in the Database" flags and "Active" in the control tab flag need to be activated.

  • Reference: The "Active in the Database" flag in the Definition tab needs to be activated

  • Conditions: The "Defined in the Database" flags and "Active" in the control tab flag need to be activated

41.2.7 Export Log Feature not Supported for Load Plan Runs

The export of Load Plan Runs is not supported. The current Export Log feature supports the export of Sessions only.

There is no generic workaround at this time.

41.2.8 Incorrect Restart Behavior of Parallel Load Plan Steps

In Load Plans for parallel steps, the behavior of the Max Error Child Count parameter does not change depending on the Restart Type parameter. The currently running child steps are always continued and not killed even if the Restart Type is set to Restart all children.

41.2.9 Markers are not Displayed

The markers are not displayed when the folder contains more than 1000 objects of the same type.

41.2.10 Residual Cachedir Content may Cause Exceptions in Jython Scripts

Jython libraries that ship with ODI have been updated to 2.5.1. When Jython initializes it creates a cache directory (cachedir). Residual cachedir content may cause exceptions in Jython scripts. This problem occurs, for example, when using the IKM SQL to Teradata (TTU). This KM fails at the Load/Pipe Data to Target step with the ImportError: cannot import name _newFunctionThread.

It is recommended to empty existing cacherdir directories before using ODI.

41.2.11 OBIEE Lineage Wizard Does not Appear in Taskbar

The OBIEE Lineage wizard does not appear in the Windows or Linux taskbar when launched. Note that the wizard is displayed as a Java application in the Task Manager.

There is no generic workaround at this time.

41.2.12 Unable to Add a Scenario to a Load Plan via the Lookup Scenario Dialog

A scenario cannot be added to a Load Plan via the Lookup Scenario dialog of the Add Step wizard when the scenario is not attached to a source interface, package, procedure, or variable. This can happen when the scenario is imported without its source object or when creating a Load Plan in a production repository.

As a workaround, drag the scenario from the Designer or Operator Navigator into the Steps Hierarchy table. Oracle Data Integrator automatically creates a Run Scenario step.

41.2.13 Incorrect Join Clause is Generated if it Contains Similar Datastore Aliases

An incorrect join clause is generated when one of the datastore aliases used in the join expression ends like the other alias used in the join expression, as for example TABLE1 in the following expression:


As a workaround, modify the second alias so that it does not have the same ending as the first one. For example:


41.2.14 User Parameters are not Taken into Account by the Runtime Agents

The runtime agents do not take into account the user parameter values specified in the userpref.xml file. The default values are always used instead.

There is no generic workaround at this time.

41.2.15 Unable to Edit Data for Technologies Using Catalog and Schema Qualifiers

When using the Data context menu on a datastore, it is possible to modify the data in the Data Editor. However, an invalid object name error may appear when applying these changes with certain technologies. For example, on Microsoft SQL Server:

[SQLServer JDBCDriver][SQLServer]Invalid object name 'dbo.SRC_CITY'.

This error appears with technologies that require object names qualified by both a catalog (database in the context of Microsoft SQL Server) and a schema name (owner in the context of Microsoft SQL Server).

When fetching the data, the object name is fully qualified (<catalog>.<schema>.<table_name>), but when performing updates, the object is qualified only with the schema name (<schema>.<table_name>) and cannot be found.

There is no generic workaround at this time.

41.2.16 Incorrect Non-ASCII File Names in Zip Export Files

Non-ASCII file names are incorrectly generated in Zip export files. Non-ASCII characters in these file names are replaced with underscores. Setting the Never transform non ASCII characters to underscores user parameter to Yes has no effect. As a workaround, export the files in a folder and compress this folder.

41.2.17 Non-ASCII Characters Incorrectly Converted to Underscore Characters

Non-ASCII characters are handled incorrectly in the following situations, even if the Never transform non ASCII characters to underscores user parameter is set to Yes:

  • Non-ASCII characters are replaced with underscores in the Alias field of a column when using the fixed file reverse-engineering wizard.

  • Non-ASCII characters from the header line are replaced with underscores in column names when doing a delimited file reverse-engineering.

As a workaround, manually modify the column definitions after the reverse process.

41.2.18 Focus Lost in Mapping Property Inspector

In the Interface Editor, when attempting a drag and drop of a source datastore column into the implementation field of the Mapping Property Inspector, the Property Inspector switches to show the source column properties.

This occurs if the focus is in the mapping property inspector.

As a workaround, use the mapping fields on the target datastore for source column drag and drop.

41.3 Oracle Data Integrator Console Issues and Workarounds

This section describes the following issues and their workarounds:

41.3.1 Oracle Data Integrator Console Page Task Flow Resets When Another Tab Is Closed.

When the tab containing a page is closed in Oracle Data Integrator Console, all the tabs after this one reset their task flows (the navigation actions that were made). The pages in these tabs reset to the original object that was opened.

There is no workaround for this issue at this time.

41.3.2 Enterprise Manager Logout Does Not Propagate to Oracle Data Integrator Console

When Enterprise Manager Fusion Middleware Control (EM) is used in conjunction with Oracle Data Integrator Console with Single Sign-On (SSO) activated, a user logging out from SSO in Enterprise Manager might still be able to access Oracle Data Integrator Console pages from the same browser window by typing a direct URL.

This issue is caused by a cookie that is created by Oracle Data Integrator Console not being invalidated by the Enterprise Manager logout action.

As a workaround:

  • Administrators should configure their Enterprise Manager and Oracle Data Integrator Console servers for access fronted by a gateway. The gateway automatically redirects the user that is not logged into SSO to the SSO Login page.

  • If you're accessing Oracle Data Integrator Console or Enterprise Manager from a shared or public computer, close the session by logging out. For additional security, close the browser session.

41.3.3 Standalone Agent Target Does Not Show Current Status

On Oracle WebTier and OPMN deployed on IBM WebSphere, the status for the Standalone Agent target (managed through OPMN) on FMW Console does not update to indicate when the application is running or is shut down. The state "Status Pending" is displayed at all times.

41.3.4 Basic Configuration Cannot Be Done Through FMW Console

On Oracle WebTier and OPMN deployed on IMB WebSphere, the Basic Configuration dialog (select StandAloneAgent, right click and select ODI Console Administration) containing host and port information does not display and therefore cannot be used to make changes to these values.

41.3.5 Error with Variable Handling in Sequences in ODI Studio

Specific sequences do not handle variables correctly in WHERE clause. The initial save works, but after saving the second time, an error occurs.

41.4 Technologies and Knowledge Modules Issues and Workarounds

This section describes the following issues and their workarounds:

41.4.1 For Microsoft Excel Target Datastores, column names must be in upper case

When using Microsoft Excel as a target, column names must be in upper case.

[Microsoft][ODBC Excel Driver]COUNT field incorrect

As a workaround, rename column names within Microsoft Excel.

41.4.2 For File Datastores, reverse-engineering process cannot be canceled

Within ODI Studio, when reverse engineering new columns to an existing datastore, after reverse new columns are automatically saved when answering "No" to "<datastore> has been modified. Save changes?"

41.4.3 SQL Keywords are not imported when importing a Technology in Duplication Mode

Within ODI Studio, when importing a technology in duplication mode, the SQL Keywords are not imported.As as workaround, manually add the SQL Keywords to the technology properties.

41.4.4 SQL Exception "Unknown Token" appears when using Complex File or XML as Staging Area

When running interfaces and using a XML or Complex File schema as the staging area, the "Unknown Token" error appears. This error is caused by the updated HSQL version (2.0). This new version of HSQL requires that table names containing a dollar sign ($) are surrounded by quotes. Temporary tables (Loading, Integration, and so forth) that are created by the Knowledge Modules do not meet this requirement on Complex Files and HSQL technologies.

As a workaround, edit the Physical Schema definitions to remove the dollar sign ($) from all the Work Tables Prefixes. Existing scenarios must be regenerated with these new settings.

41.4.5 Adding Subscribers fails for Consistent Set JKMs when the Model Code Contains Non-ASCII Characters

When the Model code contains non-ASCII characters, adding subscribers does not work for Consistent Set Journalizing KMs. This applies to all Consistent Set JKMs. The Session completes successfully but no subscriber is added. Note that this issue does not occur when the Model code contains exclusively ascii characters.

There is no generic workaround at this time.

41.4.6 IKM and LKM SQL Incremental Update (Row by Row) fail with Non-ASCII Characters

When the source datastore contains non-ascii characters the interface using the IKM or LKM SQL Incremental Update (Row by Row) fails at the step Insert flow into I$ table with the following error message:

org.apache.bsf.BSFException: exception from Jython:
Traceback (most recent call last):  File "<string>", line 149, in <module>
UnicodeEncodeError: 'ascii' codec can't encode characters in position 6-7:
ordinal not in range(128)

There is no generic workaround at this time.

41.4.7 PostgreSQL Technology not Available in the Master Repository Import Wizard

When creating a new master repository using an export of another master, the PostgreSQL Driver is not displayed in the JDBC Driver list of the Master Repository Import wizard.

There is no generic workaround at this time. For more information on certified platforms, see the certification document available on Oracle Technology Network (OTN):


41.4.8 IKM SQL Incremental Update (Row by Row) Cannot Target Japanese Tables

IKM SQL Incremental Update (Row by Row) fails on task Flag rows for update if the target table name contains Japanese characters. In the generated code, the integration table name appears as "I$_??????" where "?" characters replaces a Japanese character.

As a workaround, replace this IKM with another IKM suitable for the technology. For example, 'IKM Oracle Incremental Update' for Oracle.

41.4.9 LKM SQL to Oracle Fails at Load Data Step for NCLOB Data Type

On Oracle Database, an Interface which uses LKM SQL to Oracle fails at the Load Data step when the source and the target data stores are the NCLOB data type.

41.5 Oracle Data Profiling and Oracle Data Quality Issues and Workarounds

This section describes the following issues and their workarounds:

41.5.1 Unable to Connect the Client to a Data Quality Server on UNIX

When connecting from the Oracle Data Quality Client to a Data Quality Server installed on a UNIX machine the following error message may appear: "No support for client application "Admin" version 12.0.1".

To solve this issue re-install the metabase definitions using the following steps:

  1. On the UNIX server go to the following directory:ODQ_HOME/oracledi/odp/Client/SvrSide

  2. Run ./setup

  3. At the prompt, enter the Metabase Installation location: ODQ_HOME/oracledq/metabase_server/

  4. Enter the metabase admin user and password.

  5. When the metabase definitions installation completes, restart the Scheduler service using the following command: ODQ_HOME/oracledq/metabase_server/metabase/bin/scheduler -restart

41.5.2 Menus Are in English When Starting the Client Using a .tss File.

When starting Oracle Data Quality Client by double-clicking on a saved .tss file, the menus appear in English independently of the client machine's locale.

As a workaround, open the client from the shortcut menu, then open the .tss file.

41.5.3 Incorrect Error Message for a Wrong User/Password when Creating an Entity from a Japanese or Chinese Client

When creating an entity, if an incorrect user and password combination is entered for configuring the loader connection, an incorrect message appears in the Create Entity page.

There is no workaround for this issue at this time.

41.5.4 Project Export is Empty if Folder Path Contains Non-ASCII Characters

When a data quality project is exported to a folder which path contains non-ASCII characters, the project folder structure is created with no files.

As a workaround, use a folder path that does not include any non-ASCII characters.