Re: Problem with JSON in Oracle

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