I am busy writing code to parse JSON data received in a CLOB via REST request. I successfully parsed all the JSON received except one piece of JSON that gives the error below, and I cannot figure out why as the content is basically the same as the other JSON I am processing. The code raising errors is seeded and also obfuscated so I cannot debug.
I have checked the piece of JSON with multiple online validators and it is valid. I have also recoded the embedded base64 encoded document and it is also valid.
The piece of JSON is 297120 bytes long mainly due to the embedded file. Most of the other pieces of JSON UP TO 6674299 bytes in length with multiple embedded documents parsed fine without issues.
Can you please steer me in the right direction as to what I can look for or try to find the cause of the error? Are there things in the JSON I can look for, or can this be a coding issue? Any help would be appreciated.
Thank You
Here is the code I am executing and the error:
DECLARE
p_json CLOB;
l_values apex_json.t_values;
BEGIN
SELECT json_text
INTO p_json
FROM xx_json_test
WHERE test_no = 11;
apex_json.parse(l_values,p_json);
END;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APEX_190200.WWV_FLOW_JSON", line 1156
ORA-06512: at "APEX_190200.WWV_FLOW_JSON", line 723
ORA-06512: at "APEX_190200.WWV_FLOW_JSON", line 1096
ORA-06512: at "APEX_190200.WWV_FLOW_JSON", line 891
ORA-06512: at "APEX_190200.WWV_FLOW_JSON", line 1096
ORA-06512: at "APEX_190200.WWV_FLOW_JSON", line 1138
ORA-06512: at "APEX_190200.WWV_FLOW_JSON", line 1215
ORA-06512: at line 14
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.I have also tried to use the overloaded parse method with a varchar table, but it gave the exact same result:
DECLARE
p_json CLOB;
l_values apex_json.t_values;
l_clob_tab apex_application_global.vc_arr2;
BEGIN
SELECT json_text
INTO p_json
FROM xx_json_test
WHERE test_no = 11;
/* Convert clob to table */
DECLARE
c_max_vc2_size pls_integer := 8100;
l_offset pls_integer := 1;
l_clob_length pls_integer;
BEGIN
l_clob_length := dbms_lob.getlength(p_json);
WHILE l_offset <= l_clob_length LOOP
l_clob_tab(l_clob_tab.count + 1) :=
dbms_lob.substr (lob_loc => p_json,
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;
END;
apex_json.parse(p_values => l_values,
p_source => l_clob_tab,
p_strict => true);
END;
It's hard to say what the issue is here without seeing an example JSON document!
As with general debugging when the problem is hard to spot, remove attributes from the document until you find which is causing the error.
That said, the error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
means something somewhere is trying to assign a string that's too big to fit into the target variable. So I'd start by inspecting the JSON for attributes that have long names or values.
Also: the current version of APEX is 22.2; as you're on 19.2 it's worth checking to see if this a known problem that's been fixed.