My Oracle Support Banner

ODI Mappings with Complex CASE Statements Using "IKM SQL to Oracle BI Cloud Services" Fail with "Caused By: org.apache.bsf.BSFException: exception from Groovy: org.codehaus.groovy.control.MultipleCompilationErrorsException" (Doc ID 2356857.1)

Last updated on FEBRUARY 13, 2018

Applies to:

Oracle Data Integrator Cloud Service - Version 17.1.3 and later
Oracle Data Integrator - Version 12.2.1.2.6 and later
Information in this document applies to any platform.

Symptoms

On Oracle Data Integrator (ODI) 12.2.1.2.6, a mapping that injects data into the Business Intelligence Cloud Services (BICS) via "IKM SQL to Oracle BI Cloud Services" knowledge module fails when the BICS KM creates a complex CASE statement with multiple lines. The BICS KM creates code with many extra spaces. For example, this piece of code is extracted from the Groovy script generated by BICS KM and shows the spaces added by the BICS KM as < SPACE >:

def expressionTargetMappingPositions = "[";
String[] stagingExpressions =
"TRANSLOG_1.CLASS,ARINVT_RECIPE_CARD.ELEMENTS_ID,ARINVT_GROUP.EPLANT_ID,TRANSLOG_1.ARINVT_ID,ARINVT_RECIPE_CARD.STD_COST,TRANSLOG_1.DIVISION_ID,TRANSLOG_1.TRANS_QUAN,ARINVT_RECIPE_CARD.PTSPER,
(case when TRANSLOG_1.TRANS_IN_OUT = 'OUT' then TRANSLOG_1.TRANS_QUAN else 0.00 end),TRANSLOG_1.TRANS_TYPE,TRANSLOG_1.TRANS_DATE,ARINVT_RECIPE_CARD.EXT_COST,ARINVT_RECIPE.CREATED,
case when (CASE WHEN ARINVT.STANDARD_ID is null or
TRANSLOG_1.STANDARD_ID is null then< SPACE >case when CASE WHEN (ELEMENTS.ID IS NULL)
THEN 999999999 ELSE ELEMENTS.ACCT_ID_RECV_DISPO_INV END is null or CASE
WHEN (ELEMENTS.ID IS NULL) THEN 999999999 ELSE
ELEMENTS.ACCT_ID_RECV_DISPO_INV END = 999999999 < SPACE > then IQSYS.ACCT_ID_FG
< SPACE > else CASE WHEN (ELEMENTS.ID IS NULL) THEN 999999999 ELSE
ELEMENTS.ACCT_ID_RECV_DISPO_INV END < SPACE > end ELSE < SPACE > CASE WHEN CASE WHEN
(ARINVT.ID IS NULL) THEN 999999999 ELSE ARINVT.ACCT_ID_INV END is null or
CASE WHEN (ARINVT.ID IS NULL) THEN 999999999 ELSE ARINVT.ACCT_ID_INV END =
999999999 < SPACE > then CASE when ARINVT_GROUP.ACCT_ID_FG is null or
ARINVT_GROUP.ACCT_ID_FG = 999999999 < SPACE > then IQSYS.ACCT_ID_FG < SPACE > else
ARINVT_GROUP.ACCT_ID_FG end < SPACE > else CASE WHEN (ARINVT.ID IS NULL) THEN
999999999 ELSE ARINVT.ACCT_ID_INV END END END) is null or
TRANSLOG_1.PARENT_ARINVT_ID=0 then ARINVT_MANUFACT_ITEMS_GLACCT_V.GL_ACCT_ID else

In the ODI execution logs, the following error is reported:

ODI-1298: Serial task "SERIAL-EU-IQMSPHOENIXLOGICAL_UNIT (20)" failed because child task "Load data into Oracle BICS-IKM SQL to
Oracle BI Cloud Service-Load ITEM_RECIPE_CARD_FCT (40)" is in error.
ODI-1590: The execution of the script failed.
Caused By: org.apache.bsf.BSFException: exception from Groovy: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
Load_ITEM_RECIPE_CARD_FCT: 195: expecting anything but ''\n''; got it anyway @ line 195, column 484.
RANSLOG_1.STANDARD_ID is null
^
1 error
  at org.codehaus.groovy.bsf.GroovyEngine.exec(GroovyEngine.java:114)
  at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:396)
  at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:247)
  at oracle.odi.runtime.agent.execution.interpreter.SessionTaskScriptingInterpretor.scripting(SessionTaskScriptingInterpretor.java:190)
  at oracle.odi.runtime.agent.execution.SessionTask.scripting(SessionTask.java:105)
  at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:49)
  at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:21)
  ...
Caused by: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
  ...

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.