I have a table with several number columns that are inserted through a Asp.Net application using bind variables. Due to upgrade of Oracle client to 19c and server change, the code instead of giving an error on insert of invalid data, inserts trash and the application crashes aftewards. Any help is appreciated in finding the root cause.

SELECT trial1,
       DUMP (trial1, 17),
       DUMP (trial1, 1016),
       trial3,
       DUMP (trial3,17),
       DUMP (trial3, 1016)

Result in SQL Navigator results of query

Oracle 12c Oracle client 19

My DBA found this on Oracle Support and that lead to us find the error in the application side:

NaN is a specific IEEE754 value. However Oracle NUMBER is not IEEE754 compliant. Therefore if you force the data representing NaN into a NUMBER column results are unpredicatable. SOLUTION If you can put a value in a C float, double, int etc you can load this into the database as no checks are undertaken - just as with the Oracle NUMBER datatype it's up to the application to ensure the data is valid. If you use the proper IEEE754 compliant type, eg BINARY_FLOAT, then NaN is recognised and handled correctly.

CodePudding user response：

You have bad data as you have tried to store an double precision NAN value in a NUMBER column rather than a BINARY_DOUBLE column.

---

We can duplicate the bad data with the function (never use this in a production environment):

CREATE FUNCTION createNumber(
  hex VARCHAR2
) RETURN NUMBER DETERMINISTIC
IS
  n NUMBER;
BEGIN
  DBMS_STATS.CONVERT_RAW_VALUE( HEXTORAW( hex ), n );
  RETURN n;
END;
/

Then, we can duplicate your bad values using the hexadecimal values from your DUMP output:

CREATE TABLE table_name (trial1 NUMBER, trial3 NUMBER);
INSERT INTO table_name (trial1, trial3) VALUES (
  createNumber('FF65'),
  createNumber('FFF8000000000000')
);

Then:

SELECT trial1,
       DUMP(trial1, 16) AS t1_hexdump,
       trial3,
       DUMP(trial3, 16) AS t3_hexdump
FROM   table_name;

Replicates your output:

TRIAL1	T1_HEXDUMP	TRIAL3	T3_HEXDUMP
~	Typ=2 Len=2: ff,65	null	Typ=2 Len=8: ff,f8,0,0,0,0,0,0

---

Any help is appreciated in finding the root cause.

You need to go back through your application and work out where the bad data came from and see if you can determine what the original data was and debug the steps it went through in the application to work out if it was:

1. Always bad data, and then you need to put in some validation into your application to make sure the bad data does not get propagated; or
2. Was good data but there is a bug in your code that changed it and then you need to fix the bug.

As for the existing bad data, you either need to correct it (if you know what it should be) or delete it.

We cannot help with any of that as we do not have visibility of your application nor do we know what the correct data should have been.

---

If you want to store that data as a floating point then you need to change from using a NUMBER to using a BINARY_DOUBLE data type:

CREATE TABLE table_name (value BINARY_DOUBLE);
INSERT INTO table_name(value) VALUES (BINARY_DOUBLE_INFINITY);
INSERT INTO table_name(value) VALUES (BINARY_DOUBLE_NAN);

Then:

SELECT value,
       DUMP(value, 16)
FROM   table_name;

Outputs:

VALUE	DUMP(VALUE,16)
Inf	Typ=101 Len=8: ff,f0,0,0,0,0,0,0
Nan	Typ=101 Len=8: ff,f8,0,0,0,0,0,0

Then BINARY_DOUBLE_NAN exactly matches the binary value in your column and you have tried to insert a Not-A-Number value into a NUMBER column (that does not support it) in the format expected for a BINARY_DOUBLE column (that would support it).

CodePudding user response：

The issue was a division by zero on the application side that was inserted as infinity into the database, but Oracle has an unpredictable behavior with this values. Please see original post above for all the details.