CODEWITHSUNDEEP
oracle-database
Arpan Paliwal
Arpan Paliwal

Reputation: 264

How to use table type in oracle select statement

i have created below type in Oracle

create or replace TYPE "T_STRING_LIST" 
AS TABLE OF varchar2(4000)

and using it to collect employee ids.

V_EMP_ID_LIST T_STRING_LIST := T_STRING_LIST();


SELECT employee_id bulk collect
        INTO V_EMP_ID_LIST 
        FROM EMPLOYEE
        WHERE DEPT_ID = 101;

Now i want to use this V_EMP_ID_LIST in select statement to select details from another table.

select * from employee_address
where employee_id in (V_EMP_ID_LIST);

Can anyone help how to do it.

Upvotes: 0

Views: 89

Answers (1)

MT0
MT0

Reputation: 167932

Use the MEMBER OF operator:

DECLARE
  V_EMP_ID_LIST T_STRING_LIST;
  cur           SYS_REFCURSOR;
BEGIN
  SELECT employee_id
  BULK COLLECT INTO V_EMP_ID_LIST 
  FROM   EMPLOYEE
  WHERE  DEPT_ID = 101;

  OPEN cur FOR
    select *
    from   employee_address
    where employee_id MEMBER OF V_EMP_ID_LIST;
END;
/

or a table collection expression:

DECLARE
  V_EMP_ID_LIST T_STRING_LIST;
  cur           SYS_REFCURSOR;
BEGIN
  SELECT employee_id
  BULK COLLECT INTO V_EMP_ID_LIST 
  FROM   EMPLOYEE
  WHERE  DEPT_ID = 101;

  OPEN cur FOR
    select *
    from   employee_address
    where employee_id IN ( SELECT COLUMN_VALUE FROM TABLE( V_EMP_ID_LIST ) );
END;
/

Upvotes: 1

Related Questions