SQL>
SQL> create or replace type myTableType
2 as table of number;
3 /
Type created.
SQL>
SQL> create or replace
2 function str2tbl( p_str in varchar2 ) return myTableType
3 as
4 l_str long default p_str || ',';
5 l_n number;
6 l_data myTableType := myTabletype();
7 begin
8 loop
9 l_n := instr( l_str, ',' );
10 exit when (nvl(l_n,0) = 0);
11 l_data.extend;
12 l_data( l_data.count ) :=
13 ltrim(rtrim(substr(l_str, 1, l_n - 1)));
14 l_str := substr( l_str, l_n+1 );
15 end loop;
16 return l_data;
17 end;
18 /
Function created.
SQL>
SQL> variable bind_variable varchar2(30)
SQL> exec :bind_variable := '1,3,5,7,99'
PL/SQL procedure successfully completed.
BIND_VARIABLE
--------------------------------
1,3,5,7,99
SQL> select * from TABLE ( cast ( str2tbl(:bind_variable) as myTableType ) )
2 /
COLUMN_VALUE
------------
1
3
5
7
99
SQL>
SQL> drop type myTableType;
Type dropped.