Azure Data Factory - Copy Data action from SQLServer to Oracle gives an error like "Character, decimal, and binary parameters cannot have a precision of zero"

Question

I am using Copy data in order to move files from Sql server to Oracle,

I have cases where in Sql server side , columns have data type of nvarchar and can have null value or empty string '' for the cases where I have empty string - it can't be inserted into Oracle DB Server (varchar column)

I get the error:

Failure happened on 'Sink' side. 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 6.,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 6.,Source=msora28.dll,'

As a workaround i applied in oracle side nullif(column_name,'') but i don't want to do it for each and every view i read in source side - I wanted to apply something dynamic in each and every column in adf side.

Answer 1

Hello Stergiou Theofilos,

Welcome to the Microsoft Q&A forum.

One way to handle this issue is to use a data flow in ADF to transform the data before inserting it into Oracle. In the data flow, you can use a derived column transformation to replace empty strings with null values.

You can use the below expression. The expression checks if the length of the trimmed CountryName value is zero, and if so, replaces it with an empty string. Otherwise, it keeps the original value. This should allow the data to be inserted into the Oracle database without any issues.

iifNull(trim(Column_name), Column_name)

I hope this anwers your question.