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 DATATYPES
Bug 18826875 - DATAPUMP IMPORT FAILS WITH KUP-11007 ORA-01722 KUP-11009 ERRORS
If 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: 128
0 Typ=2 Len=1: 128
0 Typ=2 Len=1: 128
We 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).
