Problem with Array of Nullable types as parameter

Problem with Array of Nullable types as parameter

Postby grumpy » Wed 25 May 2011 16:20

Hi all,
I want to call a stored procedure with an array parameter which actually works with not nullable types like decimal[].
I need NULL-Values in the Array so I decided to use decimal?[] instead of decimal[]. Now I get an InvalidCastException: Ungültige Umwandlung von "System.Decimal" in "System.Nullable`1[[System.Decimal, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]".

Any hints how I can solve this problem?
Thanks in advance,
Martin
Code: Select all
            OracleConnection conn = new OracleConnection("User Id=user;password=oracle;Direct=True; server=myserver;sid=mysid");
            conn.Open();

            decimal?[] numbers = new decimal?[] {1,2, null};
           
            OracleCommand cmd = new OracleCommand("ptest.arrayparmtest", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("Result", OracleDbType.Number, ParameterDirection.ReturnValue);
            cmd.Parameters.Add("numbers", OracleDbType.Double, numbers, ParameterDirection.Input);
            cmd.ExecuteNonQuery();
            decimal res = Convert.ToDecimal( cmd.Parameters["Result"].Value);
            conn.Close();
grumpy
 
Posts: 1
Joined: Wed 25 May 2011 16:07

Postby Shalex » Thu 26 May 2011 13:17

The bug with inserting NULLs into Oracle array and table data types was fixed in dotConnect for Oracle starting from the 6.10.103 version (revision history).

If this information does not help, please tell us the version (x.xx.xxx) of your dotConnect for Oracle and DDL of your database objects.
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44

Postby amirela » Thu 30 Jun 2011 14:45

This problem replies in 6.30.172
amirela
 
Posts: 15
Joined: Tue 26 Oct 2010 10:51

Postby Shalex » Fri 01 Jul 2011 10:28

I cannot reproduce the problem with the following code:
Code: Select all
DDL:
  CREATE OR REPLACE TYPE "NUMBER_TBL" IS array(10) of number;
  CREATE OR REPLACE function arrayparmtest (numbers IN NUMBER_TBL)
  return Number
  AS
  BEGIN
  return numbers(1);
  END;

C#:
    OracleConnection conn = new OracleConnection("User Id=***;password=***;server=***;Direct=false;");
    conn.Open();
    decimal?[] numbers = new decimal?[] { 11, 12, null };
    OracleCommand cmd = new OracleCommand("arrayparmtest", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("Result", OracleDbType.Number, ParameterDirection.ReturnValue);
    cmd.Parameters.Add("numbers", OracleDbType.Array, numbers, ParameterDirection.Input).ObjectTypeName = "NUMBER_TBL";
    cmd.ExecuteNonQuery();
    decimal res = Convert.ToDecimal(cmd.Parameters["Result"].Value);
    conn.Close();

Tell us how we should modify this sample to reproduce the issue with dotConnect for Oracle v 6.30.172.

Be aware that there is the bug with OracleCommand.ExecuteArray() for nullable value types in the current version (refer to this thread). The fix will be included in the next build of dotConnect for Oracle.
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44

Postby amirela » Sat 02 Jul 2011 06:06

Code: Select all
DDL:

  CREATE OR REPLACE procedure test (p_Id     IN                    dbms_sql.Number_Table,
                             p_NumberValue      IN dbms_sql.Number_Table,
                             p_DateValue        IN dbms_sql.Date_Table,
                             p_StringValue      IN dbms_sql.Varchar2_Table)
  AS
  BEGIN
   NULL;
  END;

C#:
    OracleConnection conn = new OracleConnection("User Id=***;password=***;server=***;Direct=false;");
    conn.Open();
    decimal[] ids = new decimal[] { 1, 2, 3 };
    decimal?[] numberValues = new decimal?[] { 11, 12, 13 };
    DateTime?[] dateValues = new DateTime?[] { null, null, null };
    string[] stringValues = new string[] { null, null, null };

    OracleCommand cmd = new OracleCommand("test", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.PassParametersByName = true;


    cmd.Parameters.Add("p_Id", OracleDbType.Number);
    cmd.Parameters.Add("p_NumberValue", OracleDbType.Number);
    cmd.Parameters.Add("p_DateValue", OracleDbType.Date);
    cmd.Parameters.Add("p_StringValue", OracleDbType.VarChar);

    cmd.Parameters["p_NumberValue"].IsNullable = true;
    cmd.Parameters["p_DateValue"].IsNullable = true;
    cmd.Parameters["p_StringValue"].IsNullable = true;

    cmd.Parameters["p_Id"].ArrayLength = ids.Length;
    cmd.Parameters["p_NumberValue"].ArrayLength = numberValues.Length;
    cmd.Parameters["p_DateValue"].ArrayLength = dateValues.Length;
    cmd.Parameters["p_StringValue"].ArrayLength = stringValues.Length;
    cmd.Parameters["p_StringValue"].Size = 4000;

    cmd.Parameters["p_Id"].Value = ids;
    cmd.Parameters["p_NumberValue"].Value = numberValues;
    cmd.Parameters["p_DateValue"].Value = dateValues;
    cmd.Parameters["p_StringValue"].Value = stringValues;


    cmd.ExecuteNonQuery();
    conn.Close();



Invalid cast from 'System.Decimal' to 'System.Nullable`1[[System.Decimal, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]'. at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
at System.Decimal.System.IConvertible.ToType(Type type, IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at Devart.Data.Oracle.OracleParameter.a(t& A_0, Boolean A_1, Boolean A_2, OracleCommand A_3, Byte[] A_4, Hashtable A_5, ag A_6)
at Devart.Data.Oracle.OracleCommand.a(OracleParameterCollection A_0, t[] A_1, a2 A_2, ag A_3)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
amirela
 
Posts: 15
Joined: Tue 26 Oct 2010 10:51

Postby Shalex » Mon 04 Jul 2011 11:44

We have reproduced the error. We will investigate it and notify you about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Wed 06 Jul 2011 14:23

The problem is fixed. Look forward to the next build of dotConnect for Oracle. I will post here when it is available for download.
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Mon 11 Jul 2011 07:06

New build of dotConnect for Oracle 6.30.185 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .

For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=21452 .
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle