OIC - PLSQL - How to feed table of record types and fetch table of records output using Package and Procedure

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

PLSQL code:

Package definition:

CREATE OR REPLACE PACKAGE PKG_DUMMY AS

TYPE rcd_type IS RECORD

(

Id NUMBER,

Fname VARCHAR2(100)

);

TYPE rcd_tbl IS TABLE OF rcd_type;

PROCEDURE GET_FNAME(

tbl_io IN OUT rcd_tbl,

sqlcode_io IN OUT NUMBER,

sqlerrm_io IN OUT VHARCHAR2

);

END PKG_DUMMY;

Package Body:

CREATE OR REPLACE PACKAGE BODY PKG_DUMMY AS

PROCEDURE GET_FNAME(

tbl_io IN OUT rcd_tbl,

sqlcode_io IN OUT NUMBER,

sqlerrm_io IN OUT VARCHAR2

) IS

v_tbl rcd_tbl := rcd_tbl();

BEGIN

FOR idx in 1..tbl_io.count LOOP

v_tbl.extend;

v_tbl(idx).id := tbl_io(idx).id;

BEGIN

SELECT firstname into v_tbl(idx).fname from employees_sd where id := tbl_io(idx).id;

EXCEPTION

WHEN OTHERS THEN

v_tbl(idx).fname :='NA';

END;

END LOOP;

tbl_io := v_tbl;

sqlcode_io :=0;

sqlerrm_io := Null;

sqlcode_io := SQLCODE;

sqlcode_io := SQLERRM;

DBMS_OUTPUT.PUT_LINE('procname : ' || 'Others Exception. SQL: '||to_char(sqlcode_io) || ':' || substr(sqlerrm, 1 , 70)|| '.');

END GET_FNAME;

END PKG_DUMMY;

Call from OIC and Test:

Test codes from Database:

DECLARE

TBL_IO PKG_DUMMY.RCD_TBL;

SQLCODE_IO NUMBER;

SQLERRM_IO VARCHAR2(100);

BEGIN

TBL_IO := PKG_DUMMY.RCD_TBL();

TBL_IO.extend;

TBL_IO(1) := PKG_DUMMY.RCD_TYPE(1, 'a');

TBL_IO.extend;

TBL_IO(2) := PKG_DUMMY.RCD_TYPE(2, 'a');

SQLCODE_IO :=NULL;

SQLERRM_IO := NULL;

PKG_DUMMY.GET_FNAME(

TBL_IO => TBL_IO,

SQLCODE_IO => SQLCODE_IO,

SQLERRM_IO => SQLERRM_IO);

FOR i in 1..tbl_io.count LOOP

DBMS_OUTPUT.PUT_LINE(' ID : ' || tbl_io(i).id || ' and fname : ' || tbl_io(i).fname);

END LOOP;

END;

PLSQL code screenshots:

The same we can also do using package and function as below code:

Package definition:

CREATE OR REPLACE PACKAGE EMPPKG IS

TYPE EMP_REC IS RECORD(

id NUMBER,

fname VARCHAR2(100)

);

TYPE EMP_TABLE IS TABLE OF EMP_REC;

FUNCTION getEmployeeData(

p_emp_data EMP_TABLE

) RETURN EMP_TABLE;

END EMPPKG;

Package Body:

CREATE OR REPLACE PACKAGE BODY EMPPKG IS

FUNCTION getEmployeeData(

p_emp_data EMP_TABLE

) RETURN EMP_TABLE

IS

v_result  EMP_TABLE := EMP_TABLE();

BEGIN

FOR i IN 1..p_emp_data.COUNT LOOP

v_result.extend;

v_result(i).id := p_emp_data(i).id;

SELECT firstname INTO v_result(i).fname FROM Employees_sd WHERE id = p_emp_data(i).id;

END LOOP;

RETURN v_result;

END getEmployeeData;

END EMPPKG;

Test code:

DECLARE

P_EMP_DATA EMPPKG.EMP_TABLE;

v_return EMPPKG.EMP_TABLE;

BEGIN

P_EMP_DATA := EMPPKG.EMP_TABLE();

P_EMP_DATA.extend;

P_EMP_DATA(1) := EMPPKG.EMP_REC(1, 'a');

P_EMP_DATA.extend;

P_EMP_DATA(2) := EMPPKG.EMP_REC(2, 'a');

v_return := EMPPKG.GETEMPLOYEEDATA(

P_EMP_DATA => P_EMP_DATA

);

FOR i in 1..v_return.count LOOP

DBMS_OUTPUT.PUT_LINE(' ID : ' || v_return(i).id || ' and fname : ' || v_return(i).fname);

END LOOP;

END;