Splitting a string into an array using Oracle

Took this from :

http://www.orafaq.com/forum/t/11692/0/

Suppose you want to say :

select * from oss_cell_view where cell in (:cells)

Running this query in sql developer you get a dialog as:

But here is what sql developer does. Seeing that it is a string input it turn the query into:

select * from oss_cell_view where cell in ('asdf,fdsa')

Whereas what you expect is :

select * from oss_cell_view where cell in ('asdf','fdsa')

(notice the ',' in the middle :) )

Here is the solution:

Create a simple table type:

create or replace type myTableType as table of varchar2(255);


Create a string splitting function (default delimiter is , ):

create or replace function str2tbl
(p_str in varchar2,
p_delim in varchar2 default ',')
return myTableType
as
l_str long default p_str || p_delim;
l_n number;
l_data myTableType := myTabletype();
begin
loop
l_n := instr( l_str, p_delim );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+length(p_delim) );
end loop;
return l_data;
end;




You can test the newly created function:

SELECT column_value

FROM TABLE (CAST (str2tbl ('10,01,03,04,234') AS mytabletype))

Finally you can rewrite your query as :
select * from oss_cell_view where cell in (SELECT column_value FROM TABLE (CAST(str2tbl (:cells) AS mytabletype)))


Enjoy!