"ORA-00942: table or view does not exist" Signalled At Runtime When a Reusable Mapping Is Referred To In An ODI 12c Mapping

(Doc ID 1644315.1)

Last updated on JUNE 28, 2016

Applies to:

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

Symptoms

The following message is received in Oracle Data Integrator (ODI) 12.1.2 when executing a Mapping that refers to a Reusable Mapping:

ORA-00942: table or view does not exist

The mapping is as follows:

Analysing the code generated SQL by the IKM, you can determine that for one of the source Datastore components (TL_PULF_FOKUSOMRADE), the table name is used instead of the table alias.

More precisely, on the IKM Insert flow into I$ table step, ODI uses the table name (TL_PULF_FOKUSOMRADE) instead of the table alias (TL_PULF_FOKUSOMRADE_1):

...
  FROM (SELECT (TD_PRIORITET_LKP_TD_PRIORITE_1.PRIORITET_SN) PRIORITET_SN,
  TD_FOKUSOMRADE.FOKUSOMRADE_KOD FOKUSOMRADE_KOD,
  TD_FOKUSOMRADE.FOKUSOMRADE_NAMN FOKUSOMRADE_NAMN,
  ...
  'I' IND_UPDATE
  FROM BLIS_TL.TL_PULF_FOKUSOMRADE TL_PULF_FOKUSOMRADE_1
  LEFT OUTER JOIN
  BLIS_TL.TD_PRIORITET TD_PRIORITET_LKP_TD_PRIORITE_1
  ON TL_PULF_FOKUSOMRADE.PROGRAM_KOD = (TD_PRIORITET_LKP_TD_PRIORITE_1.PROGRAM_KOD)
  AND TL_PULF_FOKUSOMRADE.PRIORITET_KOD = (TD_PRIORITET_LKP_TD_PRIORITE_1.PRIORITET_KOD)
  WHERE (1 = 1)) S
...

the correct, expected code being:

...
  FROM (SELECT (TD_PRIORITET_LKP_TD_PRIORITE_1.PRIORITET_SN) PRIORITET_SN,
  TD_FOKUSOMRADE.FOKUSOMRADE_KOD FOKUSOMRADE_KOD,
  TD_FOKUSOMRADE.FOKUSOMRADE_NAMN FOKUSOMRADE_NAMN,
  ...
  'I' IND_UPDATE
  FROM BLIS_TL.TL_PULF_FOKUSOMRADE TL_PULF_FOKUSOMRADE_1
  LEFT OUTER JOIN
  BLIS_TL.TD_PRIORITET TD_PRIORITET_LKP_TD_PRIORITE_1
  ON TL_PULF_FOKUSOMRADE_1.PROGRAM_KOD = (TD_PRIORITET_LKP_TD_PRIORITE_1.PROGRAM_KOD)
  AND TL_PULF_FOKUSOMRADE_1.PRIORITET_KOD = (TD_PRIORITET_LKP_TD_PRIORITE_1.PRIORITET_KOD)
  WHERE (1 = 1)) S
...

Cause

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