Re: Problem with JSON in Oracle

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.'
>

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