2 Replies Latest reply on Feb 4, 2016 10:27 AM by K4E

    Advice on using APEX_JSON with a CLOB in body as a string

    K4E

      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

       

      {

           "batchcode": 1234562,

           "XML String":"THIS IS GOING TO BE A MASSIVE XML STRING",

           "regioncode":"",

           "ID":"123456465",

           "field1": ""

      .

      .

      etc

        }

       

      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.INITIALIZE_CLOB_OUTPUT;

      APEX_JSON.OPEN_OBJECT();

        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");

        .

      .

      .

      etc.

       

        APEX_JSON.CLOSE_OBJECT();

       

      Then post to endpoint using:

       

      APEX_WEB_SERVICE.MAKE_REST_REQUEST(

            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.

       

      Regards

       

      Message was edited by: K4E

       

      EDIT I HAVE SINCE COME UP WITH A SOLUTION

       

      Message was edited by: K4E - Found solution 04-02-2016