Devart Forums

Posted: **Sat 14 Nov 2009 19:01**

Hello, seems like I've found a bug in bulk inserts (command.ExecuteArray)

var vgensType = OracleType.GetObjectType("TEST_SCHEMA", "VGENS", con);
var genType = OracleType.GetObjectType("TEST_SCHEMA", "GEN", con);

GEN is an object type with two attributes: Name (varchar2) and CNum (number). VGENS is VARRAY(1000) of GEN. The table I'm trying to insert some random data looks like this:

Code: Select all

create table ZGLOBAL
(
  GUID  RAW(16) default sys_guid() not null,
  XDATA  TEST_SCHEMA.VGENS
)

Here is a snippet from my code:

Code: Select all

const int batchSize = 5;
var value = new OracleArray(vgensType);
        
using (var cmd = new OracleCommand("insert into zglobal (xdata) values (:xdata)", con))
{
  var random = new Random();
  var values = new object[batchSize];
  for (int i = 0; i < values.Length; i++)
  {
    var value = new OracleArray(vgensType);
    values[i] = value;

    for (int j = 0; j < random.Next(10); j++)
    {
      var item = new OracleObject(genType);
      item["NAME"] = "element" + j;
      item["CNUM"] = random.NextDouble() * 1000000;
      value.Add(item);
    }
  }

  var param = cmd.Parameters.Add("xdata", OracleDbType.Array);
  param.OracleDbType = OracleDbType.Array;

  foreach(var array in values) {
    param.Value = array;
    cmd.ExecuteNonQuery();
  }
}

The code above works, but doesn't use bulk inserts. I modify the later part like this to engage fast bulk inserts:

Code: Select all

var param = cmd.Parameters.Add("xdata", OracleDbType.Array);
param.OracleDbType = OracleDbType.Array;
param.Value = values;
cmd.ExecuteArray(batchSize);

Seems good but doesn't work with exception:

System.ArgumentException was unhandled
Message="Argument 'Iters' must be equal to ArrayLength."
Source="Devart.Data.Oracle"
StackTrace:
at Devart.Data.Oracle.OracleParameter.a(Object A_0, ar& A_1, OracleDbType A_2, Int32 A_3, ParameterDirection A_4, Int32 A_5, Int32 A_6, aw A_7)
at Devart.Data.Oracle.OracleCommand.a(ab A_0, Int32 A_1, OracleParameterCollection A_2, aw A_3, Boolean& A_4)
at Devart.Data.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
at Devart.Data.Oracle.OracleCommand.ExecuteArray(Int32 iters)
InnerException:

What am I doing wrong or is this feature not implemented properly?

Posted: **Mon 16 Nov 2009 10:50**

We will investigate the issue and notify you about the results as soon as possible.

Posted: **Wed 18 Nov 2009 15:23**

We have implemented array binding support for OracleObjects. I will notify you when the build with this funcionality is available for download.

Posted: **Fri 20 Nov 2009 16:15**

dotConnect for Oracle v 5.35 is released.
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).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=16436.

Posted: **Fri 20 Nov 2009 18:44**

Does it include promised feature?

Posted: **Mon 23 Nov 2009 09:03**

Hi,

I've installed the latest build, but I'm still getting this error when trying to insert via ExecuteArray:

Code: Select all

Argument 'Iters' must be equal to ArrayLength.

Is this caused by a bug in dotConnect, and is there a known way to resolve it?

Thanks,

lasseschou

Posted: **Mon 23 Nov 2009 14:40**

Lasseschou, please confirm that you are getting the mentioned error with the 5.35.54 version of Devart.Data.Oracle.dll loaded to the process of your application. You can learn it by setting a breakpoint in your code and looking into the Debug > Windows > Modules dialog.

Posted: **Tue 14 Feb 2012 21:03**

Hello,
I am trying to do a bulk insert in to an oracle database using Devart's dotconnect for oracle component, version 6.60.283.0.

Upon running the ExecuteArray method I get an exception with the following message

Unable to cast object of type 'Devart.Data.Oracle.ce[]' to type 'Devart.Data.Oracle.ce'."

What does this error mean? and what should I do in order to successfully do a bulk insert?
Thanks

Posted: **Tue 14 Feb 2012 22:15**

Upgraded to the latest build 6.70.302 and getting the following message now

Unable to cast object of type 'Devart.Data.Oracle.ch[]' to type 'Devart.Data.Oracle.ch'

Posted: **Thu 16 Feb 2012 13:55**

We could not reproduce the issue in our environment. We don't have enough information for that. Could you post here the snippet of code, where you create connection, command and parameters and execute command? The test project with DDL\DML scripts that can reproduce the issue will be appreciated.

Posted: **Tue 06 Mar 2012 21:36**

Hi
Here is the code being used to call a stored procedure named sp_update_status.
Stored procedure

PROCEDURE sp_update_status (
payment_id IN payment.PAYMENT_ID%TYPE,
payment_status_id IN payment.PAYMENT_STATUS_ID%TYPE,
user_modified IN payment.USER_MODIFIED%TYPE,
result OUT NUMBER) IS
BEGIN
UPDATE payment mp
SET MP.PAYMENT_STATUS_ID = payment_status_id,
MP.USER_MODIFIED = user_modified,
MP.DATE_MODIFIED = SYSDATE
WHERE MP.PAYMENT_ID = payment_id;

result := 1;
EXCEPTION
WHEN OTHERS THEN
result := 0;
END sp_update_status;

.Net Code
// Create a connection
OracleConnection objConTest = GetNewConnection();
objConTest.Open();
// Create a command
OracleCommand objCmdTest = new OracleCommand("sp_update_status", objConTest);
objCmdTest.CommandType = CommandType.StoredProcedure;

objCmdTest.Parameters.Add(new OracleParameter("payment_id", OracleDbType.Long));
objCmdTest.Parameters.Add(new OracleParameter("payment_status_id", OracleDbType.Long));
objCmdTest.Parameters.Add(new OracleParameter("user_modified", OracleDbType.VarChar));
objCmdTest.Parameters.Add(new OracleParameter("result", OracleDbType.Integer,ParameterDirection.Output));

objCmdTest.Parameters["payment_id"].Value = new long[] {58, 62};
objCmdTest.Parameters["payment_status_id"].Value = new long[] { 6, 6 };
objCmdTest.Parameters["user_modified"].Value = new string[] { "Test", "Test" };
objCmdTest.Parameters["result"].Value = new int[] { 0, 0 };

objCmdTest.ExecuteArray(2);

objConTest.Close();
objCmdTest.Dispose();

The error we get is:System.InvalidCastException: Unable to cast object of type 'Devart.Data.Oracle.ch[]' to type 'Devart.Data.Oracle.ch'.

Posted: **Mon 12 Mar 2012 11:40**

The exception occurs because you use wrong parameter type. Change:

Code: Select all

objCmdTest.Parameters.Add(new OracleParameter("payment_id", OracleDbType.Long));
objCmdTest.Parameters.Add(new OracleParameter("payment_status_id", OracleDbType.Long));

To

Code: Select all

objCmdTest.Parameters.Add(new OracleParameter("payment_id", OracleDbType.Number));
objCmdTest.Parameters.Add(new OracleParameter("payment_status_id", OracleDbType.Number));

OracleDbType.Long is not the same type as long in C#

Additionally you need to change the parameter names because the names of the procedure parameters are the same as the column names in the Payment table. Thus when executing

Code: Select all

MP.PAYMENT_STATUS_ID = payment_status_id

The payment_status_id column value will be used for corresponding payment_id instead of the payment_status_id parameter value. And only one column will be updated (DATE_MODIFIED). All rows will be updated, not only specified in the parameter list.

Devart Forums

Bulk Insert of VARRAYS

Bulk Insert of VARRAYS

Bulk insert Error

Bulk Insert error