IN Subquery on Collection

• 10gR2 ***

Basically I'm trying to do an IN operation on a Collection (line #10 below)

• Do I (in 10gR2) still have to define the collection type on db first ?
• If not, can I use Assoc. Array here ?
• Appreciate for code to make line #10 work.

thanks !

SQL>
  1 DECLARE
  2 l_cur SYS_REFCURSOR ;
  3 TYPE typ_a IS TABLE OF emp.ename%TYPE;   4 l_table typ_a;
  5 BEGIN
  6 OPEN l_cur FOR 'select ename from emp' ;   7 FETCH l_cur BULK COLLECT INTO l_table ;   8
  9 INSERT INTO my_emp SELECT * FROM emp WHERE ename IN ( /* select from l_table */ );
 10
 11 END; Received on Wed Jan 31 2007 - 11:15:09 CST