Search code examples
oracleoracle10gora-00932

Oracle COLLECT function and types

I've got an issue with Oracle 10g and the use of the COLLECT 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 member of condition.

Initially I wrote the code shown below, which came back with the error "ORA_00932: inconsistent datatypes: expected UDT got -".

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

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, (where 2 member of colb_vals) then the code will run and show the data retrieved included the temporary UDT (named SYSTPblahblahblah==).

After a bit more searching, I realised that I could solve this be using CREATE TYPE and then using the CAST function to change the type of the nested table. Which worked.

This was using CREATE TYPE number_ntt as TABLE OF NUMBER; and replacing collect(colb) with cast(collect(colb) as number_ntt)

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.

create or replace package mike_temp_pkg as
  type number_ntt is table of number;
end mike_temp_pkg;

And this time replacing collect(colb) with cast(collect(colb) as mike_temp_pkg.number_ntt)

This resulted in ORA-00932: invalid datatype.

So the question I have is in two parts really:

  1. Why does the system generated user defined type work for the select but not for the member of?

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

Solution

  • (1)

    The COLLECT function documentation states "To get the results of this function you must use it within a CAST function." I suspect it simply is not designed to support any uses, except a simple dump of its contents, unless you cast it to a defined type.

    (2)

    The SQL parser has no knowledge of or access to types defined in PL/SQL blocks. Even when you execute a SQL statement inside of PL/SQL code, the statement is essentially handed off to an independent parser (with PL/SQL variable names replaced by bind variable placeholders).