Oracle PLSQL convert json_array_t data to SQL object type table data

Question

I'm new to Oracle PLSQL and I'm finding difficulty to find an exact logic which can convert my json_array _t type of data to a SQL table object type as shown below. please help me

DECLARE
    input_type    XX_INPUT_TYPE;
    input_json    JSON_ARRAY_T;
BEGIN
    input_json := JSON_ARRAY_T('[{"empid":109, "name":"raj","salary":111},{"empid":110, "name":"raj1","salary":222}]');
    
    input_type := input_json;
    -- i want to convert this json_array_t to input_type data, please help as I'm new to PLSQL
    
END;

Basically I want to assign data in input_json to input_type variable in above block of PLSQL.

and below is my type definition

CREATE OR REPLACE TYPE XX_OUTPUT_OBJ 
AS
OBJECT(
    EMP_ID      NUMBER,
    NAME        VARCHAR2(50),
    SALARY      NUMBER
);

CREATE OR REPLACE TYPE XX_INPUT_TYPE
IS TABLE OF XX_OUTPUT_OBJ;

Answer 1

You can do it in SQL; you do not need PL/SQL:

SELECT CAST(
         COLLECT(
           XX_OUTPUT_OBJ(
             emp_id,
             name,
             salary
           )
         ) AS XX_INPUT_TYPE
       )
FROM   JSON_TABLE(
         '[{"empid":109, "name":"raj","salary":111},{"empid":110, "name":"raj1","salary":222}]',
         '$[*]'
         COLUMNS (
           emp_id NUMBER       PATH '$.empid',
           name   VARCHAR2(50) PATH '$.name',
           salary NUMBER       PATH '$.salary'
         )
       );

If you did want it in PL/SQL then:

DECLARE
  input_type    XX_INPUT_TYPE;
BEGIN
  SELECT XX_OUTPUT_OBJ(
           emp_id,
           name,
           salary
         )
  BULK COLLECT INTO input_type
  FROM   JSON_TABLE(
           '[{"empid":109, "name":"raj","salary":111},{"empid":110, "name":"raj1","salary":222}]',
           '$[*]'
           COLUMNS (
             emp_id NUMBER       PATH '$.empid',
             name   VARCHAR2(50) PATH '$.name',
             salary NUMBER       PATH '$.salary'
           )
         );

  FOR i IN 1 .. input_type.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      input_type(i).emp_id || ', '
      || input_type(i).name || ','
      || input_type(i).salary
    );
  END LOOP;
END;
/

fiddle