How to serialize a very big query with json_arrayagg (json

WITH
    bigquery
    AS
        (SELECT level from dual connect by level<1000)
SELECT json_arrayagg (json_object (*))
  FROM bigquery

with this code, I can serialize the result of a query. But when the query is too big. It doesn't work anymore.

Ora-40478: output value too large (maximum:4000)

The problem come from json_arrayagg(json_object(*))

because this code works

WITH
    bigquery
    AS
        (SELECT level from dual connect by level<1000)
SELECT *
  FROM bigquery

db<>fiddle

CodePudding user response：

As described in the documentation, json_arrayagg returns varchar2(4000) if returning clause of this function is not specified.

JSON_agg_returning_clause

Use this clause to specify the data type of the character string returned by this function.

If you omit this clause, or if you specify VARCHAR2 but omit the size value, then JSON_ARRAYAGG returns a character string of type VARCHAR2(4000).

You need to provide clob as returning type.

WITH
    bigquery
    AS
        (SELECT level as l from dual connect by level<1000)
SELECT json_arrayagg (
  json_object (key 'l' value l)
  returning clob
  )
  FROM bigquery

db<>fiddle here