Insert complex XML data in to Oracle Databse Column of Blob data type using Oracle connecter in Logic App

Question

Hi,

I have requirement to insert XML data in column of a Oracle table. The data type of column is BLOB. The xml data of certain length is inserted without any issue. But few xml data are failing with Oracle Error ORA-01704 string literal too long. Could you please guide any way to insert the data in to Oracle?

Content Meta-data: (This is the output of Transform XML action after applying map. This content of it has to be inserted in to oracle column)

"headers": {
"Pragma": "no-cache",
"Cache-Control": "no-cache",
"Date": "Wed, 21 Apr 2021 07:41:04 GMT",
"Server": "Microsoft-IIS/10.0",
"X-Powered-By": "ASP.NET",
"Content-Length": "5055",
"Content-Type": "application/xml",
"Expires": "-1"
},
"body": {
"$content-type": "application/xml",
"$content": <Base64EncodedMsg>

Error From Oracle:

{
"statusCode": 400,
"headers": {
"Pragma": "no-cache",
"x-ms-request-id": "1078cb48-f319-411b-8cd9-9ab77222e200",
"Strict-Transport-Security": "max-age=31536000; includeSubDomains",
"X-Content-Type-Options": "nosniff",
"X-Frame-Options": "DENY",
"Timing-Allow-Origin": "*",
"x-ms-apihub-cached-response": "true",
"Cache-Control": "no-store, no-cache",
"Date": "Wed, 21 Apr 2021 07:41:10 GMT",
"Content-Length": "414",
"Content-Type": "application/json",
"Expires": "-1"
},
"body": {
"status": 400,
"message": "Oracle: ORA-01704: string literal too long\r\n inner exception: Oracle: ORA-01704: string literal too long\r\nclientRequestId: 107xxx-xxxx-41xx-8cd9-9ab7xxxxx200",
"error": {
"message": "Oracle: ORA-01704: string literal too long\r\n inner exception: Oracle: ORA-01704: string literal too long"
},
"source": "xxx.p.azurewebsites.net"
}

Answer 1

Hi @Anonymous - The error is coming from the Oracle ADO.NET driver that's provided by Oracle and is not something coming from the connector itself. There appear to be many Stackoverflow posts around the error code "Oracle: ORA-01704" in the context of BLOB type and how there may be a 4000 character limit that can be mitigated by using chunking. I would check for best practices for tackling this error in the Oracle community.