Hi Tom!

I encountered a problem using PL/SQL collections in DML statements inside stored procedure, please find complete test case in provided LiveSQL.

In the test package there are two, very similar procedures, which firstly collect some IDs into associative array and then use this array in sub-select of other SQL statements:
- TEST_OK procedure performs SELECT statements - works fine
- TEST_NOK procedure which tries to do a DELETE: DELETE FROM ali_test_tab WHERE ID IN (SELECT COLUMN_VALUE FROM TABLE(t2)) fails with runtime error ORA-00902.

I use exactly the same sub-select in both procedures, so I don't understand why it doesn't work in DELETE (in UPDATE it fails as well). I already tried to explicitly convert datatypes - but it didn't help.

I found a workaround - define type on schema level, not on package level - but I prefer to keep everything within this package, so I would like to kindly ask you for help. Perhaps I overlook some obvious issue, but I can't understand why this works for SELECT but not for DELETE/UPDATE.

Thank you in advance for any help!

Best regards,
Andrzej

create table t (
  x int
);

insert into t 
  select rownum from dual connect by level <= 5;
commit;

create or replace package pkg as
  type tp is table of number index by pls_integer;
end pkg;
/

declare
  arr pkg.tp;
begin
  arr(1) := 1;
  arr(3) := 2;
  arr(2) := 3;
  delete t
  where  x in (select column_value from table(arr));
end;
/

ORA-00902: invalid datatype
ORA-06512: at line 7
00902. 00000 -  "invalid datatype"

create or replace type tp is table of number;
/

declare
  arr tp := tp(1, 2, 3);
begin
  delete t
  where  x in (select column_value from table(arr));
end;
/

PL/SQL procedure successfully completed.

select * from t;

X  
4  
5