ADF with Oracle Connectivity Issue

New Contributor

Issue No. 1 :

Bulk insert from the copy activity is causing issue when the target database is having a trigger associated with the table . Error Details :- ORA-26086: direct path does not support triggers Error in parameter 1 Referred Link :- https://knowledge.informatica.com/s/article/14925?language=en_US

 

Summary :- In our case we have a table in oracle database ( on premise ) which is associated with update / insert trigger . Because of this trigger , bulk insert is not working with the ADF copy activity and it is expecting the rows should be inserted or updated one by one .

 

 

Issue No. 2:

  • We’re facing issues while executing the update/insert/delete queries on Oracle database tables using Lookup activity. We understand that the Lookup activity expects an output for each execution irrespective of the operation performed.

     

    The Lookup activity in ADF expects a response output in every case irrespective of the query type being fired. This is query output which the Lookup activity looks for and not the script output.

     

    When the Lookup activity is connected to Oracle database, the ''OUTPUT'' clause doesn't work as it is SQL native. As per Oracle, the corresponding feature for fetching the output by executing DML queries in the Oracle database is ''RETURNING'' which didn't work in respective database.  Also, the Oracle connector doesn't allow firing multiple queries at the same time in a single Lookup activity like the case in Azure SQL connector.

0 Replies