Can we update data in oracle database table using azure data factory/synapse. if not what other options we have to upsert data to oracle table using azure

Question

I have table data in azure sql and I am trying to upsert data from azure sql to oracle db table using ADF. but ADF has only option to create a record in oracle but not update. Is there a option to update a record in oracle table based on the key value using ADF?

Answer 1

Hello Vikyath Mk,

Welcome to the MS Q&A platform.

using copy activity, Oracle as sink does not support upset.

Please see the below document for the supported properties.

https://learn.microsoft.com/en-us/azure/data-factory/connector-oracle?tabs=data-factory#oracle-as-sink

I see the below feedback for this feature request in the ADF feedback channel, which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

https://feedback.azure.com/d365community/idea/78d1f565-23a2-ed11-a81b-6045bd8615b0

As a workaround you can use Azure functions to perform the upsert operation.

Steps:

• Create an Azure Function with an HTTP trigger that accepts the data from Azure SQL and performs the upsert operation on the Oracle database (You can write your custom function code using Python to accept the data from Azure SQL and perform the upsert operation on the Oracle database. )
• In ADF, create a pipeline with a Lookup activity to fetch the data from Azure SQL.
• Add a ForEach activity to iterate over the rows fetched by the Lookup activity.
• Inside the ForEach activity, add a Web activity to call the Azure Function with the row data as input.

I hope this helps. Please let me know if you have any further questions.

If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.