How to pass collection in stored procedure in Oracle?

How to pass collection in stored procedure in Oracle?

Passing collection to Oracle stored procedure:

• To pass a collection to a stored procedure we need to declare a collection variable inside a package specification.
• Then we need a plsql block where we can define the collection and pass it into the stored procedure.

Setup:

--How to pass collection in stored procedure in Oracle? Example

CREATE OR REPLACE PACKAGE PKG_INIT_COLL
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 INDEX BY PLS_INTEGER;
  V_EMP_TAB EMP_TAB;
END;
/

--How to pass collection in stored procedure in Oracle? Example

CREATE OR REPLACE PROCEDURE SP_GET_EMP_DTL(P_EMP_TAB PKG_INIT_COLL.EMP_TAB)
IS
  V_EMP_TAB PKG_INIT_COLL.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;
/

--How to pass collection in stored procedure in Oracle? Example

SET SERVEROUTPUT ON;
DECLARE
  V_EMP_TAB PKG_INIT_COLL.EMP_TAB;
BEGIN
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  BULK COLLECT INTO V_EMP_TAB
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  --
  SP_GET_EMP_DTL(V_EMP_TAB);
END;
/

RELATED TOPICS:

• Collections In Oracle
• Oracle Collection in PLSQL
• Oracle Collection In SQL
• Collection Methods Example In Oracle
• Oracle Nested table as stored procedure parameters