JSON_OBJECT_T error - The provided JavaScript Object Notation (JSON) operator generated a result which exceeds the maximum length

Question

I'm trying to get the data from the table with below PL/SQL but in my case i have some 30 records and when I fetch I'm getting below error. I need response in JSON_OBJECT_T format only but I want to avoid this error message.

40478. 00000 - "output value too large (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 or BLOB in the RETURNING clause.

DECLARE
  l_json_object JSON_OBJECT_T;
  l_json CLOB;
BEGIN
   SELECT
      JSON_OBJECT(
         KEY 'employees' VALUE 
            JSON_ARRAYAGG(
               JSON_OBJECT(KEY 'empno' VALUE id
                           ,KEY 'ename' VALUE name
                           ,KEY 'job' VALUE email_id)
               ORDER BY id) 
            RETURNING CLOB)
     INTO l_json
     FROM employee_tbl;
   -- now convert clob to JSON_OBJECT_T
   l_json_object := JSON_OBJECT_T.PARSE(l_json);
   -- or...this works too
   -- l_json_object := JSON_OBJECT_T(l_json);

   -- check if value is correct
   dbms_output.put_line(l_json_object.to_clob);
END;
/

Output expected:

{"employees":[{"empno":1065,"ename":"Abu","job":"email1@example.com"},{"empno":1066,"ename":"Umar","job":"email2@example.com"}]}

Table sample:

CREATE TABLE employee_tbl (id, name, email_id) AS
  SELECT 1065, 'Abu',  'email1@example.com' FROM DUAL UNION ALL
  SELECT 1066, 'Umar', 'email2@example.com' FROM DUAL

Answer 1

You need the array aggregation to produce a CLOB, as well as the final outer JSON object; so

               ORDER BY id)
            RETURNING CLOB)
     INTO l_json

becomes

               ORDER BY id
               RETURNING CLOB) 
            RETURNING CLOB)
     INTO l_json

making that whole query:

   SELECT
      JSON_OBJECT(
         KEY 'employees' VALUE 
            JSON_ARRAYAGG(
               JSON_OBJECT(KEY 'empno' VALUE id
                           ,KEY 'ename' VALUE name
                           ,KEY 'job' VALUE email_id)
               ORDER BY id
               RETURNING CLOB) 
            RETURNING CLOB)
     INTO l_json
     FROM employee_tbl;

fiddle with extended/repeated data to reproduce the initial error, and modified output so it displays part of the CLOB result.