ORA-26026 / ORA-01452 in OracleLoader

[Jung]

Due to the fact that array binding is not working at the moment (see http://forums.devart.com/viewtopic.php?f=1&t=25822) I try to use the Oracle Loader functionality. According to the documentation it is only supported in OCI Mode. If I try inserting data in OCI mode I will get a ORA-26026 which should not occur. The documentation says that the primary key constraint is enabled.

Here is my example code (Please execute it twice).

Code: Select all

 OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder
      {
        Direct = false,
        Server = "test",
        ServiceName = "test",
        Sid = "test",
        Port = 1521,
        UserId = "TEST",
        Password = "TEST"
      };
      String ConnString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + oraCSB.Server
                 + ")(PORT= 1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=" + oraCSB.Sid + ")));User Id="
                 + oraCSB.UserId + ";Password=" + oraCSB.Password + ";";


      OracleConnection connection = new OracleConnection(ConnString);
      connection.Open();
      connection.AutoCommit = true;

      OracleCommand Command = connection.CreateCommand();
      Command.CommandText = "create table test (column1 varchar2(24) NOT NULL,column2    INTEGER, Column3 REAL, CONSTRAINT PK_TEST PRIMARY KEY  (Column1))";
      try
      {
        Command.ExecuteNonQuery();
      }
      catch (Devart.Data.Oracle.OracleException ex)
      {
        string exception = ex.Message;
      }
      Command.Dispose();
      OracleLoader loader = new OracleLoader();
      loader.Connection = connection;
      loader.TableName = "test";
      loader.CreateColumns();
      
      for (int i = 0; i < 2; i++)
      {
        loader.Open();
        try
        {
          loader.SetValue(0, "test1");
          loader.SetValue(1, 0);
          loader.SetValue(2, 0.0);
          loader.NextRow();
          loader.SetValue(0, "test2");
          loader.SetValue(1, 0);
          loader.SetValue(2, 0.0);
          loader.NextRow();
          loader.SetValue(0, "test3");
          loader.SetValue(1, 0);
          loader.SetValue(2, 0.0);
          loader.NextRow();
          loader.Close();
        }
        catch (Devart.Data.Oracle.OracleException ex)
        {
          string test = ex.Message;
        }
      }

Calling loader.Close() in the 2nd run will cause an ORA-26026 exception internally which I cannot catch. If I look at the table I see duplicated entries and that's why when rebuiling the indexes I will get an ORA-01452 exception. The only solution is to drop the primary key, remove the duplicates and then to add the primary key again. But this is not feasible if tables are getting bigger.

I also tried this in direct mode although I know it is not officially supported. But the behaviour is the same.

[Pinturiccio]

OracleLoader uses direct path loads. Unlike conventional path load, when the PRIMARY KEY constraint check is performed after every row is loaded, during direct path load, the state of the index corresponding to the PRIMARY KEY constraint becomes UNUSABLE. Thus, the PRIMARY KEY constraint is checked only after all rows are loaded. If a row violates the PRIMARY KEY constraint, this row is loaded into a table anyway, but the corresponding index will remain in the UNUSABLE state after the load is completed. In this case you should locate the duplicated rows, get rid of them and rebuild the index yourself.

After the first run or your code, PK_TEST index will have the UNUSABLE state. During the second run of the application, you get the ORA-26026 exception, which is a correct behavior.

Note, that the ORA-26026 exception occurs in the internal code of dotConnect for Oracle and is handled within it. It is likely, that you have the option of generating all internal exceptions turned on in the debug mode.

[Jung]

Thank you for your reply.

I catch also all internal exceptions, that's right. I thought your implementation of OracleLoader is similar to Datapump.

Dropping the primary key after all rows are loaded, deleting duplicates and rebuilding the index is not applicable for me. If we have a small database then I think it should work but not with large databases. Doing these extra steps is perhaps not much faster than inserting every dataset with a seperate statement.

Is there any other way to insert data really fast with Devart?

[Pinturiccio]

Is there any other way to insert data really fast with Devart?

dotConnect for Oracle provides two ways of fast data insertion:
1. Using the OracleLoader class - http://www.devart.com/dotconnect/oracle ... oader.html
2. Using array binding - http://www.devart.com/dotconnect/oracle ... yBind.html

[Jung]

Okay, currently none of these methods can be used to insert data fast.

So I guess I have to wait for the new version. (One bug was fixed today)