Returning Json string from oracle procedure

I need to have a stored procedure in PL/SQL that return a string containing a json object. This object must be a list of object representing rows a table.

For example I have the table MY_TABLE(ID, TB_VALUE)

The output must be :

[
  {
    "id":"1",
    "value":"valueTest"
  },
  {
    "id":"2",
    "value":"valueTest2"
  },
  {
    "id":"3",
    "value":"valueTest3"
  }
]

My stored procedure looks like that :

CREATE OR REPLACE PACKAGE BODY "PKG_GETCOR" AS

PROCEDURE SelectProc(output OUT VARCHAR2) IS 
v_json_start CHAR(1) := '[';
v_json_end CHAR(1) := ']';
l_first_rec BOOLEAN := TRUE;

BEGIN
    output := v_json_end;
FOR output IN (SELECT * FROM MY_TABLE)
LOOP
    IF NOT l_first_rec
    THEN
        output := output || ',';
    END IF;
    output := output || JSON_OBJECT('id' VALUE ID, 'value' VALUE TB_VALUE));
END LOOP;   
output := output || (v_json_end); 
DBMS_OUTPUT.PUT_LINE(output);

END SelectProc;

END PKG_GETCOR;

What’s the best way to achieve this ? Should I use DBMS_OUTPUT.PUT_LINE ?

Answer

From Oracle 12, you do not need a procedure or to create your own JSON functions.

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;

Outputs:

JSON
[{“id”:1,”value”:”valueTest”},{“id”:2,”value”:”valueTest2″},{“id”:3,”value”:”valueTest3″}]

---

If you really want it in a package then convert it to a function and just wrap the query:

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;
/

Then:

SELECT pkg_getcor.selectfunc() FROM DUAL;

Outputs:

PKG_GETCOR.SELECTFUNC()
[{“id”:1,”value”:”valueTest”},{“id”:2,”value”:”valueTest2″},{“id”:3,”value”:”valueTest3″}]

db<>fiddle here