My Oracle Support Banner

"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 SEPTEMBER 04, 2020

Applies to:

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

Symptoms

NOTE: In the examples below any details such as table names represent a fictitious sample.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

 

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)

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.