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: