4 Replies Latest reply on Sep 15, 2017 7:00 PM by mdrake-Oracle

    JSON from SQL/JSON: How to circumvent ORA-40459: output value too large

    pm*349208*al

      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.