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
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>"
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
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 := dbms_lob.getlength(l_clob);
while l_offset <= l_clob_length loop
l_clob_tab(l_clob_tab.count + 1) :=
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;
-- 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.