My Oracle Support Banner

ODI 12c Non-ASCII Characters of Column Names are Transformed to Underscores and Execution Fails with "ORA-00911 : invalid character" Error (Doc ID 2480929.1)

Last updated on JANUARY 23, 2024

Applies to:

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

Symptoms

NOTE: In the example and images below, and details including schema and table names or other user details represent a fictitious sample (based upon made up data). Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

 

In Oracle Data Integrator (ODI) 12c Mappings., the non-ASCII characters of column names are transformed to underscores at the Target Access Point.

This issue happens when Japanese characters are contained in the column name.

 

When executing the Mapping, it generates the following SQL code at the task of "Create work table - LKM SQL to Oracle".

This results in an "ORA-00911 : invalid character" error because the column name is not allowed to start with the underscore:

create table TRGUSER1.C$_0JPTABLE
(
  COL1 NUMBER NULL,
  ___ NUMBER NULL
)
NOLOGGING

Full error stack as follows:

ODI-1228: Task Create work table-LKM SQL to Oracle- fails on the target connection <CONNECTION_NAME>.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:495)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:447)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1055)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:624)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:253)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:613)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:214)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:38)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:891)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1194)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1835)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1790)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:301)
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 oracle.odi.runtime.agent.execution.SessionTask.processTask(SessionTask.java:206)
at oracle.odi.runtime.agent.execution.SessionTask.doExecuteTask(SessionTask.java:117)
at oracle.odi.runtime.agent.execution.AbstractSessionTask.execute(AbstractSessionTask.java:886)
at oracle.odi.runtime.agent.execution.SessionExecutor$SerialTrain.runTasks(SessionExecutor.java:2225)
at oracle.odi.runtime.agent.execution.SessionExecutor.executeSession(SessionExecutor.java:610)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:718)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:611)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:203)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor.doProcessStartAgentTask(TaskExecutorAgentRequestProcessor.java:800)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$1400(StartSessRequestProcessor.java:74)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:702)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:180)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:108)
at java.lang.Thread.run(Thread.java:748)
Caused by: Error : 911, Position : 56, Sql = create table <TABLE_NAME>
(
COL1 NUMBER NULL,
___ NUMBER NULL
)
NOLOGGING , OriginalSql = create table <TABLE_NAME>
(
COL1 NUMBER NULL,
___ NUMBER NULL
)
NOLOGGING , Error Msg = ORA-00911: invalid character

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:499)
... 30 more

Note: the above still does not work correctly even when the following options are set in ODI Studio:

  1. Select "Tools" > "Preferences".
  2. Select "ODI" > "System" in the Preferences window.
  3. Enable "Never transform non ASCII characters to underscores".

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!

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