how to execute Oracle merge statement in ADF v copy data ?

Copy data activity expects a mapping between source and sink to insert record from source to target.

In real time, especially for scd type 1 data load, you may require insert, update and delete statement execution.

Adf doesn't support scd type 1 for Oracle db source.

Resolution:

Oracle supports the above through merge statement.

For implementing above in copy data, I have done the trailing:

1. The select SQL statement used in merge clause be used as source SQL. In filter ad 1=2

2. The update sink connection with target table in merge clause

3. In sink section, execute merge clause statement in pre-copy script section

4. Debug for insert , update and delete use cases.

Let me know if you face any challenges.

P.S: we don't need to call procedures to achieve the above

Error while Copying data from Oracle 12c to Oracle 19c db using ADF V2 copy data activity

In ADF pipeline, while I use copy data for connecting to Oracle 19c Database, i get following error:

Components Used :

Copy data source / sink uses Oracle 19c and connectivity is established through Oracle linked service with SHIR run time

While copying data from azure sql to oracle I am getting following error.

{
    "errorCode": "2200",
    "message": "Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver]Data type for parameter 3 has changed since first SQLExecute call.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver]Data type for parameter 3 has changed since first SQLExecute call.,Source=msora28.dll,'",
    "failureType": "UserError",
    "target": "Copy_26n"
}

Resolution:

While creating linked service for Oracle 19c DB, add additional connection properties:

Property name: workarounds 

Value: 536870912