Bulk Insert Crash

[Alladin]

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

[Shalex]

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 .

[Alladin]

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...

[Shalex]

We have answered you in this thread: http://www.devart.com/forums/viewtopic.php?t=16390.