1 Reply Latest reply on Apr 7, 2016 7:59 PM by Kris Rice-Oracle

    APEX_JSON.PARSE issue with large return


      Good morning,


      I am having issues with APEX_JSON.PARSE when the API I am POSTING to returns a large response to my CLOB.  Basically I send a string of XML in the JSON body and the API returns this back with lots of other messages.  If the XML I send is small I can parse the return and find the correct paths to get the response data I need and store it in some tables.  When this increases in size the POST of the JSON works (been into the API to confirm it receives the data correctly) but the code falls over when parsing the return into L_CLOB. 


      See example below. 


                                            l_clob := APEX_WEB_SERVICE.MAKE_REST_REQUEST(

                                                p_url                        => l_url,

                                                     p_http_method       =>  'POST',

                                                        p_body                        => apex_json.get_clob_output



                                            dbms_output.put_line('Length of response is : '|| dbms_lob.getlength(l_clob));


      This gets me "Length of response is : 86072"


      So when i post that JSON is gets to the API without issue but when I parse the return to get a simple status like below:


      apex_json.parse (

        p_values => l_values,

        p_source => l_clob



        dbms_output.put_line('Response from WebAPI is status : ' || apex_json.get_varchar2(

        p_values => l_values,

        p_path => 'status'




      Results in the error below if the JSON returned to l_clob is over 32767




      If i send a small XML string the exact same code as above can handle my responses without issue.





      "application": "APIDCTest",



      "server": "1358Test",



      "environment": "DEV",



      "version": "",



      "status": "success",






      This is something I will need to proceed with as its no good in me posting the JSON albeit successfully if i cant keep an audit log of what happens.


      I found a page on oracle with a bug similar to this but my database is version 12c and APEX version 5.0.2


      20974582 - apex_json.parse errors when parsing clobs if DB Version <

      If your database version is 11.1.x,, or 11gXE, apex_json.parse may report errors when parsing a clob.

      Solution: There is a patchset exception for this available on My Oracle Support - search by bug number 20931298. If using ORDS (Apex Listener), restart it after installing the patch.

      Workaround: If you have not installed the patch, then you can use the overloaded parse procedures which take varchar2 or table of varchar2 instead.

      Any help will be appreciated.