Column Name Getting Replaced by Project Variable Name in ODI 11g (Doc ID 2229541.1)

Last updated on JUNE 11, 2021

Applies to:

Symptoms

The following issues with KM code is observed in these circumstances:

Declarations

• A Project variable - <PROJECT>.BUSINESS_GROUP_ID
• A column name in Content Item VO - BUSINESS_GROUP_ID

Desired outcome:
    Insert data into a D$ table having the columns marked with UD1 or UD4 and having the bind variables whose values are selected in the source.

The source (Command on Source) code is:

select
<%=snpRef.getColList("", "sel011.[COL_NAME]", ",\n\t", "", "INS and REW and (UD1 or UD4)")%>
from ( <%=odiRef.getTargetTable("TABLE_DESC").replaceAll("!!q","'")%> ) sel011
where exists (
select 0 from DATA_BATCH_KEY_MAP KEY_MAP
where KEY_MAP.TARGET_ID=<%=snpRef.getColList("", "sel011.[COL_NAME]", " ", "", "UD1")%>
and KEY_MAP.OBJECT_NAME='<%=snpRef.getTargetTable("TABLE_NAME")%>'
AND ENTERPRISE_ID=#<PROJECT>.BUSINESS_GROUP_ID
)

The target (Command On Target) code is:

insert into <%=nlu.dataName%>
( <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "INS and
REW and (UD1 or UD4)")%> , DATA_BATCH_ID)
values
(
<%=snpRef.getColList("", ":[COL_NAME]", ",\n\t", "", "INS and
REW and (UD1 or UD4)")%>
,#<PROJECT>.BATCH_ID
)

The generated target code is:

insert into D$_CONTENT_ITEM_VO
(CONTENT_ITEM_ID,
BUSINESS_GROUP_ID,
CONTENT_TYPE_ID,
CONTENT_ITEM_CODE,
NAME,
..., DATA_BATCH_ID)
values
(
:CONTENT_ITEM_ID,
:<PROJECT>.BUSINESS_GROUP_ID,
:CONTENT_TYPE_ID,
:CONTENT_ITEM_CODE,
:NAME,
... ,#<PROJECT>.BATCH_ID
)

The issue is the colon (:) in front of ":[COL_NAME]", is causing the replacement of the BUSINESS_GROUP_ID column name with the project variable name <PROJECT>.BUSINESS_GROUP_ID.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.