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;
