CODEWITHSUNDEEP
.netoracle-databasetriggersodp.net
hieund
hieund

Reputation: 370

OracleBulkCopy does not support Triggers

My software needs to insert about 5000 records into Oracle database at once, so I used OracleBulkCopyto do this. My table use trigger to auto increment primary key. But the OracleBulkCopythrow "ORA-26086: direct path does not support triggers" exception.

So, how can I both use OracleBulkCopy and auto increment primary key?

Upvotes: 5

Views: 8852

Answers (3)

hieund
hieund

Reputation: 370

Justin Cave's solution is easy to achieve but there is one problem. That is my steps should be :

  1. Disable trigger
  2. Get 5000 Ids from sequences
  3. Assign Ids to records
  4. Execute bulk insert into database
  5. Enable trigger

If an user is at step 2 (or 3, 4) - he disabled trigger. And another user at different context also insert a record into my table at that time, so that, he cannot get the increased id;

Marc Gravell's solution seems very nice. But it's hard for me to achieve. My project is using Entity Framework. Do you mean that: with each table in the database, i must create another table with the same structure ?

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1063774

A common solution to this type of scenario is to perform the bulk load into a staging table; a separate table without triggers, etc that you can throw the data into quickly. This means you get the advantage of bulk-load in terms of bandwidth and round-trip performance. Then; when and only when the data is in the staging table, use regular SQL (presumably insert) to move the data from the staging table into the actual transactional table. This is then entirely local the the database server, so is very fast.

A nice advantage of this is that it means that *while you are doing the bulk load you aren't impacting any real users - as the real users will only be looking at the transactional table - which we haven't touched yet.

Upvotes: 5

Justin Cave
Justin Cave

Reputation: 231791

It appears from the ODP.Net Developer's Guide that OracleBulkCopy class does a direct-path load. As the error indicates, you cannot do a direct-path load into a table with enabled triggers.

If you want to use the OracleBulkCopy class, you could potentially disable the trigger that generates the primary key, fetch 5000 values from the sequence, and then use those values in your application. You could then re-enable the trigger once the load is complete. Of course, that would mean that no other sessions could be loading data into that table at the same time.

Upvotes: 3

Related Questions