Re: Problem with JSON in Oracle

Just an aside: in general, CLOB is easier to work with than BLOB.

If they were using Varchar2, then CLOB may be perfectly acceptable.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Principal Consultant at Pythian
Oracle ACE Alumni
Pythian Blog http://www.pythian.com/blog/author/still/ Github: https://github.com/jkstill

On Fri, Mar 26, 2021 at 1:02 PM Kim Berg Hansen <kibeha_at_gmail.com> wrote:

> Okay, I found that. Yes, the max return-value length of SQL/JSON functions.
> I'll have to interpret that as the return value of JSON functions that
> retrieve individual elements of JSON.
>
> Because also in the JSON docs are written:
>
> Use BLOB (binary large object) or CLOB (character large object) storage
> if you know that you have some JSON documents that are larger than 32767
> bytes (or characters)Foot 1
> <https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/overview-of-storage-and-management-of-JSON-data.html#fnsrc_d6187e110>
> .
>
>
> https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/overview-of-storage-and-management-of-JSON-data.html#GUID-26AB85D2-3277-451B-BFAA-9DD45355FCC7
>
> It looks to me that it is definitely *meant *to be able to handle larger
> than 32K.
>
> I think I'll try and test it tomorrow. I feel it should be possible :-)
>
> If you're on Twitter, try to tweet to Beda Hammerschmidt:
> https://twitter.com/bch_t
> He'll be able to answer for certain.
>
> Cheerio
> /Kim
>
>
>
>
> On Fri, Mar 26, 2021 at 8:43 PM Andrew Kerber <andrew.kerber_at_gmail.com>
> wrote:
>
>> ITs in the oracle json developers guide.
>>
>> -
>>
>> General
>> -
>>
>> Number of nesting levels for a JSON object or array: 1000,
>> maximum.
>> -
>>
>> JSON field name length: 32767 bytes, maximum.
>> -
>>
>> SQL/JSON functions
>> -
>>
>> Return-value length: 32767 bytes, maximum.
>> -
>>
>> Path length: 4K bytes, maximum.
>> -
>>
>> Number of path steps: 65535, maximum.
>> -
>>
>> Simplified JSON syntax
>> -
>>
>> Path length: 4K bytes, maximum.
>> -
>>
>> Path component length: 128 bytes, maximum.
>> -
>>
>> JSON search index
>> -
>>
>> Field name length: 255 bytes, maximum. If a document has a field
>> name longer than 255 bytes then it might not be completely indexed. In that
>> case, an error is recorded in database view CTX_USER_INDEX_ERRORS.
>>
>>
>> On Fri, Mar 26, 2021 at 2:29 PM Kim Berg Hansen <kibeha_at_gmail.com> wrote:
>>
>>> Is it necessary for them to parse the JSON CLOBs into JSON object types?
>>> Could they potentially be better off using the native SQL functions like
>>> JSON_TABLE and JSON_QUERY to retrieve data from the JSON values?
>>>
>>> Having said that, JSON_ELEMENT_T.PARSE is an overloaded function
>>> accepting both VARCHAR2, CLOB and BLOB.
>>> Are they passing the CLOB directly to PARSE, or could they somehow be
>>> hitting an implicit conversion that makes the code choose the VARCHAR2
>>> version of PARSE?
>>>
>>> Where do you see the 32k limit in the docs? (It's probably there, I just
>>> can't find it at the moment ;-)
>>>
>>>
>>> Cheerio
>>> /Kim
>>>
>>>
>>> Regards
>>>
>>>
>>> Kim Berg Hansen
>>> Senior Consultant at Trivadis
>>> Oracle ACE Director
>>>
>>> Author of Practical Oracle SQL
>>> <https://www.apress.com/gp/book/9781484256169>
>>> http://www.kibeha.dk
>>> kibeha_at_kibeha.dk
>>> _at_kibeha <http://twitter.com/kibeha>
>>>
>>>
>>> On Fri, Mar 26, 2021 at 8:06 PM Andrew Kerber <andrew.kerber_at_gmail.com>
>>> wrote:
>>>
>>>> I really dont know much at all about JSON, though we have a group that
>>>> is using that format to store data in CLOBs in the database. They are
>>>> running into a problem that seems to be a hard oracle limit.
>>>>
>>>> They have a bunch of JSON CLOBs larger than 32k. I havent asked them
>>>> how they created them, But anyway, they are using a stored procedure called
>>>> JSON_ELEMENT_T.PARSE. And they are getting a parse error whenever they try
>>>> to parse a json document larger than 32k. Looking at the documentation for
>>>> JSON, its pretty clear they are hitting a hard string length limit of 32k.
>>>>
>>>> They tried to tell me its an Oracle bug, but reading through the
>>>> documentation, I can see that this 32k string size limit is pretty well
>>>> documented. In any case, does anyone know of a way around this? My
>>>> suggestion was to pull out the JSON in 32k chunks and assemble it in a
>>>> language that can deal with JSON larger than 32k.
>>>>
>>>> --
>>>> Andrew W. Kerber
>>>>
>>>> 'If at first you dont succeed, dont take up skydiving.'
>>>>
>>>
>>
>> --
>> Andrew W. Kerber
>>
>> 'If at first you dont succeed, dont take up skydiving.'
>>
>

--
http://www.freelists.org/webpage/oracle-l