2 Replies Latest reply on Oct 30, 2019 2:50 PM by Holger.Lehmann

    JSON_VALUE returning varchar2 limits

    a4c59b02-d67c-4f2d-ae94-4790c77486df

      Hi there,

       

      I'm looking at the json_value function in Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 and am seeing discrepancies with the expected output when using json_value and strings that are larger than the expected returned varchar2 type.

      I have looked for similar issues but cannot find any, and cannot see anything in the documentation related to this either.

      I understand that the default return type is varchar2(4000) I would assume that any value larger than that should throw an error, but it seems to be dependent on both the size of the return type and the size of the full json_value.

       

      For example, in the code below I have a json_value function returning a string of length 5031 while using a returning clause of 1258 and 1257 respectively. I would expect both to error but instead only the second statement errors. This seems to be caused because the expected length is less than a quarter of the total length. (quarter of 5031 is 1257.75)

      To prove this again I update the length of the json value to 10032 and perform the same action with the return clause of 2508 and 2507 respectively and again you can see that the first will succeed while the second fails because the return type is less that a quarter of the total json_value length, but I would again have expected both to fail.

       

      Can you confirm if this is the expected outcome, of if there might be a limitation with the json_value function?

       

      Thanks,

      Mike

       

      declare
      l_clob clob := '{"longString": "This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long."}';
      
      
      begin
      --5031/4=1257.75
      dbms_output.put_line(length(json_value(l_clob, '$.longString' returning varchar2(1258) error on error)));
      begin
      dbms_output.put_line(length(json_value(l_clob, '$.longString' returning varchar2(1257) error on error)));
      exception when others then
      dbms_output.put_line(sqlerrm);
      end;
      
      
      l_clob := '{"longString": "This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long."}';
      --10032/4=2508
      dbms_output.put_line(length(json_value(l_clob, '$.longString' returning varchar2(2508) error on error)));
      begin
      dbms_output.put_line(length(json_value(l_clob, '$.longString' returning varchar2(2507) error on error)));
      exception when others then
      dbms_output.put_line(sqlerrm);
      end;
      
      
      l_clob := '{"longString": "8       "}';
      dbms_output.put_line(length(json_value(l_clob, '$.longString' returning varchar2(2) error on error)));
      begin
      dbms_output.put_line(length(json_value(l_clob, '$.longString' returning varchar2(1) error on error)));
      exception when others then
      dbms_output.put_line(sqlerrm);
      end;
      end;
      /
      

       

       

      Output from the block is

      5031
      ORA-40478: output value too large (maximum: )
      10032
      ORA-40478: output value too large (maximum: )
      8
      ORA-40478: output value too large (maximum: )
      
        • 1. Re: JSON_VALUE returning varchar2 limits
          Pierre Yotti

          I tested it on 19c Version

           

          test.png

           

           

          i have the errors in all case

           

          save.png

          • 2. Re: JSON_VALUE returning varchar2 limits
            Holger.Lehmann

            If you change it to

             

            DECLARE
               l_clob   CLOB
                  := '{"longString": "This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long."}';
            BEGIN
               --5031/4=1257.75
               DBMS_OUTPUT.put_line (LENGTH (json_value (l_clob, '$.longString' RETURNING VARCHAR2 (5031) ERROR ON ERROR)));
            
            
               BEGIN
                  DBMS_OUTPUT.put_line (LENGTH (json_value (l_clob, '$.longString' RETURNING VARCHAR2 (5031) ERROR ON ERROR)));
               EXCEPTION
                  WHEN OTHERS THEN
                     DBMS_OUTPUT.put_line (SQLERRM);
               END;
            
            
               l_clob   :=
                  '{"longString": "This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long."}';
               --10032/4=2508
               DBMS_OUTPUT.put_line (LENGTH (json_value (l_clob, '$.longString' RETURNING VARCHAR2 (10032) ERROR ON ERROR)));
            
            
               BEGIN
                  DBMS_OUTPUT.put_line (LENGTH (json_value (l_clob, '$.longString' RETURNING VARCHAR2 (10032) ERROR ON ERROR)));
               EXCEPTION
                  WHEN OTHERS THEN
                     DBMS_OUTPUT.put_line (SQLERRM);
               END;
            
            
               l_clob   := '{"longString": "8       "}';
               DBMS_OUTPUT.put_line (LENGTH (json_value (l_clob, '$.longString' RETURNING VARCHAR2 (8) ERROR ON ERROR)));
            
            
               BEGIN
                  DBMS_OUTPUT.put_line (LENGTH (json_value (l_clob, '$.longString' RETURNING VARCHAR2 (8) ERROR ON ERROR)));
               EXCEPTION
                  WHEN OTHERS THEN
                     DBMS_OUTPUT.put_line (SQLERRM);
               END;
            END;
            /
            

             

            it will work.