OracleBulkCopy does not support Triggers

Question

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?

score 4 · Answer 1 · answered Jun 14 '13 at 09:55

4

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.

answered Jun 14 '13 at 09:55

Marc Gravell

1,026,079
266
2,566
2,900

@hieund which bit was unclear...? 2 tables, your existing regular table with triggers etc, and a second table that doesn't; bulk-insert into the 2nd table, then move the data over with `insert`... – Marc Gravell Jun 26 '13 at 07:55
Yes, my problem is that if i have 100 tables, i must create others 100 tables with same structure to use bulk insert for each table. So that my database has 200 tables, it is too much and hard to maintain. It is very usefull if there is a table that can represent for these others 100 tables. – hieund Jun 27 '13 at 08:51

score 2 · Answer 2 · answered Jun 14 '13 at 09:45

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.

score 0 · Answer 3 · edited May 30 '18 at 07:29

0

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

Disable trigger
Get 5000 Ids from sequences
Assign Ids to records
Execute bulk insert into database
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 ?

edited May 30 '18 at 07:29

Patrick Hofman

153,850
22
249
325

answered Jun 17 '13 at 09:46

hieund

356
1
6
16

It's has been a long time. But I think that we should not disable the trigger. Just check if the input ID of the trigger is null or zero (0) then use the sequence. Otherwise, just use the input id. – hieund Jan 23 '18 at 07:14

OracleBulkCopy does not support Triggers

3 Answers3