By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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;
100 rows affected
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;
| JSON |
|---|
| {"CODECLASS":[{"FCODES":"RDMT_1-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_2-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_3-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_4-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_5-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_6-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_7-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_8-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_9-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_10-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_11-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_12-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_13-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_14-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_15-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_16-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_17-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_18-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_19-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_20-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_21-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_22-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_23-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_24-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_25-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_26-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_27-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_28-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_29-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_30-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_31-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_32-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_33-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_34-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_35-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_36-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_37-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_38-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_39-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_40-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_41-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_42-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_43-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_44-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_45-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_46-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_47-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_48-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_49-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_50-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_51-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_52-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_53-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_54-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_55-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_56-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_57-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_58-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_59-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_60-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_61-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_62-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_63-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_64-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_65-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_66-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_67-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_68-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_69-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_70-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_71-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_72-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_73-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_74-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_75-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_76-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_77-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_78-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_79-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_80-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_81-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_82-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_83-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_84-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_85-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_86-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_87-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_88-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_89-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_90-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_91-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_92-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_93-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_94-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_95-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_96-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_97-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_98-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_99-65-600","CLASSFL":"Controller/RP"},{"FCODES":"RDMT_100-65-600","CLASSFL":"Controller/RP"}]} |
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;
/
DECLARE
v_clob CLOB;
BEGIN
DBMS_OUTPUT.ENABLE();
get_codeclasses(v_clob);
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_clob, 1, 400) || '...');
END;
/
1 rows affected
dbms_output:
{"CODECLASS":[{"FCODES":"RDMT_1-65-600","CLASSFL":"Line Card/Controller/RP"},{"FCODES":"RDMT_2-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_3-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_4-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_5-65-600","CLASSFL":"Fabric Card/Controller/RP"},{"FCODES":"RDMT_6-65-600","CLASSFL":"Line Card/Controller/RP"},...