Oracle 18c

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"}]