OBIA 11g 'ORA-01722: Invalid Number' Error Received When Enabling The Partitioning Functionality (Doc ID 1965658.1)

Last updated on JANUARY 31, 2017

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.7.0 and later
Information in this document applies to any platform.

Symptoms

 You are getting the following error when running the PLP_PLP_WORKFORCEEVENTFACT_MONTH Session for BI Apps 11.1.1.7.1 ETL:-

ODI-1226: Step PLP_WorkforceEventFact_Month.W_WRKFC_EVT_MONTH_F fails after 1 attempt(s).
ODI-1240: Flow PLP_WorkforceEventFact_Month.W_WRKFC_EVT_MONTH_F fails while performing a Integration operation. This flow loads target table W_WRKFC_EVT_MONTH_F.
ODI-1228: Task PLP_WorkforceEventFact_Month.W_WRKFC_EVT_MONTH_F (Integration) fails on the target ORACLE connection BIAPPS_DW.
Caused By: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number


Further investigation shows that the ODI failed on this step query:

INSERT INTO OBI_DW.I$_PART_5681180_3
(
EVENT_MONTH_WID, partition_high_value, partition_name, partition_exists, partition_split )
select distinct
S.EVENT_MONTH_WID partition_key, P.high_value_number, P.partition_name, 'Y' ,'N' from OBI_DW.I$_5681180_3 S, OBI_DW.W_ETL_PART_TABLE_PARTS P
WHERE P.table_name = 'W_WRKFC_EVT_MONTH_F' AND P.low_value_number < S.EVENT_MONTH_WID AND P.high_value_number >= S.EVENT_MONTH_WID


Reviewing some data we can see that in W_ETL_LOAD_DATES, has 'maxvalue' in P.HIGH_VALUE_NUMBER of W_ETL_PART_TABLE_PARTS instead of any max number

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