Good morning all and thanks for any help in advance.
I have the task of liaising with a 3rd party API via JSON to post some information from our database. First disclaimer is the actual HOST for us is not up and running so I have no way to properly test this until tomorrow but I wanted to ask this question to try to overcome a potential problem.
Within the JSON object body we need to add a big piece of xml (already created/stored and/or can be created on fly) and it has to be posted out as a long string. Now this issue is that the string can be up to 20000 chars in length so when its processed it is stored as a CLOB in the database (not done by me).
Now my initial thoughts to do this was to create a procedure which allows some parameters to get the relevant piece of data and its xml stored as a CLOB. Then and this is where I may need some advice, do the following.
This host requires JSON in this format
"XML String":"THIS IS GOING TO BE A MASSIVE XML STRING",
We currently have a few apex applications so I have had a good look at the documentation and can generate the required JSON using:
So collect information from parameters inputting
APEX_JSON.WRITE(‘batchcode,”Value from database”);
APEX_JSON.WRITE(‘XML String' , our xml string from database which is stored as a CLOB);
APEX_JSON.WRITE(regioncode ' , ,”Value from database”);
APEX_JSON.WRITE(‘ID’ ,"Value from database");
Then post to endpoint using:
p_url => “end point URL”,
p_http_method => 'POST',
p_username => API_USER,
p_password => API_KEY,
p_body => apex_json.get_clob_output
Now assuming my method is okay does the APEX_JSON.WRITE literally accept its input as a CLOB type and will it know to parse it correctly or will I need to come up with a manor that will actually write out the string itself?
Edit I have found that if i do APEX_JSON.WRITE('XML',"CLOB column from table") that it does output the CLOB but not in the format i desire. This is due to the fact my CLOB has characters like < > etc within it & the output is writing them out in unicode so for example <?xml version="1.0" encoding="UTF-8"?> as stored in the CLOB becomes \u003C?xml version=\"1.0\" encoding=\"UTF-8\"?\u003E\n when outputting using APEX_JSON.WRITE.
The only other way I can think of doing it is to split this out into chunks like so:
SELECT ROWNUM as XML_PIECE_NO,TO_CHAR(SUBSTR (a.xmldoc, (ROWNUM-1)*4000, 4000)) AS SECTION
FROM ast_xmldata a
CONNECT BY (ROWNUM-1)*4000 <= LENGTH(a.xmldoc)
Then maybe concat these into the JSON field as a long xml string.
Hopefully I have explained what I am looking to do but if not please fire back Any help will be greatly appreciated.
Message was edited by: K4E
EDIT I HAVE SINCE COME UP WITH A SOLUTION
Message was edited by: K4E - Found solution 04-02-2016