Bulk Insert Crash

Bulk Insert Crash

Postby Alladin » Sat 14 Nov 2009 14:55

Hello there,

I'm experiencing crash of my .NET application using both x32 and x64 Oracle drivers. The application tries to insert 100 records using bulk insert methods (command.ExecuteArray).

Requirements:
1) Instant client 11.1.0.7 x64 and x32.
2) Unicode 10G Release 2 database.
3) OS Windows 7 x64

Here is the code to reproduce the crash:

Code: Select all
using System;
using Devart.Data.Oracle;

namespace BulkInsert
{
  class Program
  {
    const string connection = "data source=test_server;user id=test_user;password=test_password;unicode=true";
    const string pathToTnsNames = @"c:\oracle";
#if x64
    const string pathToInstantClient = @"c:\oracle\x64;";
#else
    const string pathToInstantClient = @"c:\oracle\x32;";
#endif

    static void Main(string[] args)
    {
      Environment.SetEnvironmentVariable("TNS_ADMIN", pathToTnsNames);
      Environment.SetEnvironmentVariable("PATH", pathToInstantClient + Environment.GetEnvironmentVariable("PATH"));

      using (var con = new OracleConnection(connection))
      {
        con.Open();
        Console.WriteLine("Client: {0}", con.ClientVersion);

        string schema;

        using (var cmd = con.CreateCommand())
        {
          cmd.CommandText = "select sys_context('userenv', 'current_schema') from dual";
          schema = (string)cmd.ExecuteScalar();

          try
          {
            cmd.CommandText = string.Format(
  @"create or replace type {0}.geld as object
( mv number,
  mc char(3)) final", schema);
            cmd.ExecuteNonQuery();
          }
          catch
          {
            // sometimes fails if exists
          }

          cmd.CommandText = string.Format("begin\n dbms_utility.compile_schema('{0}', FALSE);\nend;", schema);
          cmd.ExecuteNonQuery();

          try
          {
            cmd.CommandText = string.Format(
  @"create table tablegeld (
   guid raw(16) default sys_guid() not null,
   name varchar2(150 char) null,
   csize {0}.geld)", schema);
            cmd.ExecuteNonQuery();
          }
          catch (OracleException e)
          {
            if (e.Code != 955) // table exists
              throw;
          }
        }

        const int batchSize = 100;

        var moneyType = OracleType.GetObjectType(schema.ToUpper(), "GELD", con);
        var moneyUnit = moneyType.Attributes["MC"];
        var moneyValue = moneyType.Attributes["MV"];

        using (var cmd = new OracleCommand("insert into tablegeld (contract_size) values(:mygeld)", con))
        {
          var random = new Random();
          var values = new OracleObject[batchSize];
          for (int i = 0; i < values.Length; i++)
          {
            var value = new OracleObject(moneyType);
            value[moneyUnit] = "EUR";
            value[moneyValue] = random.NextDouble() * 1000000;
            values[i] = value;
          }

          var param = cmd.Parameters.AddWithValue("mygeld", values);
          param.ObjectTypeName = moneyType.Name;

          cmd.ExecuteArray(batchSize);
        }

        con.Commit();
      }
    }
  }
}


Hope this helps,
Alexey
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby Shalex » Mon 16 Nov 2009 09:08

Please note that usage of array binding with the OracleObject classes is not supported. For detailed information, please refer to http://www.devart.com/dotconnect/oracle ... yBind.html .
Shalex
Devart Team
 
Posts: 7535
Joined: Thu 14 Aug 2008 12:44

Postby Alladin » Mon 16 Nov 2009 15:23

Is there some technical reason why it is not implemented?

As far as I see, support of Object Types is advertised as a major feature of this product. So in my opinion, if you declare support of Object Types, you should support them across all of the product.

We as your customers are really confused now to find out that our long term investment in your drivers lacks some declared features like bulk inserts & object types.

We always knew, that there is a performance optimization potential in our software by using bulk inserts. And now, when we actually decided to use it and found time to change implementation, you say it is not possible...

When could we expect this feature nevertheless implemented if possible? We have to deliver "the happy message" to our clients now...
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby Shalex » Thu 19 Nov 2009 10:05

We have answered you in this thread: http://www.devart.com/forums/viewtopic.php?t=16390.
Shalex
Devart Team
 
Posts: 7535
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle