Oracle Nested table as stored procedure parameters
Nested table to pass as a parameter to stored procedure:
- To pass a nested table as a parameter to stored procedure we need to declare a nested table type inside a package specification
- Then create a plsql block to populate the nested table via extend method before passing it to stored procedure.
Setup:
--Oracle Nested table as stored procedure parameters : Example CREATE OR REPLACE PACKAGE PKG_INIT_COLL_NT IS TYPE EMP_REC IS RECORD (EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE, FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE, LAST_NAME EMPLOYEES.LAST_NAME%TYPE, JOB_ID EMPLOYEES.JOB_ID%TYPE, SALARY EMPLOYEES.SALARY%TYPE ); TYPE EMP_TAB IS TABLE OF EMP_REC; V_EMP_TAB EMP_TAB; END; /
--Oracle Nested table as stored procedure parameters : Example
CREATE OR REPLACE PROCEDURE SP_GET_EMP_DTL_NT(P_EMP_TAB PKG_INIT_COLL_NT.EMP_TAB)
IS
V_EMP_TAB PKG_INIT_COLL_NT.EMP_TAB := PKG_INIT_COLL_NT.EMP_TAB();
BEGIN
V_EMP_TAB := P_EMP_TAB;
FOR I IN 1..V_EMP_TAB.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('EMPLOYEE INFO -> ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||'-> NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' -> JOB -> '||V_EMP_TAB(I).JOB_ID||' -> SALARY -> '||V_EMP_TAB(I).SALARY);
END LOOP;
END;
/ --Oracle Nested table as stored procedure parameters : Example
SET SERVEROUTPUT ON;
DECLARE
V_EMP_TAB PKG_INIT_COLL_NT.EMP_TAB := PKG_INIT_COLL_NT.EMP_TAB();
BEGIN
FOR I IN (SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
)
LOOP
V_EMP_TAB.EXTEND;
V_EMP_TAB(V_EMP_TAB.LAST).EMPLOYEE_ID := I.EMPLOYEE_ID;
V_EMP_TAB(V_EMP_TAB.LAST).FIRST_NAME := I.FIRST_NAME;
V_EMP_TAB(V_EMP_TAB.LAST).LAST_NAME := I.LAST_NAME;
V_EMP_TAB(V_EMP_TAB.LAST).JOB_ID := I.JOB_ID;
V_EMP_TAB(V_EMP_TAB.LAST).SALARY := I.SALARY;
END LOOP;
--
SP_GET_EMP_DTL_NT(V_EMP_TAB);
END;
/ RELATED TOPICS: