OracleBulkCopy does not support triggers

Question

My software should insert about 5,000 records into the Oracle database right away, so I used OracleBulkCopy for this. My table uses a trigger to automatically increase the primary key. But OracleBulkCopy throws an exception "ORA-26086: direct path does not support triggers."

So, how can I use OracleBulkCopy and automatically increase the primary key? (sorry for my bad english)

Answer 1

A common solution to this type of scenario is to perform bulk loading into an intermediate table; separate table without triggers, etc. that can be quickly transferred. This means that you get the advantage of volumetric load in terms of bandwidth and round-trip performance. Then; when and only when data is in the staging table, use plain SQL (supposedly insert ) to move the data from the staging table to the actual transaction table. This is then a fully local database server, so very fast.

A good advantage of this is that it means that * while you are doing bulk uploads, you do not affect real users, as real users will only look at the transaction table, which we have not yet touched on.

Answer 2

The ODP.Net Developer Guide shows that the OracleBulkCopy class loads the direct path . As the error indicates, you cannot load a direct path into a table with triggers enabled.

If you want to use the OracleBulkCopy class, you can potentially disable the trigger that generates the primary key, extract 5,000 values ​​from the sequence, and then use these values ​​in your application. You can then turn on the trigger again after the download is complete. Of course, this would mean that no other sessions could load data into this table at the same time.

Answer 3

Justin Cave's solution is easy to achieve, but there is one problem. These are 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 the user is in step 2 (or 3, 4), he disabled the trigger. And another user in a different context also inserts a record into my table at that time, so that he cannot get the increased id;

Mark Gravell’s decision seems very pleasant. But it’s hard for me to achieve. My project uses Entity Framework. You mean: with each table in the database, I have to create another table with the same structure?