ORA-00904 Invalid Identifier When Deploying an OWB 11.2 Mapping with Key Lookup Operator

(Doc ID 1283584.1)

Last updated on MAY 09, 2017

Applies to:

Oracle Warehouse Builder - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.

Symptoms

 After migration from OWB 10.2 to 11.2, the generated code for some mappings is wrong. As an example, a mapping that has a filter operator feeding a number of lookup operators and then an aggregator. The below listed errors occur while deploying a mapping of this type:

ORA-06550: PACKAGE BODY, line 11, column 2:
PL/SQL: ORA-00904: "LOOKUP_INPUT_SUBQUERY"."F_FIN_EFECTIVOS": invalid
identifier

ORA-06550: PACKAGE BODY, line 11, column 2:
PL/SQL: SQL Statement ignored

ORA-06550: PACKAGE BODY, line 1256, column 2:
PL/SQL: ORA-00904: "LOOKUP_INPUT_SUBQUERY$1"."F_FIN_EFECTIVOS$1": invalid
identifier

ORA-06550: PACKAGE BODY, line 1256, column 2:
PL/SQL: SQL Statement ignored

ORA-06550: PACKAGE BODY, line 2835, column 1:
PL/SQL: ORA-00904: "LOOKUP_INPUT_SUBQUERY$2"."F_FIN_EFECTIVOS$2": invalid
identifier

ORA-06550: PACKAGE BODY, line 2835, column 1:
PL/SQL: SQL Statement ignored


When checking the mapping generated SQL, you will find that filter condition is duplicated, like:

WHERE
( "LOOKUP_INPUT_SUBQUERY$1"."F_FIN_EFECTIVOS$1" < sysdate/* OPERATOR
FILTRO_MES_ACT: FILTER CONDITION */ ) ) "LOOKUP_SUBQ$1"
WHERE
( "LOOKUP_INPUT_SUBQUERY$1"."F_FIN_EFECTIVOS$1" < sysdate/* OPERATOR
FILTRO_MES_ACT: FILTER CONDITION */ ) ) "AGG_INPUT$6"

Changes

Migration from OWB 10.2 to OWB 11.2.

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