Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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 connect by level <= 100
101 rows affected

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;
/
ORA-40478: output value too large (maximum: 4000)
ORA-06512: at line 5

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) 
            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);
   -- just for fiddle show start as varchar
   dbms_output.put_line(dbms_lob.substr(l_json_object.to_clob, 1000, 1));
END;
/
1 rows affected

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