Reading large data from table & sending output in json

database is 19C

I'm trying to read 10k+ records from Oracle table[with 2 columns in this case], convert records to JSON and return the result via procedure in CLOB. e.g. table CODES_CLASS, columns FCODES varchar2, CLASSFL varchar2

I tried with,

1. json_object ('FCODES' VALUE FCODES,'CLASSFL' VALUE CLASSFL FORMAT JSON) FROM table_name & then tried looping in for 10K+ records
2. SELECT json_arrayagg (json_object (*) RETURNING CLOB) AS json_doc FROM table_name & then tried looping in for 10K+ records
3. traditional APEX_JSON.open_array/write/close_array/close_object methods & then looping.

In 3, getting Error - ORA-06502: PL/SQL: numeric or value error. In 1,2 somewhere I'm going wrong with looping along with bulk collect OR if succeeds, JSON is not rightly getting generated - giving/shows error while formatting. :-|

Following JSON structure I'm trying for,

{
   "CODECLASS":[
      {
         "FCODES":"RDMT_179-65-600",
         "CLASSFL":"Fabric Card/Controller/RP"
      },
      {
         "FCODES":"RDMT_180-65-600",
         "CLASSFL":"Line Card/Controller/RP"
      },
      {
         "FCODES":"RDMT_182-65-600",
         "CLASSFL":"Controller/RP"
      },
      {
         "..":"..",
         "..":".."
      }
.
.
.

   ]
}

Kind request to advice. Thank you!

You can use JSON_OBJECT and JSON_ARRAYAGG:

SELECT JSON_OBJECT(
         KEY 'CODECLASS' VALUE JSON_ARRAYAGG(
           JSON_OBJECT(
             KEY 'FCODES' VALUE fcodes,
             KEY 'CLASSFL' VALUE classfl
           )
           RETURNING CLOB
         )
         RETURNING CLOB
       ) AS json
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (fcodes, classfl) AS
SELECT 'RDMT_' || LEVEL || '-65-600',
       CASE FLOOR(DBMS_RANDOM.VALUE(1,4))
       WHEN 1 THEN 'Fabric Card/Controller/RP'
       WHEN 2 THEN 'Line Card/Controller/RP'
       WHEN 3 THEN 'Controller/RP'
       END
FROM   DUAL CONNECT BY LEVEL <= 100;

Outputs:

(Output is abbreviated to not make the answer too large, the actual output includes all the values.)

How to output the select query in a CLOB OUT parameter of a procedure?

Use SELECT ... INTO the parameter:

CREATE PROCEDURE get_codeclasses(
  o_json OUT CLOB
)
IS
BEGIN
  SELECT JSON_OBJECT(
           KEY 'CODECLASS' VALUE JSON_ARRAYAGG(
             JSON_OBJECT(
               KEY 'FCODES' VALUE fcodes,
               KEY 'CLASSFL' VALUE classfl
             )
             RETURNING CLOB
           )
           RETURNING CLOB
         )
  INTO   o_json
  FROM   table_name;  
END;
/

fiddle