"ORA-00911" Error when Executing a Mapping Using ODI Variables with Table or Data Store Alias Prefix in ODI 12c

(Doc ID 2352463.1)

Last updated on MARCH 15, 2018

Applies to:

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


After upgrading Oracle Data Integrator (ODI) 11g to 12g, mappings that use certain prefixed variables are now taking the values of the column names in the database tables.

For example, in a mapping we have the following code where T1 is not prefixed with the project code, but rather the table (or data store) alias.

ROW_NUMBER() over(partition by EMP.ID order by EMP.#T1, #T2)

The execution of the mapping fails with the error:

ODI-1298: Serial task "SERIAL-MAP_MAIN- (10)" failed because child task "SERIAL-EU-CHILD_TASK (20)" is in error.
ODI-1298: Serial task "SERIAL-EU-CHILD_TASK (20)" failed because child task "Insert new rows-IKM SQL Control Append- (50)" is in error.
ODI-1228: Task Insert new rows-IKM SQL Control Append- fails on the target connection DBCONN.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:466)
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:407)
  at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1113)
  at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:546)
  at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
  at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:603)
  at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:218)
  at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:31)
  at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1000)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1316)
  at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2168)
  at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2100)




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 hundreds of Community platforms