Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Muhammad.

Asked: August 08, 2018 - 9:03 pm UTC

Last updated: August 09, 2018 - 4:06 pm UTC

Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

Viewed 10K+ times! This question is

You Asked

I am trying to use JSON functions like JSON_OBJECT and JSON_ARRAYAGG to generate a JSON string through SQL. We have alot of columns in our tables that have alot more data than 4000 bytes which I am trying to parse through and generate JSON string. Everytime I run it I get the following error: "ORA-40459: output value too large (actual: 4044, maximum: 4000)". I am trying to figure out how to parse large amount of data into a JSON string.

SELECT JSON_OBJECT('rows' VALUE JSON_ARRAYAGG (JSON_OBJECT('id' VALUE ASSET.asset_id , 'data' VALUE JSON_ARRAY(
COLUMN 1,
COLUMN 2,
COLUMN 3,
COLUMN 4,
COLUMN 5,
COLUMN 6))))

FROM TEST_TABLE;

Is there another solution to generating JSON from large amount of data natively supported in Oracle?

and Chris said...

The JSON generation functions in 12.2 have limited data type support.

You can return a clob from json_arrayagg:

select length ( json_arrayagg ( object_name ) )
from   all_objects;

ORA-40459: output value too large (actual: 4004, maximum: 4000)

select length ( json_arrayagg ( object_name returning clob ) )
from   all_objects;

LENGTH(JSON_ARRAYAGG(OBJECT_NAMERETURNINGCLOB))   
                                          2018592 


But not json_object:

select json_object ( 
         'objects' value json_arrayagg ( object_name returning clob ) 
         returning clob
       )
from   all_objects;

SQL Error: ORA-40449: invalid data type for return value


18c fixes these issues.

In the meantime you can return larger documents than 4,000 bytes by using extended data types:

- First apply <Patch 27521093: MERGE REQUEST ON TOP OF 12.2.0.1.0 FOR BUGS 24693010 25481087>
- Enable extended data types
- Set the returning clause of the json functions to varchar2(32767):

select length ( json_arrayagg ( object_name returning varchar2(32767) ) )
from   all_objects
where  rownum <= 1000;

LENGTH(JSON_ARRAYAGG(OBJECT_NAMERETURNINGVARCHAR2(32767)))   
                                                       17316


MOS note 2354511.1 includes more details.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

JSON Function Input Size Limitations

Muhammad, August 09, 2018 - 3:24 pm UTC

Thank you for your response, it was really helpful. I do have some more questions. As I was reading online about these JSON functions, I read on one of your posts that the JSON_ARRAYAGG functions currently can only accept 4000 characters. Would this be an issue since I am trying to pass in a JSON_OBJECT that contains a huge JSON_ARRAY list of values. Can you please clarify if there input size limitation to these functions?

Thank you,
Chris Saxon
August 09, 2018 - 3:51 pm UTC

Yes, the inputs are limited too. Again, this restriction is lifted in 18c.

A reader, August 09, 2018 - 3:55 pm UTC

Currently we don't have the option to move to 18c, so I was trying to see if I can have size of data that is bigger than 4000 that gets passed into JSON_OBJECT function. Is there a way to bypass these input limitations because it seems like even if I can have extended data types enabled I still cannot use it as input into JSON_OBJECT function.
Chris Saxon
August 09, 2018 - 4:06 pm UTC

They should support extended data types (up to 32,767).

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.