Dear jsoner,
I have a sql statement with nested json_object's and json_arrayagg. I often, quickly bump into
ORA-40459: output value too large (actual: 4178, maximum: 4000)
40459. 00000 - "output value too large (actual: %s, maximum: %s)"
*Cause: The provided JavaScript Object Notation (JSON) operator generated a
result which exceeds the maximum length specified in the RETURN
clause.
*Action: Increase the maximum size of the data type in the RETURNING clause
or use a CLOB/BLOB in the RETURNING clause.
The syntax is correct, because when I sometimes limit to 1 element, I get a proper JSON object.
First of all, why can't oracle just spit the JSON object whatever its size? Why is a limited varchar(4000) the limit? Is there a way to change this default returning type?
Second, I read about the returning clause, but I don't know where to place it. I tried so many combinations, trying to place it on the outermost json_object, or in a inner json_arrayagg, or both. I tried returning VACHAR2( 8000 ) and CLOB. But nothing worked. When I place the returning clause at the end of some json_object, the sql statement even fails with syntax error.
Can an inner json_object return a CLOB and will that returned CLOB be "merged" or concatenated with a "outer" CLOB?
Can someone knowledgeable please help me get this thing to work by placing the appropriate returning clauses?
Her is a typical sql statement: (an entity named provider_type has properties, and has a list of attribute attached to it; each attribute has a type. Nothing complicated: A master-detail with some intermediate tables.)
select json_object(
'name' value pt.name,
'display_name' value pt.display_name,
'attributes' value (select json_arrayagg(
json_object(
'attribute' value json_object(
'name' value a.name,
'display_name' value a.display_name,
'description' value a.description,
'type' value json_object(
'name' value at.name,
'display_name' value at.display_name,
'description' value at.description,
'data_type' value at.data_type
)),
'mandatory' value ea.mandatory,
'default_value' value ea.default_value,
'position' value ea.position,
'array_index' value ea.array_index))
from entity_attributes ea,
attributes a,
attribute_types at
where ea.entity_id = pt.id
and ea.entity_type = 'PROVIDER_TYPE'
and ea.attribute_id = a.id
and a.attribute_type_id = at.id
)
) from provider_types pt
Many thanks in advance.
P.