ERROR [22001] [Microsoft][ODBC Oracle Wire Protocol driver]String data, right truncated saving text into CLOB

Question

Hi,

I have a data factory setup, where I download a json file from an external provider, and I want to save the json in a CLOB field in Oracle table.
My pipeline retrieves the json using a REST call, and saves the result into Blob storage. In a second step, I read the saved json file as a csv with 1 column, and store it into the CLOB field in the table.

This gives me the following error :

Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [22001] [Microsoft][ODBC Oracle Wire Protocol driver]String data, right truncated. Error in parameter 1.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [22001] [Microsoft][ODBC Oracle Wire Protocol driver]String data, right truncated. Error in parameter 1.,Source=msora28.dll,'

In an attempt to narrow down the problem, I have downloaded the json file to local storage, and converted the extension to csv. So there is only 1 "copy data" action in the pipeline : read the csv, and save the column to the clob field in the Oracle database.

The ODBC connection url looks like

Host=...;Port=...;ServiceName=...;User Id=...;Password=...;ArraySize=‭10485760;WorkArounds=540016640

where I already applied a few workarounds, see 4837 (540016640 = 536870912 + 2097152 + 1048576).

I don't know how to get further details to troubleshoot this issue.
Could it be that the drivers that the integration runtime is using, are too old ? I see Microsoft Oracle ODBC Driver_8.0.2.2417.
Is there a way to configure the intergration runtime to use the official Oracle ODBC drivers instead ?

Here 's the pipeline code :

{
    "name": "pl_OraCLOB_to_DWH_copy1",
    "properties": {
        "activities": [
            {
                "name": "cp_Purchase_Orders",
                "type": "Copy",
                "dependsOn": [],
                "policy": {
                    "timeout": "7.00:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "DelimitedTextSource",
                        "storeSettings": {
                            "type": "FileServerReadSettings",
                            "recursive": true,
                            "enablePartitionDiscovery": false
                        },
                        "formatSettings": {
                            "type": "DelimitedTextReadSettings"
                        }
                    },
                    "sink": {
                        "type": "OracleSink",
                        "maxConcurrentConnections": 1
                    },
                    "enableStaging": false,
                    "parallelCopies": 1,
                    "validateDataConsistency": true,
                    "logSettings": {
                        "enableCopyActivityLog": true,
                        "copyActivityLogSettings": {
                            "logLevel": "Info",
                            "enableReliableLogging": false
                        },
                        "logLocationSettings": {
                            "linkedServiceName": {
                                "referenceName": "bst_stmrgcostman01",
                                "type": "LinkedServiceReference"
                            },
                            "path": "adfstaging"
                        }
                    },
                    "translator": {
                        "type": "TabularTranslator",
                        "mappings": [
                            {
                                "source": {
                                    "name": "column1",
                                    "type": "String"
                                },
                                "sink": {
                                    "name": "JSON_CONTENT",
                                    "type": "String"
                                }
                            }
                        ]
                    }
                },
                "inputs": [
                    {
                        "referenceName": "file_tmp",
                        "type": "DatasetReference",
                        "parameters": {
                            "file_name": "exportedPurchaseOrders.csv"
                        }
                    }
                ],
                "outputs": [
                    {
                        "referenceName": "ora_DWH_LOAD",
                        "type": "DatasetReference",
                        "parameters": {
                            "table_name": "BW_PURCHASE_ORDERS"
                        }
                    }
                ]
            }
        ],
        "folder": {
            "name": "OraCLOB"
        },
        "annotations": []
    }
}

Thanks a lot

Bart

Answer 1

Hello,
I am having the same issue with our SHIR.
Here is the error we receive.
Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver]Error tolerance exceeded. Bulk load operation terminated. Error in parameter 2. ERROR [22001] [Microsoft][ODBC Oracle Wire Protocol driver]String data, right truncated. Error in row 426. Error in parameter 2.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver]Error tolerance exceeded. Bulk load operation terminated. Error in parameter 2. ERROR [22001] [Microsoft][ODBC Oracle Wire Protocol driver]String data, right truncated. Error in row 426. Error in parameter 2.,Source=msora28.dll,'

Let me know what other logs i can get for you.

Rick

Answer 2

ok Thank you Bart,
We seemed to have had Microsoft resolve ours by not using the BulkLoad.
Not sure if you would have time to test this for yourself...
Thanks,
Rick

Answer 3

I found some similar cases, and there the solution is increasing the writeBatchSize 10,000 -> 50,000

adf-copy-activity-oracle-error-error-tolerance-bulk-load-vishwakarma
adf-copy-activity-to-oracle-error-error.html