6 Replies Latest reply on Mar 5, 2018 1:03 PM by Prodipto Tokder

    Retrieve values with json_value that are larger than varchar2(4000)?

    roryg18

      I've searched everywhere for an answer on this but can't find anything, it seems like a pretty big issue if it is a bug so maybe I'm missing something.

       

      I'm using;

      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

       

      And ORDS is;

      3.0.9.348.07.16

       

      When I try to extract out a value from a JSON string that is larger than 4000 chars it returns null e.g.

      select json_value('{

      "string": "string",

      "longString": "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb",

      "number": 1,

      "boolean": true

      }', '$.longString') value_found from dual;

       

      This returns null, I know this is because 'RETURNING' defaults to 4000. So I increase it by replacing the last line with;

      }', '$.longString' RETURNING VARCHAR2(32767)) value_found from dual;

       

      Which gives me;

      ORA-00910: specified length too long for its datatype

      00910. 00000 -  "specified length too long for its datatype"

      *Cause:    for datatypes CHAR and RAW, the length specified was > 2000;

                 otherwise, the length specified was > 4000.

      *Action:   use a shorter length or switch to a datatype permitting a

                 longer length such as a VARCHAR2, LONG CHAR, or LONG RAW

      Error at Line: 6 Column: 35

       

      When I replace the 'RETURNING' value with verbose error messaging;

      }', '$.longString' ERROR ON ERROR) value_found from dual;

       

      I get this;

      ORA-40441: JSON syntax error

      40441. 00000 -  "JSON syntax error"

      *Cause:    The provided JavaScript Object Notation (JSON) data had invalid

                 syntax and could not be parsed.

      *Action:   Provide JSON data with the correct syntax.

       

      The weird thing is that I can't even select other values;

      }', '$.number') value_found from dual;

       

      I get the exact same errors.

       

      As soon as I shorten the 'longString' value to a few hundred characters shorter it works, for all values.

       

      I'm following the guidelines here;

      https://docs.oracle.com/database/121/SQLRF/functions093.htm#SQLRF56668

       

      If this post would be better suited in any of the following please let me know;

      https://community.oracle.com/community/database/developer-tools/application_express

      https://community.oracle.com/community/database/developer-tools/sql_and_pl_sql

       

      Any help, insights would be greatly appreciated.

        • 1. Re: Retrieve values with json_value that are larger than varchar2(4000)?
          MaxOrgiyan-Oracle

          It sounds like you simply don't have extended data types enabled:

           

          https://oracle-base.com/articles/12c/extended-data-types-12cR1

           

          By default VARCHAR2 is limited to 4K. To get up to 32K for varchar2,

          you need to enable extended data types.

           

          If you need to go beyond 32K, I am not sure if there's a way to do

          that on 12.1.0.2 (the latest release, 18.1, has 'returning clob' option

          for the returning clause, which would allow you to do that). I forwarded

          your question to Beda, our lead JSON guy.

          • 2. Re: Retrieve values with json_value that are larger than varchar2(4000)?
            Gaz in Oz

            Here's a workaround for 12.1.0.2 and 12.2.0.1:

            WITH j (json_str) AS (
               select TO_CLOB('{"string": "string", ')||
                      TO_CLOB(' "longString": "')     ||
                      TO_CLOB(rpad('b', 3999, 'b'))   ||
                      TO_CLOB(rpad('x',   81, 'x'))   ||
                      TO_CLOB('", "number": 1, ')     ||
                      TO_CLOB('"boolean": true }')
               from   dual
            )
            SELECT length(j.json_str)                                                  len,
                   json_value(j.json_str, '$.string')                                  str,
                -- json_value(j.json_str, '$.longString') /* VARCHAR2() max limit */   var,
                   REGEXP_SUBSTR(j.json_str, '("longString": )(.+)(",)', 1, 1, 'i', 2) lob,
                   json_value(j.json_str, '$.number')                                  num,
                   json_value(j.json_str, '$.boolean')                                 bool
            FROM   j j
            /
                   LEN
            ----------
            STR
            --------------------------------------------------------------------------------
            LOB
            --------------------------------------------------------------------------------
            NUM
            --------------------------------------------------------------------------------
            BOOL
            --------------------------------------------------------------------------------
                  4150
            string
            bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
            bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
            ...
            bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbx
            xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
            1
            true
            
            

            Note: lines 01 to 09, the WITH() clause, is just to mimic a json doc stored in a CLOB, so we can produce a json doc > 4000 chars to test.

            REGEXP_SUBSTR()

            • 3. Re: Retrieve values with json_value that are larger than varchar2(4000)?
              MaxOrgiyan-Oracle

              Note that it's always best to rely on JSON operators when processing

              JSON in Oracle, as opposed to regexp. So if under 32k is sufficient for op,

              best option is to enable extended data types and use json_value.

               

              Multiple reasons for this:

               

              * Path could be a lot more complicated than the simple single step

              in this example

               

              * JSON operators are specifically designed for quickly processing JSON

               

              etc.

              • 4. Re: Retrieve values with json_value that are larger than varchar2(4000)?
                Gaz in Oz

                As stated, this is a workaround for the VARCHAR2(n) limit imposed in 12.1.0.2 and 12.2.0.1.

                • 5. Re: Retrieve values with json_value that are larger than varchar2(4000)?
                  roryg18

                  Thank you Gaz in Oz and MaxOrgiyan-Oracle for your responses.

                   

                  The workaround solution is nice, however, something I should have mentioned before is that this needs to be dynamic i.e. I won't be generating the json string myself, it will be coming from a web request.

                  Also, I couldn't guarantee the format of the JSON. Might be valid but break the regex e.g. {"string": "string"   , "string2": ", string"} wouldn't work.

                   

                  I will look into the extended data types solution, as it mentions that the process is not reversible and I'm not sure about what other implications it may have.

                   

                  Thanks Again.

                  • 6. Re: Retrieve values with json_value that are larger than varchar2(4000)?
                    Prodipto Tokder

                    Hi

                     

                    Can you give a sample of your JSON data.

                     

                    Thanks

                    PT