title: Oracle COLLECT function and types
body: <p>I've got an issue with Oracle 10g and the use of the <code>COLLECT</code> function. I only found out about its existence this morning but have a problem which could be solved by using it in association with the <code>member of</code> condition.</p>
  
  <p>Initially I wrote the code shown below, which came back with the error "ORA_00932: inconsistent datatypes: expected UDT got -".</p>
  
  <pre><code>with my_tab as (
    select 1 as cola, 1 as colb from dual union all
    select 1 as cola, 2 as colb from dual union all
    select 2 as cola, 3 as colb from dual union all
    select 2 as cola, 4 as colb from dual union all
    select 3 as cola, 3 as colb from dual union all
    select 3 as cola, 4 as colb from dual union all
    select 4 as cola, 1 as colb from dual union all
    select 4 as cola, 2 as colb from dual 
  )
  select 
    cola, 
    colb_vals
  from (
    select 
      cola, 
      collect(colb) as colb_vals
    from my_tab
    group by cola
  )
  where 2 member of colb_vals
  </code></pre>
  
  <p>I found this a little strange since in Oracle 10.2.4.0, it seems that the database will create a temporary system generated user defined type, and use that. If I remove the condition, (<code>where 2 member of colb_vals</code>) then the code will run and show the data retrieved included the temporary UDT (named SYSTPblahblahblah==).</p>
  
  <p>After a bit more searching, I realised that I could solve this be using <code>CREATE TYPE</code> and then using the <code>CAST</code> function to change the type of the nested table. Which worked.</p>
  
  <p>This was using <code>CREATE TYPE number_ntt as TABLE OF NUMBER;</code> and replacing <code>collect(colb)</code> with <code>cast(collect(colb) as number_ntt)</code></p>
  
  <p>I then tried to use a nested table type created in a package, since I only need this type to be available for one particular query in one procedure in a single package. And I couldn't get it to work.</p>
  
  <pre><code>create or replace package mike_temp_pkg as
    type number_ntt is table of number;
  end mike_temp_pkg;
  </code></pre>
  
  <p>And this time replacing <code>collect(colb)</code> with <code>cast(collect(colb) as mike_temp_pkg.number_ntt)</code></p>
  
  <p>This resulted in ORA-00932: invalid datatype.</p>
  
  <p>So the question I have is in two parts really:</p>
  
  <ol>
  <li><p>Why does the system generated user
  defined type work for the <code>select</code>
  but not for the <code>member of</code>?</p></li>
  <li><p>Why does the type need to be a SQL
  type and not a PL/SQL type in a
  package? I don't really define types
  that often so there might be a
  simple answer to that question.</p></li>
  </ol>