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

Last updated on JUNE 16, 2022

Applies to:

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 <TABLE_NAME>_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 <TABLE_NAME>_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 <NAME>.
Caused By: org.netezza.error.NzSQLException: ERROR: 'create external table <TABLE_NAME>_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

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