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

Tommaso Di Gennaro 20 Reputation points
2024-07-01T08:57:18.64+00:00

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!

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,135 questions
Accepted answer
  1. Harishga 5,910 Reputation points Microsoft Vendor
    2024-07-01T11:18:58.7333333+00:00

    @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://video2.skills-academy.com/en-us/answers/questions/1611466/azure-data-factory-copy-data-action-from-sqlserver
    https://video2.skills-academy.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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 19,706 Reputation points
    2024-07-01T11:55:19.4566667+00:00

    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.

    0 comments