ORDS: Removing escaping char from json in ORDS OUT param

352 Views Asked by At

I am using the OUT parameter in the ORDS parameters to send a response which is already in JSON and this stored as CLOB in the DB. When I send this out in the response from the ORDS, ORDS is actually adding lot of back spaces to the response. Can someone help me to understand how I can remove the escaping chars here(all the backslashes). I had tried a different approach explained by Jeff in another thread to try to use an alias for the JSON key, but it did not work for me. Over here I have mentioned the response as OUT parameter in my code. eg payload:

{
    "response": "{\n\"Order\":{\n\"OriginalShipmentID\":1\n,\"orderNumber\":1\n,\"orgID\":1\n,\"orderShipmentNumber\":1\n,\"oracleShipSet\":\"1\"\n,\"OeHeaderId\":1\n,\"GsHeaderId\":1\n,\"CustomerPoNumber\":\"1\"\n,\"PaymentTerms\":1\n,\"FreightTerms\":\"PAID\"\n,\"CurrencyCode\":\"USD\"\n,\"BillToSiteUseID\":1\n,\"ShipToSiteUseID\":1\n,\"SalesChannel\":\"DIRECT\"\n,\"HeaderKeyCode\":\"1 1\"\n,\"OrigSysDocumentRef\":\"1\"\n,\"OrderTypeCode\":\"XYZ yXYZ\"\n,\"OrderTypeID\":1\n,\"SalesRepID\":-3\n,\"IsAtgOrder\":\"N\"\n,\"OrderSource\":\"MCP\"\n,\"OdiDocSet\":\"PO\"\n,\"CsServiceSymbol\":\"XYZ\"\n,\"CsServiceFriendlyName\":\"XYZ\"\n,\"CsShipper\":\"NDC_MX\"\n,\"GsShipToAddressID\":1\n,\"GsBillFrtAddressID\":-1\n,\"BillingMethod\":\"DoNotBillFreight\"\n,\"ReasonForNoInvoice\":\"NA\"\n,\"ShippedBy\":\"NA\"\n,\"ManifestID\":1771748\n,\"IsVoided\":\"N\"\n,\"CustomerNumber\":\"1\"\n,\"OrderLines\":[\n]\n,\"HeaderShortNotes\":{\n}\n}\n}\n"
}

Some more details:

SELECT get_json ( :ordernumber,
                  :warehouseid,
                  :shipset,
                  :ordershipmentno,
                  :gsshipmentid,
                  :countrycode,
                  :shipperid,
                  :fromcurrency,
                  :tocurrency,
                  :groupid,
                  :optionvalues,
                  :linehaul,
                  :servicename,
                  :sigid,
                  :lineid,
                  :customerid) "{}Order"
          INTO l_response_clob                                   
          FROM DUAL;

This function returns a CLOB with a JSON format data and l_response_clob is defined as STRING output parameter on the ORDS. Essentially, I want to stop ORDS from again converting JSON to JSON. Been banging head over this from some time but can't seem to make it work. Thanks for the help I can get here.

0

There are 0 best solutions below