Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Danie.

Asked: January 20, 2023 - 7:32 am UTC

Last updated: January 24, 2023 - 7:03 am UTC

Version: Database 19c Apex 19.2

Viewed 100+ times

You Asked

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;


and Chris said...

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.

Rating

  (1 rating)

Comments

Danie, January 24, 2023 - 5:22 am UTC

Thank you for the feedback. I have followed your advice and found that he embedded document seems to be causing the error. When I shorten the value, the error disappears. I do not understand this, because I have other similar pieces of JSON with much larger embedded files and they parse without issues.

Is there somewhere that I can upload or email you the sample JSON for you to have a look at. I tried LiveSQL but is did not allow me to upload.

Thank You

Connor McDonald
January 24, 2023 - 7:03 am UTC

mail it to asktom_us@oracle.com with the subject line "Q 9547123300346287192"

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.