OIC - PLSQL - How to feed comma separated fields as input and fetch table of records output using stored Procedure

Usecase:  We have a requirement in oracle integration where we need to pass comma separated ids as input and fetch the employee names along with the ids as a table of records from a database table using stored procedure.

PLSQL CODE:

Create object type:

CREATE OR REPLACE TYPE employee_row_type AS OBJECT(

id number,

firstname varchar2(200)

);

Create a table of rows types:

CREATE OR REPLACE TYPE employee_table_type AS TABLE OF employee_row_type;

Create the stored procedure:

CREATE OR REPLACE PROCEDURE fetch_employee_data(p_employee_ids IN VARCHAR2,p_employee_data OUT employee_table_type)

IS

BEGIN

SELECT employee_row_type(id,firstname)

BULK COLLECT INTO p_employee_data FROM employees_sd WHERE id IN (SELECT TO_NUMBER(REGEXP_SUBSTR(p_employee_ids, '[^,]+',1,LEVEL)) FROM DUAL CONNECT BY REGEXP_SUBSTR(p_employee_ids, '[^,]+',1,LEVEL) IS NOT NULL);

END fetch_employee_data;

/

Test code:

DECLARE 

v_emp_ids varchar2(100) := '1,2,3';

v_emp_data employee_table_type;

BEGIN

fetch_employee_data(v_emp_ids,v_emp_data);

FOR i IN 1..v_emp_data.COUNT LOOP

dbms_output.put_line('Empno = ' || v_emp_data(i).id || ', Name: ' || v_emp_data(i).firstname);

END LOOP;

END;

/

OIC screenshots:

Test: