ORA-26026 / ORA-01452 in OracleLoader

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Jung
Posts: 6
Joined: Thu 07 Feb 2013 06:49

ORA-26026 / ORA-01452 in OracleLoader

Post by Jung » Tue 26 Feb 2013 07:06

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: ORA-26026 / ORA-01452 in OracleLoader

Post by Pinturiccio » Tue 26 Feb 2013 14:13

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
Posts: 6
Joined: Thu 07 Feb 2013 06:49

Re: ORA-26026 / ORA-01452 in OracleLoader

Post by Jung » Tue 26 Feb 2013 14:37

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: ORA-26026 / ORA-01452 in OracleLoader

Post by Pinturiccio » Wed 27 Feb 2013 14:59

Jung wrote: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
Posts: 6
Joined: Thu 07 Feb 2013 06:49

Re: ORA-26026 / ORA-01452 in OracleLoader

Post by Jung » Wed 27 Feb 2013 15:34

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)

Post Reply