oracle - Function or Procedure for an IN clause

Question

I want to write a funcion or procedure that can be used in the IN clause of another procedure. The function or procedure would return ID numbers.

The main procedure would say something like

SELECT *
FROM EMPLOYEES
WHERE OFFICE_ID IN (GET_OFFICE_IDS);  -- GET_OFFICE_IDS requires no parameters

GET_OFFICE_IDS returns a VARCHAR2 with the ID separated by commas. When I run the main procedure, I get a "ORA-01722: invalid number" error which makes sense but I don't know where I need to go from here.

Do I need GET_OFFICE_IDS to create a temp table that the main procedure uses? If so, will there be a performance penalty?

Answer 1

Here is a working example of the nested table solution, using the EMP table:

create type t_ids is table of integer
/

create or replace function get_office_ids return t_ids
is
   l_ids t_ids := t_ids();
   l_idx integer := 0;
begin
   for r in (select empno from emp where deptno=10)
   loop
      l_ids.extend;
      l_idx := l_idx+1;
      l_ids(l_idx) := r.empno;
   end loop;
   return l_ids;
end;
/

select ename from emp where empno in (select * from table(get_office_ids));


ENAME
----------
CLARK
KING
TEST
MILLER
BINNSY
FARMER