By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE MY_TABLE(ID, TB_VALUE) AS
SELECT 1, 'valueTest' FROM DUAL UNION ALL
SELECT 2, 'valueTest2' FROM DUAL UNION ALL
SELECT 3, 'valueTest3' FROM DUAL;
3 rows affected
SELECT JSON_ARRAYAGG(
JSON_OBJECT( KEY 'id' VALUE id, KEY 'value' VALUE tb_value )
FORMAT JSON
ORDER BY id
RETURNING CLOB
) AS json
FROM my_table;
| JSON |
|---|
| [{"id":1,"value":"valueTest"},{"id":2,"value":"valueTest2"},{"id":3,"value":"valueTest3"}] |
CREATE PACKAGE PKG_GETCOR AS
FUNCTION SelectFunc RETURN CLOB;
END;
/
CREATE PACKAGE BODY PKG_GETCOR AS
FUNCTION SelectFunc RETURN CLOB
IS
v_json CLOB;
BEGIN
SELECT JSON_ARRAYAGG(
JSON_OBJECT( KEY 'id' VALUE id, KEY 'value' VALUE tb_value )
FORMAT JSON
ORDER BY id
RETURNING CLOB
)
INTO v_json
FROM my_table;
RETURN v_json;
END SelectFunc;
END PKG_GETCOR;
/
SELECT pkg_getcor.selectfunc() FROM DUAL;
| PKG_GETCOR.SELECTFUNC() |
|---|
| [{"id":1,"value":"valueTest"},{"id":2,"value":"valueTest2"},{"id":3,"value":"valueTest3"}] |