Copy Activity - Azure Sql to Oracle

Question

Copy Activity - Azure Sql to Oracle

Vivek, Anand 0

Hi,

I am using "Copy Activity" to load the data into ORACLE from Azure SQL.

In Azure SQL datatype is NVARCHAR(4000) and in ORACLE datatype is VARCHAR2(4000 CHAR).

in this case copy activity fails with following error. Any suggestion on how to resolve this?

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 10 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 10 has changed since first SQLExecute call.,Source=msora28.dll,'

Note : I tried changing the datatype in ORACLE from VARCHAR2(4000 CHAR) to NVARCHAR2(2000) but now I start getting length issue as in source column length is more than 2000.

Answer 1

Amira Bedhiafi 4,461

I think your issue is related to data type mismatches or inconsistencies during the data transfer process. The copy process fails when you have a character not represented in VARCHAR2.

Ensure that the Oracle database's character set supports the characters present in your Azure SQL data. Unicode characters might not be correctly represented in databases with non-Unicode character sets. For instance, AL32UTF8 is a common Unicode character set for Oracle databases.

Also, heck if any rows in your Azure SQL data have values in the NVARCHAR(4000) column that exceed 4000 characters when represented in Oracle's character set. If yes, you'll need to trim, split, or otherwise manage these values.

Instead of directly copying data from Azure SQL to Oracle, consider copying the data into an intermediate format like Parquet or CSV. From there, you can load the data into Oracle. This might give you more flexibility in terms of data transformation and cleansing.

In the ADF copy activity, you can define data type mapping. Define the source type NVARCHAR to map to the Oracle VARCHAR2(4000 CHAR). Ensure that the data does not contain characters that are not supported in the Oracle VARCHAR2 datatype.

Vivek, Anand 0 Reputation points

2023-09-20T02:45:14.2733333+00:00

In Oracle character set is correctly set to AL32UTF8.
If I increase the Write batch size in such a way that it is more than the number of rows in table then its works perfectly. I dont want do this as table data would keep growing and I cant keep increasing Write Batch size.
BhargavaGunnam-MSFT 19,316 Reputation points Microsoft Employee

2023-09-20T23:21:16.9+00:00

Hi Vivek, Anand,

This seems like more of a performance issue.

If increasing the write batch size resolves the issue, can you check and see if you can split the data into smaller batches and process them?

You can use a where clause in the SQL to set the batch size and then use a loop to process each batch separately.
Vivek, Anand 0 Reputation points

2023-09-21T02:13:13.4666667+00:00

Isn't Write batch size is supposed to do that? It commits the data as per the size mentioned in this?
Vivek, Anand 0 Reputation points

2023-09-21T02:24:38.24+00:00

Hi @BhargavaGunnam-MSFT

Performance issue is which component. SHIR? Source Query? Sink?
I would prefer to resolve the performance issue rather than over engineer simple copy activity
Amira Bedhiafi 4,461 Reputation points

2023-09-21T08:36:21.21+00:00

Can you use EXPLAIN PLAN to see the execution plan and see if you can optimize it ?
Vivek, Anand 0 Reputation points

2023-09-21T12:11:13.9266667+00:00

This Select all rows from Azure SQL table and direct insert into Oracle table.
How I can use explain plan in this to make this "performant". And I don't think this is performance issue.

I believe ADF Copy to Oracle is not working as expected.
Amira Bedhiafi 4,461 Reputation points

2023-09-21T14:16:14.6066667+00:00

If you assume that there is a bug try to open a ticket ;)

Copy Activity - Azure Sql to Oracle

1 answer

Activity