Hi Tom,
We are preparing request in database as JSON.
v_http_request CLOB;
v_req_body CLOB;
v_http_request := '{"url":"' || www.oracle.com||
'","message_id":"' || 1234||
'","header":"' ||Content-Type#text/xml; charset=UTF-8;Content-Length#872 ||
'","body_char_set":"' || UTF-8 ||
'","timeout":"' || 60 ||
'","proxy_url":"' || null||
'","http_method":"' || POST ||
'","http_version":"' || 1.1 ||
'","http_req_body":"' || v_req_body||
'"}';And req_body contains XML message.
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header/>
<soapenv:Body>
.....
</soapenv:Body>
</soapenv:Envelope>The problem we are facing is req body is XML and it has "" in between and JSON parser is giving issues.
And there is not method we see that to convert or apply encode and decode on CLOB column.
And q'[req_body] -- quotation is working only for 32767 characters.
Can you please advise how to handle this scenario.
Thank You
You could try:
- Converting problematic characters to their HTML code (e.g. double-quotes => ") OR
- base64 encoding the XML document
e.g.
set define off
declare
jdoc varchar2(4000);
xdoc xmltype;
begin
xdoc := xmltype ( q'!<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header/>
<soapenv:Body>
</soapenv:Body>
</soapenv:Envelope>!' );
jdoc :=
' { "url " : "www.oracle.com" , "message_id" : 1234, "xml":"' ||
replace (
replace (
replace (
replace (
xdoc.getStringVal(),
'"', '"'
), '/', '/'
), '<', '<'
), '>', '>'
) || '" }';
dbms_output.put_line ( jdoc );
jdoc :=
' { "url " : "www.oracle.com" , "message_id" : 1234, "xml":"' ||
utl_raw.cast_to_varchar2 (
utl_encode.base64_encode (
utl_raw.cast_to_raw (
xdoc.getStringVal()
)
)
) || '", "encoding" : "base64" }';
dbms_output.put_line ( jdoc );
end;
/
{ "url " : "www.oracle.com" , "message_id" : 1234, "xml":"<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header/>
<soapenv:Body>
</soapenv:Body>
</soapenv:Envelope>" }
{ "url " : "www.oracle.com" , "message_id" : 1234, "xml":"PHNvYXBlbnY6RW52ZWxvcGUgeG1sbnM6c29hcGVudj0iaHR0cDovL3NjaGVtYXMu
eG1sc29hcC5vcmcvc29hcC9lbnZlbG9wZS8iPgogIDxzb2FwZW52OkhlYWRlci8+
CiAgPHNvYXBlbnY6Qm9keT4KICA8L3NvYXBlbnY6Qm9keT4KPC9zb2FwZW52OkVu
dmVsb3BlPg==", "encoding" : "base64" }