Devart Forums

Posted: **Thu 12 Oct 2017 12:14**

Using version 9.4.348.0
The default datatype conversion from NUMBER is to Decimal.
My test plsql procedure gives the following output to prove this:

Column name NUMBER, Value 1, Oracle Type Decimal, Expected Type Int64
Column name NUMBER(12,2), Value 3,45, Oracle Type Decimal, Expected Type Decimal
Column name FLOAT, Value 4,5670, Oracle Type Decimal, Expected Type Decimal

When I add NumberMappings.Add(OracleNumberType.Integer, 0, typeof(Int64));
The datatype conversion for NUMBER is Correct! Hurray.

But now a NUMBER with precision and scale is converted to Int64. And this is not as expected!
My test plsql procedure gives the following output to prove this:

Column name NUMBER, Value 1, Oracle Type Int64, Expected Type Int64
Column name NUMBER(12,2), Value 3, Oracle Type Int64, Expected Type Decimal
Column name FLOAT, Value 4, Oracle Type Int64, Expected Type Decimal

When I add:
oraConnection.NumberMappings.Add(OracleNumberType.Number, 12, typeof(decimal));
the result stays the same and is not correct.

My Stored procedure is as follows:

Code: Select all

create or replace package ZZOISUTILITIES is

   TYPE mtrecordset IS REF CURSOR;
   
   PROCEDURE TestDataTypes
   (
      poRC OUT mtrecordset
   );
END ZZOISUTILITIES;

create or replace package body ZZOISUTILITIES is

   PROCEDURE TestDataTypes
   (
      poRC OUT mtrecordset
   )
   AS
      lnNumber         NUMBER := 1;
      lnNumber12_2     NUMBER(12,2) := 3.45;
      lnFloat          FLOAT := 4.567;
   BEGIN
      OPEN poRC FOR
         SELECT 
                lnNumber AS "NUMBER",
                lnNumber12_2 AS "NUMBER(12,2)",
                lnFloat AS "FLOAT"
         FROM   dual;
   END;         
END ZZOISUTILITIES;

And my c# code to test this is:

Code: Select all

         OracleConnection oraConnection = new OracleConnection("User ID=xxxx;Password=xxxx;Data Source=xxxx;Port=1521;SID=ETCP;Pooling=true;Direct=true;");
         oraConnection.NumberMappings.Add(OracleNumberType.Integer, 0, typeof(Int64));
         oraConnection.Open();

         string cmdText = "ZZOISUTILITIES.TESTDATATYPES";
         OracleCommand oraCommand = new OracleCommand(cmdText, oraConnection);
         oraCommand.CommandType = CommandType.StoredProcedure;
         oraCommand.Parameters.Add("PORC", OracleDbType.Cursor);
         oraCommand.Parameters["PORC"].Direction = ParameterDirection.Output;
         oraCommand.ExecuteNonQuery();

         bool lbHasErrors = false;
         var loValue1 = oraCommand.Parameters[0].Value;
         string lsValueType1 = loValue1.GetType().Name;

         OracleCursor oraCursor = (OracleCursor)loValue1;
         OracleDataAdapter oraDataAdapter = new OracleDataAdapter();
         DataSet loDataSet = new DataSet();
         oraDataAdapter.Fill(loDataSet, "Table", oraCursor);

         for (int i = 0; i < loDataSet.Tables[0].Columns.Count; i++)
         {
            string lsColumn = loDataSet.Tables[0].Columns[i].ColumnName;
            var loValue = loDataSet.Tables[0].Rows[0][lsColumn];

            string lsValueType = loValue.GetType().Name;

            string lsExpectedType = "Decimal";
            if (lsColumn.Equals("NUMBER")) 
            {
               lsExpectedType = "Int64";
            }
            Console.WriteLine(string.Format("Column name {0}, Value {1}, Oracle Type {2}, Expected Type {3}", lsColumn, loValue.ToString(), lsValueType, lsExpectedType));

         }

Posted: **Tue 17 Oct 2017 16:03**

We have reproduced the issue. We will investigate it and post the results here.

Posted: **Wed 08 Nov 2017 14:23**

We are in need of a solution. Please give us information concerning progress.
thank you.

Posted: **Fri 10 Nov 2017 13:39**

We have studied the issue and come to the conclusion that it is the correct behaviour. The reason is that in your example, Oracle returns information about columns without precision and scale. You can check it by adding the following code after: OracleCursor oraCursor = (OracleCursor)loValue1;

Code: Select all

var schemaTable = oraCursor.GetDataReader().GetSchemaTable();

You can see in schemaTable that Oracle returns 0 for the NumberPresicion column. Thus, for all values, precision is 0 and NumberMappings works for all returned data. This is the Oracle behaviour to not return precision and scale when you query data from dual.

If you select data from a table, then the issue should not be reproduced. You can try the following example:

Code: Select all

CREATE TABLE SEMENB.devart_test_table (
  LNNUMBER     NUMBER,
  LNNUMBER12_2 NUMBER(12, 2),
  LNFLOAT      FLOAT(126)
);

insert into devart_test_table VALUES (1, 3.45, 4.567);

CREATE OR REPLACE PROCEDURE devart_test(PORC OUT SYS_REFCURSOR)
IS
BEGIN
        OPEN PORC FOR
        SELECT * FROM devart_test_table WHERE ROWNUM < 100;
END devart_test;

and replace "ZZOISUTILITIES.TESTDATATYPES" with "devart_test" in your code.

You can create a temporary table and then insert data to it inside a procedure and read data from the table into a cursor.

Devart Forums

Oracle NumberMappings for datatype NUMBER not working in direct mode with cursor

Oracle NumberMappings for datatype NUMBER not working in direct mode with cursor

Re: Oracle NumberMappings for datatype NUMBER not working in direct mode with cursor

Re: Oracle NumberMappings for datatype NUMBER not working in direct mode with cursor

Re: Oracle NumberMappings for datatype NUMBER not working in direct mode with cursor