Oracle Nested table as stored procedure parameters

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:

• Collections In Oracle
• Oracle Collection in PLSQL
• Oracle Collection In SQL
• Collection Methods Example In Oracle