Bulk Insert of VARRAYS

Bulk Insert of VARRAYS

Postby Alladin » Sat 14 Nov 2009 19:01

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

Code: Select all
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?
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby Shalex » Mon 16 Nov 2009 10:50

We will investigate the issue and notify you about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » 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.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » 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.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby Alladin » Fri 20 Nov 2009 18:44

Does it include promised feature?
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby lasseschou » 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
lasseschou
 
Posts: 13
Joined: Mon 23 Nov 2009 08:59

Postby Shalex » 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.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Bulk insert Error

Postby vkarumbaiah » 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
Last edited by vkarumbaiah on Tue 14 Feb 2012 22:16, edited 1 time in total.
vkarumbaiah
 
Posts: 3
Joined: Thu 05 Jan 2012 23:24

Bulk Insert error

Postby vkarumbaiah » 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'
vkarumbaiah
 
Posts: 3
Joined: Thu 05 Jan 2012 23:24

Postby Pinturiccio » 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.
Pinturiccio
Devart Team
 
Posts: 1860
Joined: Wed 02 Nov 2011 09:44

Postby vkarumbaiah » 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'.
vkarumbaiah
 
Posts: 3
Joined: Thu 05 Jan 2012 23:24

Postby Pinturiccio » 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.
Pinturiccio
Devart Team
 
Posts: 1860
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle