prb with table(cast .. as ..)

i'm trying the following (oracle 8.1.6 on linux):

drop table t;
create table t (x number);
insert into t values (1);
insert into t values (2);
commit;
create or replace type ids as table of number;
/

declare
tabids ids := ids();
begin
tabids.extend();
tabids(1) := 2;
tabids.extend();
tabids(2) := 3;

insert into t (x)
select column_value
from table(cast(tabids as ids)) myids
where myids.column_value not in (select x from t); end;
/

select * from t;

the idea is that i have a table with two rows, values of 1 and 2 for the single column. then using an insert .. select from the array casted to a table, but i only want to insert the entries of the array that do not exist in the table. the final select in my case yields

SQL> select * from t;

         X

         1
         2

i would have expected the 3 to be in there but for some reason, the where clause in the insert .. select .. does not return true for any entry in the casted array. if i use the following instead:

insert into t (x)
select column_value
from table(cast(tabids as ids)) myids
minus
select x from t;
end;

i get the behavior that i expect. any ideas? thanks in advance. Received on Thu Feb 13 2003 - 16:38:05 CST