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
selec开发者_开发百科t 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:
Why does the system generated user defined type work for the
select
but not for themember of
?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.
(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).
精彩评论