OBIA 11g 'ORA-01722: Invalid Number' Error Received When Enabling The Partitioning Functionality
Last updated on JANUARY 31, 2017
Applies to:Business Intelligence Applications Consumer - Version 126.96.36.199.0 and later
Information in this document applies to any platform.
You are getting the following error when running the PLP_PLP_WORKFORCEEVENTFACT_MONTH Session for BI Apps 188.8.131.52.1 ETL:-
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 )
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
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