Re: IN Subquery on Collection

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Thu, 01 Feb 2007 17:09:15 +0100
Message-ID: <45C210AB.7090901@arcor.de>

m.t schrieb:

> "Maxim Demenko";

>> 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
>>

> 
> 
> thanks
> So there is NO WAY Assoc-Array/Index-by Table can be used in this fashion , 
> correct ? 
> 
> 

Strictly spoken, where is NO WAY to use a PL SQL Type (and associative array is pure pl sql type) in this fashion in current release (i wouldn't even try to predict, what will be changed in the future releases). Sometimes, however, it seems to be possible: ( example taken unchanged from www.sql.ru)

SQL> CREATE OR REPLACE PACKAGE test_pk IS

   2 TYPE test_type IS RECORD(

   3      x VARCHAR2(10),
   4      y VARCHAR2(10));

   5 TYPE test_tab IS TABLE OF test_type;    6 END test_pk;
   7 /

Package created.

SQL>
SQL> CREATE OR REPLACE FUNCTION test_fn RETURN test_pk.test_tab

   2 PIPELINED IS
   3 m_row test_pk.test_type;
   4 BEGIN
   5 FOR j IN 1 .. 2 LOOP

   6      m_row.x := ('X' || j);
   7      m_row.y := ('Y' || j);
   8      PIPE ROW(m_row);

   9 END LOOP;
  10 RETURN;
  11 END test_fn;
  12 /

Function created.

SQL>
SQL>
SQL> SELECT * FROM TABLE(test_fn());

X          Y
---------- ----------
X1         Y1
X2         Y2

But if you look, closer, you will see, that, to perform it, oracle in background created SQL types (object types and collection types) to make it possible.

You can as well read a long discussion by AskTom http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4447489221109

Best regards

Maxim Received on Thu Feb 01 2007 - 10:09:15 CST