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