How to pass array in Oracle SQL query?

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)));

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)));

RELATED TOPICS:

• How To Pass Array in where clause in Oracle?