CODEWITHSUNDEEP
jsonoracle-databaseplsql
Niklas Eriksson
Niklas Eriksson

Reputation: 3

Build a procecure that returns a JSON Object (ORACLE 19)

I need to build a procedure that has a couple of varchars that I need to merge into a JSON object which should be the output of the procedure.

For example:

PROCEDURE json (p_input varchar2, o_json (datatype?) is

v_input_      varchar2(200);
v_str1        varchar2(2000);
v_str2        varchar2(2000);

begin

v_str1 := 'test1';
v_str2 := 'test2';

end;

What I want to do is to create the output as a JSON consisting of the two varchars v_str1 and v_str2 with the KEY values as "Machine" and "Serial".

Any ideas how to do this?

Thanks for the kind help.

Upvotes: 0

Views: 97

Answers (2)

MT0
MT0

Reputation: 168671

You can use:

CREATE PROCEDURE json(
  i_str1  IN  VARCHAR2,
  i_str2  IN  VARCHAR2,
  o_json  OUT CLOB
)
IS
BEGIN
  SELECT JSON_OBJECT(
           KEY 'Machine' VALUE i_str1,
           KEY 'Serial'  VALUE i_str2
         )
  INTO   o_json
  FROM   DUAL;
END;
/

Then:

DECLARE
  v_json CLOB;
BEGIN
  json('a', 'b', v_json);
  DBMS_OUTPUT.PUT_LINE(v_json);
END;
/

Outputs:

{"Machine":"a","Serial":"b"}

db<>fiddle here

Upvotes: 2

Gnqz
Gnqz

Reputation: 3382

You could use the JSON_OBJECT:

SELECT JSON_OBJECT (KEY 'Machine' IS some_object.Machine,
                    KEY 'Serial'  IS some_object.Serial ) "your JSON"
  FROM (SELECT 'test1' AS Machine
             , 'test2' AS Serial
          FROM dual) some_object;

reference

Upvotes: 1

Related Questions