How to set value in out parameter in oracle-CodePudding

I want to create a stored procedure on oracle which return the value in a out parameter. I was able to create a sample query but I am able to set the value to output variable. And the output should be converted into JSON

Here is my code

 CREATE OR REPLACE PROCEDURE GET_TABLE_NAMES(JSON_DATA OUT CLOB )
  --  OUT OUT_IS_SUCCESS BOOLEAN,
  --  OUT OUT_ERROR_MESSAGE VARCHAR(4000)
AS
l_cursor_1 SYS_REFCURSOR;


BEGIN

--JSON_DATA :=
    OPEN l_cursor_1 FOR SELECT JSON_ARRAYAGG(
          JSON_OBJECT('TABLE_NAME' VALUE T.TABLE_NAME)
        ) INTO JSON_DATA
     FROM
        (
       select TABLE_NAME FROM all_tables 
    ) T 
; 

JSON_DATA := TO_CLOB(l_cursor_1);
--dbms_sql.return_result(l_cursor_1);

END GET_TABLE_NAMES;

I want to get output in 3 variables JSON_DATA contain out in JSON format OUT_IS_SUCCESS Boolean flag if no error occurred OUT_ERROR_MESSAGE if any error message

Any help will be appreciated. Thanks in advance

CodePudding user response：

You can tell json_arrayagg to return a CLOB, and select that directly into your OUT parameter. The other two commented-out parameters are malformed; the OUT is in the wrong place, and the string should not have a size.

You can do something like:

CREATE OR REPLACE PROCEDURE GET_TABLE_NAMES(
  P_JSON_DATA OUT CLOB,
  P_IS_SUCCESS OUT BOOLEAN,
  P_ERROR_MESSAGE OUT VARCHAR2
)
AS
BEGIN
  SELECT JSON_ARRAYAGG(
      JSON_OBJECT('TABLE_NAME' VALUE TABLE_NAME)
      FORMAT JSON
      RETURNING CLOB
    )
  INTO P_JSON_DATA
  FROM all_tables;

  P_IS_SUCCESS := TRUE;
EXCEPTION
  WHEN OTHERS THEN
    P_IS_SUCCESS := FALSE;
    P_ERROR_MESSAGE := SQLERRM;
END GET_TABLE_NAMES;
/

db<>fiddle, with an extra bit of dummy code to force an exception on the second call.

But catching exceptions, particularly with when others, just to turn them into basic strings with no context such as the position the error occurred, is rarely a good idea - it would be simpler to just let any exception flow back to the caller:

CREATE OR REPLACE PROCEDURE GET_TABLE_NAMES(
  P_JSON_DATA OUT CLOB
)
AS
BEGIN
  SELECT JSON_ARRAYAGG(
      JSON_OBJECT('TABLE_NAME' VALUE TABLE_NAME)
      FORMAT JSON
      RETURNING CLOB
    )
  INTO P_JSON_DATA
  FROM all_tables;
END GET_TABLE_NAMES;
/

db<>fiddle

... unless you have a good reason to hide useful debugging information.