OracleBulkCopy.WriteToServer() Method Disregards Primary Keys On Destination Table (Doc ID 1181547.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Data Provider for .NET - Version 9.2.0.8 and later
Microsoft Windows (32-bit)
Microsoft Windows x64 (64-bit)
***Checked for relevance on 23-Dec-2013***

Symptoms

When using the Oracle Data Provider for .NET (ODP.NET) OracleBulkCopy.WriteToServer() method your are able to add duplicate data into the destination table even if a primary key exist on the data that would normally prevent it from being added from a SQL environment.

Here is an example of the problem...

SQL*Plus Script

DROP TABLE test_tab;

-- Create test table
CREATE TABLE test_tab (
  id NUMBER NOT NULL,
  CONSTRAINT "TEST_TAB_PK" PRIMARY KEY ("ID")
);

-- Insert 1 row of data
INSERT INTO test_tab VALUES (1);

-- The second insert generates an "ORA-00001: unique constraint violated" error which is expected behavior from a SQL*Plus environment
INSERT INTO test_tab VALUES (1);



ODP.NET C# Code

try
{
  // set the user id, password and data source appropriately
  string ConnectionString = "User Id=scott; Password=tiger; Data Source=orcl";

  DataTable rDT = new DataTable();
  OracleDataAdapter rODA = new OracleDataAdapter(@"SELECT id FROM test_tab",ConnectionString);
  rODA.Fill(rDT);

  MessageBox.Show("DataTable Created");

  Oracle.DataAccess.Client.OracleBulkCopy rOracleBulkCopy = new OracleBulkCopy(ConnectionString);
  rOracleBulkCopy.DestinationTableName = "TEST_TAB";
  rOracleBulkCopy.WriteToServer(rDT);

  MessageBox.Show("Rows Copied To Destination Table Disregarding PK");
}
catch (Exception ex)
{
   MessageBox.Show(ex.ToString());
}


The above example code will run without error and two rows each containing the same ID will be inserted into a table that has a Primary Key on the ID column.

SQL> SELECT * FROM test_tab;

ID
----------
1
1


The next time you attempt to insert data into this table you will see one of the following errors...

ODP.NET Environment

Oracle.DataAccess.Client.OracleException Error in row '-437433' column '1' ORA-26026: unique index SCOTT.TEST_TAB_PK initially in unusable state at Oracle.DataAccess.Client.OracleBulkCopy.PerformBulkCopy()
at Oracle.DataAccess.Client.OracleBulkCopy.WriteDataSourceToServer()
at Oracle.DataAccess.Client.OracleBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
at Oracle.DataAccess.Client.OracleBulkCopy.WriteToServer(DataTable table)
at TestCase.Form1.button1_Click(Object sender, EventArgs e) in E:\TestCases\3-2033617311\TestCase\TestCase\Form1.cs:line 34


SQL*Plus Environment

SQL> INSERT INTO test_tab VALUES (1);
INSERT INTO test_tab VALUES (1)
*
ERROR at line 1:
ORA-01502: index 'SCOTT.TEST_TAB_PK' or partition of such index is in unusable state

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms