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));
}