Devart Forums

Posted: **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();

Posted: **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.

Posted: **Thu 30 Jun 2011 14:45**

This problem replies in 6.30.172

Posted: **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.

Posted: **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()

Posted: **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.

Posted: **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.

Posted: **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 .

Devart Forums

Problem with Array of Nullable types as parameter

Problem with Array of Nullable types as parameter