Azure Data Factory : Unable to copy data into table with Constraints

Question

Hello ,

i am trying to use copy activity using Azure Data factory,

1. reading file from Azure storage from CSV file as a source
2. and in Sink connect to Oracle Database and insert into table
3. Table is having Constraints
4. while loading the data , Data factory goes in error :

Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-26086: direct path does not support triggers

Error in parameter 1.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-26086: direct path does not support triggers

Error in parameter 1.,Source=msora28.dll,'

5. After Debugging , we found that, table has a constraints and that cause it gives an error

so if you can please provide solution for this to insert data into table

Answer 1

Hello @Mahendra Pawar

Welcome to Microsoft Q&A and thank you for posting your questions here.

You encountering and error: Error in parameter 1.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-26086: direct path does not support triggers

I'm sorry to hear that you're having this error. The error message is indicating that when you are trying to perform a data operation, such as an insert or update, on a table that has a trigger defined, it's not compatible with the direct path loading method. Direct path loading is a high-performance mechanism for quickly loading large amounts of data into an Oracle database, and it has some limitations, including not supporting certain triggers.

Here are some steps to help you resolve this issue: Do these independently.

1. Re-run the change when the database is idle.
2. Do all DDL during a maintenance window with all end-users locked-out.
3. Kill the sessions that are preventing the exclusive lock.
4. Examine the triggers that are defined on the table where you are encountering the error.
5. If the triggers are not essential for the data load operation, you can consider disabling them temporarily.
6. Instead of using direct path loading, you can use conventional path loading. Conventional path loading is slower but more flexible and compatible with triggers.
7. If disabling triggers or changing the loading method is not feasible, consider loading the data in smaller batches, loading data in smaller portions might allow you to bypass the issue without compromising the database's integrity.
8. If the triggers are necessary, you may need to review and optimize them to minimize their impact on the data loading process.

NOTE: These are personal experience and little research about your error code: Remember that making changes to triggers and data loading methods can have a significant impact on your database's behavior and performance, so it's very important to thoroughly test any changes in a non-production environment before applying them to your production database.

I hope this is helpful! PS: Do not hesitate to let me know if you have any other questions.

Please remember to "Accept Answer" if answer helped, so that others in the community facing similar issues can easily find the solution.

Best Regards,

Sina Salam