How can i shorten the fetch into clause in my PLSQL to return JSON_OBJECT_T type of data

Question

I have a requirement to return the response in JSON_OBJECT_T which contains array of records from table. but I have 50 columns to fetch and this fetch into clause makes me to write all 50 column names, is there a way to directly write the response from select query to JSON_ARRAY_T and JSON_OBJECT_T and return. Please help as I'm new to PLSQL

-- Declare a JSON object variable and a cursor for the query
DECLARE
  l_json_array JSON_ARRAY_T := JSON_ARRAY_T();
  l_json_object JSON_OBJECT_T;
  p_json_object JSON_OBJECT_T := JSON_OBJECT_T();
  l_cursor SYS_REFCURSOR;
  l_id NUMBER;
  l_name VARCHAR2(100);
  l_email_id VARCHAR2(100);
BEGIN
  OPEN l_cursor FOR
    SELECT id, name, email_id
    FROM EMPLOYEE_TBL;
  LOOP
    FETCH l_cursor INTO l_id, l_name, l_email_id;
    EXIT WHEN l_cursor%NOTFOUND;
    l_json_object := JSON_OBJECT_T();
    l_json_object.put('empno', l_id);
    l_json_object.put('ename', l_name);
    l_json_object.put('job', l_email_id);
    l_json_array.append(l_json_object);
  END LOOP;
  CLOSE l_cursor;
  p_json_object.put('employees', l_json_array);
  DBMS_OUTPUT.PUT_LINE(p_json_object.stringify);
END;

Response i need as below but i want to improve above code such that i dont have to declare all the column names as variables in PLSQL like l_id, l_name, l_email_id

{"employees":[{"empno":1065,"ename":"Abu","job":"email1@example.com"},{"empno":1066,"ename":"Umar","job":"email2@example.com"}]}

Answer 1

You can do it entirely in SQL:

SELECT JSON_SERIALIZE(
         JSON_OBJECT(
           KEY 'employees' VALUE JSON_ARRAYAGG(
             JSON_OBJECT(
               KEY 'empno' VALUE id,
               KEY 'ename' VALUE name,
               KEY 'job'   VALUE email_id
             )
             ORDER BY id
           )
         )
         RETURNING CLOB
       ) AS json
FROM   employee_tbl;

Which, for the sample data:

CREATE TABLE employee_tbl (id, name, email_id) AS
  SELECT 1065, 'Abu',  'email1@example.com' FROM DUAL UNION ALL
  SELECT 1066, 'Umar', 'email2@example.com' FROM DUAL

Outputs:

JSON
{"employees":[{"empno":1065,"ename":"Abu","job":"email1@example.com"},{"empno":1066,"ename":"Umar","job":"email2@example.com"}]}

If you did want to use PL/SQL then:

DECLARE
  p_json CLOB;
BEGIN
  SELECT JSON_SERIALIZE(
           JSON_OBJECT(
             KEY 'employees' VALUE JSON_ARRAYAGG(
               JSON_OBJECT(
                 KEY 'empno' VALUE id,
                 KEY 'ename' VALUE name,
                 KEY 'job'   VALUE email_id
               )
               ORDER BY id
             )
           )
           RETURNING CLOB
         ) AS json
  INTO   p_json
  FROM   employee_tbl;
  DBMS_OUTPUT.PUT_LINE(p_json);
END;
/

Which outputs:

{"employees":[{"empno":1065,"ename":"Abu","job":"email1@example.com"},{"empno":1066,"ename":"Umar","job":"email2@example.com"}]}

fiddle