Re: IN Subquery on Collection

klabu schrieb:
> *** 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;
>

You can select only from unnested (table expression) collection (at least in 10gR2) . Collection should be an SQL datatype ( as opposite to PL SQL datatype).
There are tons of examples in pl sql developer quide ,sql reference and app dev guide - oo features.

http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2241
http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14260/adobjcol.htm#sthref481
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2071637

Best regards

Maxim Received on Wed Jan 31 2007 - 11:59:02 CST