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 ?
CodePudding user response:
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
CodePudding user response:
DBMS_OUTPUT.PUT_LINE is meant to be used for debugging and shouldn't show up in user's applications.
If you wish to display it, you can change that procedure into a function or give the procedure an out parameter and then read that.
Turn it into a function, put the json into a clob, return it and then call it like:
SELECT SelectProc() FROM DUAL;