Return
-
Title
Foglight database Oracle 11.2.0.4 to 12.1.0.2 upgrade errors ORA 02374,ORA 01722,ORA 02872 -
Description
Many errors while running impdp ( Data Pump Import) on OBS_METRIC_AGGERATE tables - ORA-02374: conversion error loading table "FOGLIGHT"."OBS_METRIC_AGGREGATE_0413" ORA-01722: invalid number ORA-02372: data for row: OMA_SUM_SQUARES : ''
Oracle support site knowledge articles:12c DataPump Import Fails With ORA-02374 ORA-01722 ORA-02372 Errors. (Doc ID 2046832.1)This behavior is correct in 12c and it is not a normal behavior in 11g to allow the invalid numbers.Similar bugs, like below, already exist but they were closed with status 'Not a Bug':==============================================================Bug 19942999 - ORA-2374, ORA-1722 AND ORA-2372 TRYING TO IMPORT BINARY_FLOAT DATATYPESBug 18826875 - DATAPUMP IMPORT FAILS WITH KUP-11007 ORA-01722 KUP-11009 ERRORSIf we select from the (11g) source table:select OMA_SUM_SQUAREs, DUMP(OMA_SUM_SQUARES,10) DUMP from "FOGLIGHT"."OBS_METRIC_AGGREGATE_0964"where OMA_SUM_SQUARES='0'OMA_SUM_SQUARES DUMP--------------------------------------------------------------------------------0 Typ=2 Len=1: 1280 Typ=2 Len=1: 1280 Typ=2 Len=1: 128We can see, the value '0' is not a valid number (neither FLOAT nor NUMBER or another Oracle number type).We suspect the data could have been inserted from third party tools which used binary types (IEEE 754 format). -
Cause
OMA_SUM_SQUARES is a number column and it actually contains such.
The DUMP function returns a varchar2 value that includes the datatype code.So in this case it is saying the data is type2 len=1 128, which is the correct data type for the number column.The '0' is a red herring here, because the DUMP function always returns a varchar2 data type.If one ran the same query without the quotes, the exact same result will occur. -
Resolution
WORKAROUND
Using the Oracle legacy import and export utilities (not the DataPump utilities).
STATUS
Oracle dump utility issue.