Tuesday, May 12, 2015

APEX_JSON.PARSE Issue with CLOBs and 11g

Update: this issue is documented on the APEX 5 Known Issues page (bug 20974582) and has a patch for users with access to MOS (bug 20931298).

APEX 5.0 comes with some new APIs including APEX_JSON which manages JSON files.

When using APEX_JSON to consume a JSON file, the process is fairly straight forward:
- Parse: This parses the JSON and stores it in a record type.
- Read values in the JSON object by providing the path to each name/value pair.

The APEX_JSON documentation has some great examples which covers the above process in more details.

There is one "bug" that I found with APEX_JSON.PARSE when passing in a CLOB that has more than 8191 characters.  The reason is APEX_JSON.PARSE uses DBMS_LOB.SUBSTR behind the scenes which has a known bug in 11g R1, 11g R2, and 11g XE. In 12c onwards, this is not an issue. An example of the error that is raised is:

ORA-20987: Error at line 626, col19: Expected ":", seeing "<varchar2>"

ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 292
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 560
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 756
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 774
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 624
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 719
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 615
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 758
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 774
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 811
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 887
ORA-06512: at line 24

The APEX_JSON.PARSE procedure currently has two supported overloaded methods. One which takes in a varchar2, and the other which takes in a CLOB. Thankfully there's a third (undocumented) method which takes in a table of varchar2. Note: in future versions of APEX, this may be a supported procedure.

Here's a sample snippet on how to convert your CLOB to a varchar2 table and then call APEX_JSON.PARSE:
  l_clob clob;
  l_json apex_json.t_values;

  l_temp varchar2(1000);

  l_clob_tab apex_application_global.vc_arr2;


  l_clob := 'load json data here';

  -- Convert clobtotable
    c_max_vc2_size pls_integer := 8100; -- Bug with dbms_lob.substr 8191

    l_offset pls_integer := 1;
    l_clob_length pls_integer;
    l_clob_length := dbms_lob.getlength(l_clob);

    while l_offset <= l_clob_length loop
      l_clob_tab(l_clob_tab.count + 1) :=
        dbms_lob.substr (
         lob_loc => l_clob,
         amount => least(c_max_vc2_size, l_clob_length - l_offset +1 ),
         offset => l_offset);

      l_offset := l_offset + c_max_vc2_size;
    end loop;

  -- Parse clob as JSON
    p_values => l_json,
    p_source => l_clob_tab,
    p_strict => true);

  -- You can now use the JSON object for l_json
Note: the code to convert the CLOB to the table will be available soon as part of the new OraOpenSource OOS_UTILS project.

Thanks to Christian Neumüller for helping resolve this issue.


  1. Good writeup. Thanks for explaining that. You just saved me a bunch of heartache.

    One note: shouldn't the first argument to DBMS_LOB.SUBSTR be lob_loc => l_clob rather than files.clob_content? Looks like maybe a cut-and-paste code relic there, or else some clob magic going on that I'm not seeing.

    Anyway, thanks, very helpful.

    1. You're right, it was a copy & paste from some other code I was working on. I have updated it. Thanks for pointing it out.

  2. Hi Martin,

    I tried to use your solution but it didnt work. I have a very big json because it has base64 images and I cant parse it using apex_json.parse, shows the same error.

    I'm also trying PL/JSON (https://github.com/pljson/pljson) but for a json with 3 base64 photos it takes over 5 minutes to complete, its very slow.

    Any ideas?

    Thank you

  3. This bug seems to be happening on my 12c install.

    My database version is Oracle Database 12c Enterprise Edition Release - 64bit Production & we are running APEX 5.0.2.

    I will have a look at your fix this afternoon and report back.


  4. No we have not upgraded as I may have been a bit hasty in my post so apologies. I am receiving a similar issue to this but not the exact same error.

    Oracle seems to have fixed the bug mention above but the limit for the APEX_JSON.PARSE seems to be now at 32767 characters. We sometimes receive a response from an API which is tens of thousands of characters long as they post back what we posted them (large XML strings) as some sort of confirmation. The parser just fails when this is over 32767 but works correctly when under it. I posted on the community my issue here https://community.oracle.com/message/13722591#13722591 to see if anyone had any solutions.

    Thanks for the response.

    1. Hmmmm if you have 12c you may be able to parse using its native capabilities. I've used node.js in the past to help process large amounts of JSON data and then pass back into Oracle.