My Oracle Support Banner

ODI 12c Strings Containing Escaped Characters Result in "ORA-01425: escape character must be character string of length 1" (Doc ID 2435180.1)

Last updated on NOVEMBER 05, 2019

Applies to:

Oracle Data Integrator - Version 12.2.1.3.0 and later
Information in this document applies to any platform.

Symptoms

When executing a mapping in Oracle Data Integrator (ODI) 12c, the code is missing the backslash ('\'), tab ('\t') or other escaped characters. 

Please note, in the following examples there are placeholders which should be replaced by your actual values. These placeholders are in and include <>

Two examples of code in the User Function. 

TRIM($(<source_fld_nm>)) LIKE '%.%.%' OR TRIM($(<source_XXX>)) LIKE '%\%%' ESCAPE '\'

TRIM(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(<TABLE>.<COLUMN>), '([[:cntrl:]])|(^\t)|( )',' '),' ',' '))=1

However, the code generated during execution ends up as:

TRIM($(<source_fld_nm>)) LIKE '%.%.%' OR TRIM($(<source_XXX>)) LIKE '%\%%' ESCAPE ''

TRIM(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(<TABLE>.<COLUMN>), '([[:cntrl:]])|(^ )|( )',' '),' ',' '))=1

 

When it is executed, there may be errors related to SQL parsing, such as:

ODI-1226: Step <STEP> fails after 1 attempt(s).
ODI-1240: Flow <STEP> fails while performing a Insert new rows-IKM SQL Control Append- operation. This flow loads target table <TABLE>.
ODI-1298: Serial task "SERIAL-MAP_MAIN-<TASK>" failed because child task "SERIAL-<TASK2>" is in error.
ODI-1298: Serial task "SERIAL-<TASK2>" failed because child task "Insert new rows-IKM SQL Control Append-<TASK3>" is in error.
ODI-1228: Task Insert new rows-IKM SQL Control Append-<TASK> fails on the target connection <CONNECTION>.
Caused By: java.sql.SQLDataException: ORA-01425: escape character must be character string of length 1
  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)
  ...
Caused by: Error : 1425, Position : 618, Sql =
[...SQL code that caused error...]
, Error Msg = ORA-01425: escape character must be character string of length 1
  at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:499)
  ... 30 more

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.