Oracle 11.2.0.4, NAN, INF in Number column using Oracle.ManagedDataAccess — oracle-tech

    Forum Stats

  • 3,715,999 Users
  • 2,242,927 Discussions
  • 7,845,731 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Oracle 11.2.0.4, NAN, INF in Number column using Oracle.ManagedDataAccess

Ruslan Gilmanov
Ruslan Gilmanov Member Posts: 3
edited December 2019 in ODP.NET

Hello.

I have some problem.

I am using Oracle 11.2.0.4, .Net (4.6.1) + Dapper (the freshest version: 2.0.30) + Oracle.ManagedDataAccess (the freshest version: 19.3.0).

In database i have created the table with column of number or integer type.

And after executing insert of:

- double.NaN

- double.NegativeInfinity

- double.PositiveInfinity

in number/integer types,

in column appears:

NAN

-INF (0 in integer type)

+INF

As far as i khnow (https://docs.oracle.com/database/121/TTSQL/types.htm#TTSQL124 )

only BINARY_DOUBLE and BINARY_FLOAT supports +Inf, -Inf, and NaN values but not oracle native datatypes (number)

If i try to select some values from my table using:

- select * from <table> where <column> is NAN

- select * from <table> where <column> is INFINITE

oracle returns ORA-01426 (Numeric overflow),

in addition to this OGG crashes during replication of such data: OGG-02570 Invalid numeric data detected. Error converting numeric from Oracle to ASCII on column

How can i fix it without changing .Net datatypes in source code (double -> decimal),

the solution of nulling such values will be enough,

maybe there are some settings in oracle Oracle.ManagedDataAccess config file of datatypes mapping,

but i cant find this:

https://docs.oracle.com/database/121/ODPNT/install.htm#ODPNT0006

thnaks, Ruslan

Ruslan Gilmanov

Answers

  • Mark Williams
    Mark Williams Member Posts: 67 Blue Ribbon
    edited December 2019

    Hello,

    EDIT: Apologies as I have re-read the posting and it seems you have +Inf, -Inf, and NaN stored in a column declared as NUMBER and not BINARY_DOUBLE (or BINARY_FLOAT). Storing -Inf and +Inf like this was common in very old database versions (something like version 5 or 6 as I recall) but like you say NUMBER should not be used for this purpose now. Can you describe how these special values were inserted into the NUMBER column?

    Could you share the output of "select abc, dump(nan_column_value) from table" for a row with a NaN as I show in my example below for a NaN value? It could be helpful. I've seen -Inf and +Inf in a NUMBER column before that could be displayed but not NaN as far as I recall just now.

    Basically the Oracle database internal representation of -Inf and +Inf can be expressed without error in a NUMBER column whereas the internal representation for NaN can not be expressed properly in a NUMBER column. I'm surprised you are able to see NaN from a NUMBER column (at least that is what I think you mean by "in column appears NAN").

    As far as how to fix it — you want to have a way to uniquely identify the rows in the table with incorrect values and then you can simply update that column to NULL for those rows. I don't think we have enough information yet to provide a guaranteed fix. You will also need to change the client code so that it does not insert these special values (in particular the NaN) into a NUMBER column. That will depend on how the client works.

    Leaving the following original response below as it might be worthwhile…

    It's not clear to me where the ORA-01426 is being raised — is that in the .NET code that uses ODP?

    Can you provide a simple, yet complete, testcase if the ORA-01426 is related to code that uses ODP? Something like the following which works as expected in my environment would be interesting.

    NOTE: This is using Oracle Database 19.3 as I don't have an 11.2 instance handy and ODP (Oracle.ManagedDataAccess) 19.6 version from NuGet. It should not matter though.

    In SQL*Plus create a simple test table:

    create table special_num_test (  data_type  varchar2(8),  data_value binary_double);

    Then a very simple test using ODP.NET (abridged but should be enough to convey the full idea):

    string sqlInsert = "insert into special_num_test values (:0, :1)";string sqlSelect = "select   * " +  "from special_num_test " +  "where data_value is infinite " +  "or       data_value is nan";var con = new OracleConnection(constr);con.Open();var cmd = con.CreateCommand();cmd.CommandText = sqlInsert;cmd.Parameters.Add(null, OracleDbType.Varchar2, "-Inf", ParameterDirection.Input);cmd.Parameters.Add(null, OracleDbType.Decimal, Double.NegativeInfinity, ParameterDirection.Input);cmd.ExecuteNonQuery();cmd.Parameters.Clear();cmd.Parameters.Add(null, OracleDbType.Varchar2, "+Inf", ParameterDirection.Input);cmd.Parameters.Add(null, OracleDbType.Decimal, Double.PositiveInfinity, ParameterDirection.Input);cmd.ExecuteNonQuery();cmd.Parameters.Clear();cmd.Parameters.Add(null, OracleDbType.Varchar2, "NaN", ParameterDirection.Input);cmd.Parameters.Add(null, OracleDbType.Decimal, Double.NaN, ParameterDirection.Input);cmd.ExecuteNonQuery();cmd.Parameters.Clear();cmd.CommandText = sqlSelect;var dr = cmd.ExecuteReader();while (dr.Read()){  Console.WriteLine("Data Type = {0}, Data Value = {1}", dr.GetString(0), dr.GetDouble(1).ToString());}

    This produces the following (correct) output in the console:

    Data Type = -Inf, Data Value = -∞Data Type = +Inf, Data Value = ∞Data Type = NaN, Data Value = NaN

    For verification of the values in the database table after executing the sample C# code (in SQL*Plus):

    col dump_value format a48select   data_type,         dump(data_value) as dump_valuefrom     special_num_testwhere    data_value is infiniteor       data_value is nan;DATA_TYP DUMP_VALUE-------- -------------------------------------------------Inf     Typ=101 Len=8: 0,15,255,255,255,255,255,255+Inf     Typ=101 Len=8: 255,240,0,0,0,0,0,0NaN      Typ=101 Len=8: 255,248,0,0,0,0,0,0

    I don't know about Oracle Golden Gate (OGG) and special values but that should not be related to ODP.NET provider in any case.

    Regards,

    Mark

    Alex Keh-Oracle
  • Ruslan Gilmanov
    Ruslan Gilmanov Member Posts: 3
    edited December 2019

    Yes, you are right, i have +Inf, -Inf, and NaN stored in a column declared as NUMBER/INTEGER, future example will be using NUMBER.

    the code is the same you have sent, except datatype of column.

    Sql*Plus:

    CREATE TABLE special_num_test(    data_type      VARCHAR2 (8),    data_value     NUMBER);

    After executing your code error will be raised in next line:

    var dr = cmd.ExecuteReader();

    Oracle.ManagedDataAccess.Client.OracleException: 'ORA-01426: numeric overflow

    But all inserts were successfull.

    if to try execute Sql*Plus

    col dump_value format a48  select   data_type,           dump(data_value) as dump_value  from     special_num_test  where    data_value is infinite  or       data_value is nan;  

    the same exception is raised:

    ORA-01426: numeric overflow

    If to delete predicate next statement will be successfull:

    col dump_value format a48select   data_type,          dump(data_value) as dump_value,          data_value from     special_num_test;DATA_TYP DUMP_VALUE                                       DATA_VALUE-------- ------------------------------------------------ -----------Inf     Typ=2 Len=1: 0                                           -~+Inf     Typ=2 Len=2: 255,101                                      ~NaN      Typ=2 Len=8: 255,248,0,0,0,0,0,0

    I think i can find these values in number type, for example using dump, and update these values to null,

    but i would like to prevent insertion of such values for all cases.

    I don't think INF values are legal in number types too, because next sql script causes to exception:

    declare    a binary_float := 1/0d;begin    dbms_output.put_line (a);    insert into special_num_test values ('inf',a);    insert into special_num_test values ('-inf',-a);end;/

    ORA-01426: numeric overflow

    NOTE: This is using Oracle Database 11.2.0.4 and 12.2.0.1.0 (tried too) i can't check right now in fresher DB version.

    Can you try tio insert Nan values in Number type for your Oracle version?

    I have seen such visual values (Nan, -Inf, +Inf using Ide for DB Toad).

    BR, Ruslan

  • Mark Williams
    Mark Williams Member Posts: 67 Blue Ribbon
    edited December 2019

    Hi,

    Yes, I am able to insert the -Inf, Inf, and NaN values into table when using a number column as follows:

    create table special_num_test (  data_type  varchar2(8),  data_value number);

    Here's what I see in the table after doing this in my environment:

    select   data_type,         data_value,         dump(data_value) as dump_valuefrom     special_num_test;DATA_TYPE DATA_VALUE DUMP_VALUE--------- ---------- -------------------------------------------------Inf              -~ Typ=2 Len=1: 0+Inf               ~ Typ=2 Len=2: 255,101NaN                  Typ=2 Len=8: 255,248,0,0,0,0,0,03 rows selected.

    Notice the DATA_VALUE column for the NaN row does not display — it's not really null of course since we can see the value in the DUMP_VALUE output column. This is what I mean when I say I've not seen a NaN display when inserted into a number column. It looks like you have a different tool (Toad) that does display it. In any case, that is not important.

    So, what to do if this has happened? Since you can't properly filter on the column when it is declared as number here's a suggestion using cast to binary_double:

    select   data_type,         data_value,         dump(data_value) as dump_valuefrom     special_num_testwhere    cast(data_value as binary_double) is infiniteor       cast(data_value as binary_double) is nan;DATA_TYPE DATA_VALUE DUMP_VALUE--------- ---------- -------------------------------------------------Inf              -~ Typ=2 Len=1: 0+Inf               ~ Typ=2 Len=2: 255,101NaN                  Typ=2 Len=8: 255,248,0,0,0,0,0,0

    By casting the number column to binary_double you should be able to update all the special values in your table to null. Again, using my simple example when DATA_VALUE is a NUMBER, this would look like:

    update   special_num_testset      data_value = nullwhere    cast(data_value as binary_double) is infiniteor       cast(data_value as binary_double) is nan;3 rows updated.

    Perhaps you can use the cast as above in your environment to update the existing -Inf, Inf, and NaN values to NULL.

    Preventing insertion of the special values is a little more tricky. Even though I do not like using triggers it may be that you could use a trigger such as the following to convert any of the special values to null during insert or update:

    create or replace trigger no_special_valuesbefore insert or update of data_valueon special_num_testfor each rowbegin  if (cast(:new.data_value as binary_double) is infinite or      cast(:new.data_value as binary_double) is nan) then    :new.data_value := null;  end if; end;/

    I would strongly prefer to fix the application or process that is inserting the special values rather than use a trigger like this but it may be a valid option in this case. Rather than converting the values to null perhaps the trigger could raise an exception instead — though that would cause the process to fail of course. But then you could determine what is inserting the special values.

    Regards,

    Mark

    Alex Keh-Oracle
  • Ruslan Gilmanov
    Ruslan Gilmanov Member Posts: 3
    edited December 2019

    Tnanks for fast answer, so, do you think these values, stored in Number column are correct and legal and it is not a Bug? because your solution is the hook, and requires some continuous work, there are a lot of tables with number columns, which can store potentinally nan values. The first place, we have seen some Number rows have Nan is because of replication such data, OGG crashes during replication of these rows. If nan values are correct in number types (not a Bug), and can be stored in DB legally, (but documentation, for example https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Data-Types.html#GUID-CFE7487C-A4D0-4E90-A836-2697C45BDD10 does not say about this possibility about number type) something wrong with Oracle Golden Gate.

    we have found the possibility to replicate such values in OGG, but converting them to another value (ZERO),  maybe somebody can help this info, these settings are not default.

    TRANLOGOPTIONS _CONVERTBADNUMBER ZEROTRANLOGOPTIONS _INFINITYTOZERO CAUSE0x00 is -infinity and will show up in sqlplus as -~.0xFF65 is infinity and will show up in sqlplus as ~.infinity and -infinity are unsupported since Oracle 5 as described inSOLUTIONUse TRANLOGOPTIONS _INFINITYTOZERO  to get past this problem.Using _INFINITYTOZERO converts the infinity value to a zero.

    But we have not found how to replicate these values as is, without converting.

    https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=357405751513603&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=1316627.1&_afrWindowMode=0&_adf.ctrl-state=9khlc7che_53

    As you see,

    there is info: infinity and -infinity are unsupported since Oracle 5,

    some collission between possibility of inserting such values, and its unsupport.

    There is also instead of ZERO next config:

    TRANLOGOPTIONS _CONVERTBADNUMBER USEV8CONV,

    this way NaN will be converted to 2.4252525252525E125,

    dump - Typ=2 Len=8: 255,25,26,26,26,26,26,26

    BR, Ruslan

  • Mark Williams
    Mark Williams Member Posts: 67 Blue Ribbon
    edited December 2019

    Well, I don't think Oracle would consider this a bug. For example, see the following My Oracle Support note:

    ORA-01722 - Invalid Number, forcing data representing NaN into a NUMBER column results are unpredictable (Doc ID 2220351.1)

    While that discusses Java, it's the same for any client really — Oracle basically leaves it up to the application to verify valid data when inserting numeric data like this.

    However, storing special number representations like this in a NUMBER column is definitely not a good thing to do. You are experiencing some of the issues that can arise from this sort of thing.

    I still think the real answer is to determine what client or process is inserting the special values into a NUMBER column and fix that. We've already seen a few places in the documentation where it is mentioned the special values are supported in BINARY_[FLOAT | DOUBLE] columns. So that implies that they are not supported in NUMBER.

    I guess I might consider the ability to insert these values into a NUMBER column (even though you should not do so) similar to how Oracle treats an empty string (i.e., "") as NULL. It's frustrating sometimes but it is the way Oracle behaves.

    Regards,

    Mark

    Ruslan GilmanovRuslan Gilmanov
Sign In or Register to comment.