ODBC Error [HY104] during Data Transfer to Oracle via Azure Data Factory: Character, decimal, and binary parameters cannot have a precision of zero

Question

Hello,

I am encountering an issue while transferring data to an Oracle database using Azure Data Factory copy activity. The operation fails with the following error message:

ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY104] [Microsoft][ODBC Oracle Wire Protocol driver]Character, decimal, and binary parameters cannot have a precision of zero. Error in parameter 111.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [HY104] [Microsoft][ODBC Oracle Wire Protocol driver]Character, decimal, and binary parameters cannot have a precision of zero. Error in parameter 111.,Source=msora28.dll,'

Parameter 111 is defined as a varchar(12) nullable field. Here are some additional details:

1. Data Transfer Tool: Azure Data Factory
2. ODBC Driver: Microsoft ODBC Oracle Wire Protocol driver
3. Field Definition: varchar(12) nullable
4. Source Data: Dedicated SQL Pool view (in 111 position, the filed is varchar(12), the source field contains various string values of length 12, 2,036,569 NULL values, and 9,756 blank values)
5. Destination Data: Oracle table (In 111 position, the destination field is a VARCHAR2(12 CHAR) with NULLABLE = YES)

What could be causing this error for the varchar(12) field?

Are there specific settings that I should check?

Thank you for any suggestions or solutions!

Answer 1

@Tommaso Di Gennaro
Welcome to Microsoft Q&A platform and thanks for posting your question here.

The error is likely caused by the ODBC driver interpreting blank values in the varchar(12) field as having a precision of zero. To fix this, modify your source SQL query to handle blank values before transferring them. Replace blank values with NULL or a default value. Also, make sure your ODBC driver settings are correctly configured.

Check for any specific parameters that might need to be added to the connection string to handle character data correctly. Finally, verify that the field mappings between the source and destination in Azure Data Factory are set up correctly. By doing this, you can avoid the precision error and ensure a smooth data transfer.

To configure your ODBC driver settings for handling character data, check and adjust the following: Set the ColumnSizeAsCharacter parameter in the registry to ensure the column size is interpreted as the number of characters rather than bytes. Edit the odbc.ini file and set the EnableNcharSupport parameter to 1 to enable support for NCHAR data types. Ensure that the IANAAppCodePage option in the odbc.ini file matches the character set of your Oracle metadata installation.

Reference
https://learn.microsoft.com/en-us/answers/questions/1611466/azure-data-factory-copy-data-action-from-sqlserver
https://learn.microsoft.com/en-us/answers/questions/1031326/azure-data-factory-copy-data-action-gives-an-error

I hope this information helps you. Let me know if you have any further questions or concerns.

Answer 2

Based on this old thread, the NULL and the blank may be the cause. Given that the source data has a significant number of NULL values and some blank values, you need to ensure these are being handled properly. ADF should be able to distinguish between NULL and empty strings, but sometimes additional configuration is required.