Domain Load Issue on w_domain_general_gs ORA-00911 : invalid character

Dhandabani S

Hi,

Version:     BI Apps 11.1.1.8.1

The domain load plan step is failing with below error on loading target table.

--------------------------------------------------------------------------------------------------------------------------------------

ODI-1226: Step SDE_ORA_DomainGeneral_FlexfieldValueSet.W_DOMAIN_MEMBER_GS fails after 1 attempt(s).

ODI-1240: Flow SDE_ORA_DomainGeneral_FlexfieldValueSet.W_DOMAIN_MEMBER_GS fails while performing a Loading operation. This flow loads target table W_DOMAIN_MEMBER_GS.

ODI-1227: Task SrcSet0 (Loading) fails on the source ORACLE connection GIPF_EBS_SOURCE.

Caused By: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

-----------------------------------------------------------------------------------------------------------------------------------

The probable cause could the usage of semicolon(&APOS') in the generated source code:

ODI Variable:     #BIAPPS.FLEX_VALUE_SET_SQL

SELECT DISTINCT TO_CHAR(LOOKUP_CODE) ID, TO_CHAR(LOOKUP_CODE) MEANING, NULL DESCRIPTION,'US' LANGUAGE,'US' SOURCE_LANG,TO_DATE(NULL) LAST_UPDATE_DATE,TO_NUMBER(NULL) LAST_UPDATED_BY,TO_NUMBER(NULL) CREATED_BY,TO_DATE(NULL) CREATION_DATE FROM APPS.HR_LOOKUPS HRL, FND_SESSIONS S WHERE S.SESSION_ID = USERENV('SESSIONID') AND  HRL.LOOKUP_TYPE = 'GHR_US_GRADE_OR_LEVEL' AND HRL.ENABLED_FLAG = 'Y' AND S.EFFECTIVE_DATE BETWEEN NVL(START_DATE_ACTIVE,S.EFFECTIVE_DATE) AND NVL(END_DATE_ACTIVE,S.EFFECTIVE_DATE)

' (apostrophe) and null values are not generated properly.

'Kindly help to resolve the issue.

Thanks in Advance

DB

Dhandabani S

Anyone Kindly help us to fix the above issue. We are struck and unable to move from here.

Thanks in Advance

Dhandabani S

Above mentioned Issue has been resolved.

Bug 19292474 - MANUAL_FIX_STEPS:BI Apps Customization methodology