0 Replies Latest reply on Oct 2, 2019 3:49 PM by user3260131

    12c DataPump Import Fails With ORA-02374 ORA-01722 ORA-02372 Errors (Doc ID 2046832.1)

    user3260131

      Not sure how to add a comment that may enhance the solution of the above issue.

       

      I encountered this same issue while importing a table from a database that has existed for some time. I was able to find the row that had the issue such as:

      SQL> select puid, pseq, pval from PXFORM_DATA where puid='VAUdfE6ABx5aAA';

       

      PUID                  PSEQ       PVAL

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

      VAUdfE6ABx5aAA           0          1

      VAUdfE6ABx5aAA           1 2.0000E-16

      VAUdfE6ABx5aAA           2          0

      VAUdfE6ABx5aAA           3          0

      VAUdfE6ABx5aAA           4 -1.000E-16

      VAUdfE6ABx5aAA           5          1

      VAUdfE6ABx5aAA           6                      <- Notice the "blank" spot for PVAL

      VAUdfE6ABx5aAA           7          0

      VAUdfE6ABx5aAA           8 1.0000E-16

      VAUdfE6ABx5aAA           9                      <- Notice the "blank" spot for PVAL

      VAUdfE6ABx5aAA          10          1

      VAUdfE6ABx5aAA          11          0

      VAUdfE6ABx5aAA          12          0

      VAUdfE6ABx5aAA          13          0

      VAUdfE6ABx5aAA          14          0

      VAUdfE6ABx5aAA          15          1

       

      16 rows selected.

       

      The column PVAL is of type "FLOAT" and that the problem rows somehow had a blank value.  I then found that if I compared PVAL with PVAL*1, that it would fail and so I could then issue:

      SQL> select puid, pseq, pval, pval * 1 npval from PXFORM_DATA where puid='VAUdfE6ABx5aAA' and NOT(pval = (pval * 1));

       

      PUID                  PSEQ       PVAL      NPVAL

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

      VAUdfE6ABx5aAA           6            -1.000E-34

      VAUdfE6ABx5aAA           9            1.0000E-34

       

      So it looks like Oracle is having a problem when the exponent is some large value like "E-34" or some kind of Infinity/Negative Infinity.

       

      So to correct the problem, I came up with this statement:

      update PXFORM_DATA SET PVAL = (PVAL * 1) WHERE PVAL != (PVAL * 1);

       

      So after the fix, the table looks like:

      PUID                  PSEQ       PVAL

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

      VAUdfE6ABx5aAA           0          1

      VAUdfE6ABx5aAA           1 2.0000E-16

      VAUdfE6ABx5aAA           2          0

      VAUdfE6ABx5aAA           3          0

      VAUdfE6ABx5aAA           4 -1.000E-16

      VAUdfE6ABx5aAA           5          1

      VAUdfE6ABx5aAA           6 -1.000E-34      <- Notice the corrected value for PVAL

      VAUdfE6ABx5aAA           7          0

      VAUdfE6ABx5aAA           8 1.0000E-16

      VAUdfE6ABx5aAA           9 1.0000E-34      <- Notice the corrected value for PVAL

      VAUdfE6ABx5aAA          10          1

      VAUdfE6ABx5aAA          11          0

      VAUdfE6ABx5aAA          12          0

      VAUdfE6ABx5aAA          13          0

      VAUdfE6ABx5aAA          14          0

      VAUdfE6ABx5aAA          15          1

       

      16 rows selected.

       

      So if you encounter the ORA-02374 ORA-01722 ORA-02372 Errors, then for each of the table/column errors, do:

      UPDATE <table> SET <column> = <column> * 1 WHERE <column> != <column> * 1;