Re: Problem with JSON in Oracle

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