Sayantan's Blog On Oracle

How To Pass Array in where clause in Oracle?

Array can be passed in WHERE clause of sql query and in PLSQL as well.

But to use any array in a SQL query, we will store the array data inside database.

Setup for passing array in WHERE clause in Oracle SQL:

Creating a record type EMP_REC_TYPE in DB:

--How To Pass Array in where clause in Oracle? : Setup

CREATE OR REPLACE TYPE EMP_REC_TYPE
AS OBJECT
(EMPLOYEE_ID NUMBER(6),
 FIRST_NAME  VARCHAR2(20),
 LAST_NAME   VARCHAR2(20),
 SALARY      NUMBER(8, 2)
);
/

Creating a table type EMP_TAB_TYPE with record type EMP_REC_TYPE:

CREATE OR REPLACE TYPE EMP_TAB_TYPE AS TABLE OF EMP_REC_TYPE;
/

Creating a new table with table type EMP_TAB_TYPE:

CREATE TABLE EMPLOYEES_TEMP
(EMP_DTL EMP_TAB_TYPE)
NESTED TABLE EMP_DTL STORE AS EMP_DETAILS;

Inserting data into EMP_TEMP table:

--How To Pass Array in where clause in Oracle? : Setup

BEGIN
  FOR I IN (SELECT EMPLOYEE_ID,
                   FIRST_NAME,
                   LAST_NAME,
                   SALARY
            FROM EMPLOYEES
            WHERE DEPARTMENT_ID = 30)
  LOOP
    INSERT INTO EMPLOYEES_TEMP VALUES(EMP_TAB_TYPE(EMP_REC_TYPE(I.EMPLOYEE_ID, I.FIRST_NAME, I.LAST_NAME, I.SALARY)));
  END LOOP;
END;
/

Viewing the raw data of EMPLOYEES_TEMP table:

SELECT * FROM EMPLOYEES_TEMP;

Data from above query is not user readable format. To convert the data into user readable format use the below query:

SELECT B.EMPLOYEE_ID, 
       B.FIRST_NAME, 
       B.LAST_NAME, 
       B.SALARY
FROM EMPLOYEES_TEMP A, 
     TABLE(A.EMP_DTL) B;

Using the data of EMPLOYEES_TEMP table in the WHERE clause of EMPLOYEES table:

SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (SELECT B.EMPLOYEE_ID
                      FROM EMPLOYEES_TEMP A, 
                           TABLE(A.EMP_DTL) B
                     );

Setup for passing array in WHERE clause in Oracle PLSQL:

SET SERVEROUTPUT ON;
DECLARE
  TYPE EMP_REC IS RECORD
  (EMPLOYEE_ID  EMPLOYEES.EMPLOYEE_ID%TYPE,
   FIRST_NAME   EMPLOYEES.FIRST_NAME%TYPE,
   LAST_NAME    EMPLOYEES.LAST_NAME%TYPE,
   SALARY       EMPLOYEES.SALARY%TYPE
  );
  --
  TYPE T_EMP_TAB IS TABLE OF EMP_REC INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
BEGIN
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         SALARY
  BULK COLLECT INTO V_EMP_TAB
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID IN (SELECT B.EMPLOYEE_ID
                        FROM EMPLOYEES_TEMP A, 
                             TABLE(A.EMP_DTL) B
                        );
  --
  FOR I IN 1..V_EMP_TAB.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' -> NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' -> SALARY -> '||V_EMP_TAB(I).SALARY);
  END LOOP;
END;
/

RELATED TOPICS:

• Oracle Collection In SQL
• Oracle Collection in PLSQL