PLS-00642: local collection types not allowed in SQL statements

Oracle Error : PLS-00642: local collection types not allowed in SQL statements

Cause: A locally-defined (i.e. not schema level) collection type was used in a SQL statement. The type must be defined in a schema to be accepted in a SQL statement.

Action: Define the collection type in your schema, not inside a PL/SQL subprogram.

It'll look like this:

ops$tkyte-at-ORA817DEV.US.ORACLE.COM> create or replace type myTableType 2 as
table of number
3 /

Type created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte-at-ORA817DEV.US.ORACLE.COM>
ops$tkyte-at-ORA817DEV.US.ORACLE.COM> create or replace 2 function in_list( p_string in
varchar2 ) 3 return myTableType 4 as 5 l_string long default p_string || ','; 6
l_data myTableType := myTableType(); 7 n number; 8 begin 9 loop 10 exit when l_string
is null; 11 n := instr( l_string, ',' ); 12 l_data.extend; 13 l_data(l_data.count) :=
substr( l_string, 1, n-1 ); 14 l_string := substr( l_string, n+1 ); 15 end loop; 16
17 return l_data; 18 end;
4 /

Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte-at-ORA817DEV.US.ORACLE.COM> variable str
varchar2(4000); ops$tkyte-at-ORA817DEV.US.ORACLE.COM> exec :str := '1,3,5'

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte-at-ORA817DEV.US.ORACLE.COM> select * 2 from
TABLE( cast( in_list(:str) as myTableType ) )
5 /

COLUMN_VALUE TESTTESTTESTTEST 1 3 5

ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte-at-ORA817DEV.US.ORACLE.COM> select * 2 from
all_users 3 where user_id in ( select * from TABLE( cast( in_list(:str) as
myTableType ) ) )
6 /

USERNAME USER_ID CREATED

SYSTEM 5 28-AUG-01

ops$tkyte-at-ORA817DEV.US.ORACLE.COM>

the type MUST be visible to the SQL engine. You must use the CREATE TYPE, it'll not work to hide the type in the package.