Table of Contents
How to pass array in Oracle SQL query?
We can use PIPELINED TABLE FUNCTION to pass an array to Oracle SQL query.
We can also use normal function instead of Pipelined table function but from performance perspective Pipelined table function is better than normal function. Because unlike normal function, Pipelined table function does not wait for the entire collection to fetch before showing the output.
Setup for Pipelined Table function:
--How to pass array in Oracle SQL query? : Setup CREATE OR REPLACE PACKAGE EMP_PKG IS TYPE RC_1 IS REF CURSOR RETURN EMPLOYEES%ROWTYPE; END; /
--How to pass array in Oracle SQL query? : Setup CREATE OR REPLACE TYPE EMP_REC IS OBJECT ( EMPLOYEE_ID NUMBER(6), FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, JOB_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), DEPARTMENT_ID NUMBER(4) ); /
CREATE OR REPLACE TYPE EMP_TAB IS TABLE OF EMP_REC; /
--How to pass array in Oracle SQL query? : Example CREATE OR REPLACE FUNCTION FUN_EMP_PL(REC_IN EMP_PKG.RC_1) RETURN EMP_TAB PIPELINED IS TYPE T_EMP_TAB IS TABLE OF EMPLOYEES%ROWTYPE INDEX BY PLS_INTEGER; V_EMP_TAB T_EMP_TAB; BEGIN LOOP FETCH REC_IN BULK COLLECT INTO V_EMP_TAB LIMIT 100; EXIT WHEN V_EMP_TAB.COUNT = 0; -- FOR I IN 1..V_EMP_TAB.COUNT LOOP PIPE ROW(EMP_REC(V_EMP_TAB(I).EMPLOYEE_ID, V_EMP_TAB(I).FIRST_NAME, V_EMP_TAB(I).LAST_NAME, V_EMP_TAB(I).EMAIL, V_EMP_TAB(I).PHONE_NUMBER, V_EMP_TAB(I).HIRE_DATE, V_EMP_TAB(I).JOB_ID, V_EMP_TAB(I).SALARY, V_EMP_TAB(I).COMMISSION_PCT, V_EMP_TAB(I).MANAGER_ID, V_EMP_TAB(I).DEPARTMENT_ID ) ); END LOOP; END LOOP; RETURN; END; /
SELECT * FROM TABLE(FUN_EMP_PL(CURSOR(SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 30)));
![How to pass array in Oracle SQL query? : Output](https://sayantanblogonoracle.in/wp-content/uploads/2022/01/image-15-1024x164.png)
Setup for Normal Function:
CREATE OR REPLACE FUNCTION FUN_EMP(REC_IN EMP_PKG.RC_1) RETURN EMP_TAB IS TYPE T_EMP_TAB IS TABLE OF EMPLOYEES%ROWTYPE INDEX BY PLS_INTEGER; V_EMP_TAB T_EMP_TAB; P_EMP_TAB EMP_TAB := EMP_TAB(); BEGIN LOOP FETCH REC_IN BULK COLLECT INTO V_EMP_TAB LIMIT 100; EXIT WHEN V_EMP_TAB.COUNT = 0; -- FOR I IN 1..V_EMP_TAB.COUNT LOOP P_EMP_TAB.EXTEND; P_EMP_TAB(P_EMP_TAB.LAST) := EMP_REC(V_EMP_TAB(I).EMPLOYEE_ID, V_EMP_TAB(I).FIRST_NAME, V_EMP_TAB(I).LAST_NAME, V_EMP_TAB(I).EMAIL, V_EMP_TAB(I).PHONE_NUMBER, V_EMP_TAB(I).HIRE_DATE, V_EMP_TAB(I).JOB_ID, V_EMP_TAB(I).SALARY, V_EMP_TAB(I).COMMISSION_PCT, V_EMP_TAB(I).MANAGER_ID, V_EMP_TAB(I).DEPARTMENT_ID ); END LOOP; END LOOP; CLOSE REC_IN; RETURN P_EMP_TAB; END; /
SELECT * FROM TABLE(FUN_EMP(CURSOR(SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 30)));
![](https://sayantanblogonoracle.in/wp-content/uploads/2022/01/image-16-1024x163.png)
RELATED TOPICS: