My Oracle Support Banner

Incorrect Column Data Types Are Used During External Table Creation When Executing An ODI 12c Mapping (Doc ID 2463446.1)

Last updated on OCTOBER 26, 2018

Applies to:

Oracle Data Integrator - Version 12.2.1.2.6 to 12.2.1.3.1 [Release 12c]
Information in this document applies to any platform.

Symptoms

An Integration Interface in Oracle Data Integrator (ODI) 11g uses a knowledge module that calls the getColList() substitution method with a SOURCE_CRE_DT pattern to create an external table:

create external table <%=snpRef.getTargetTable( "TABLE_NAME")%>_ext
(
<%=odiRef.getColList("", "\t[COL_NAME]\t[SOURCE_CRE_DT] ", ",\n", "", "")%>
)

The execution of the Integration Interface in ODI 11g correctly generates the column datatypes.

For example:

create external table MY_TABLE_ext
(
  COL1 VARCHAR(20),
  COL2 CHAR(2),
  ...
)

After upgrading the above to ODI 12.2.1.2.6 or later, the column datatypes are incorrect:

create external table MY_TABLE_ext
(
  COL1 STRING(20),
  COL2 STRING(2),
  ...
)

Additionally, the execution of 12c Mapping would fail with error messages such as:

ODI-1227: Task Create external table...- fails on the source connection MySRCDataServer.
Caused By: org.netezza.error.NzSQLException: ERROR: 'create external table MY_TABLE_ext
 (
  COL1 STRING(20) ,
  COL2 STRING(2) ,
  ...
 )
 ...
error ^ found "(" (at char 68) expecting next item or end of list

  at org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:276)
  at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
  at org.netezza.sql.NzConnection.execute(NzConnection.java:2620)
  at org.netezza.sql.NzStatement._execute(NzStatement.java:848)
  at org.netezza.sql.NzStatement.execute(NzStatement.java:305)
  at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:205)
  at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:142)
  at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:28)
  at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:52)
  at ...

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.