Alternative for sending Multiple values to Oracle IN Clause

Usually when connecting we have a query which has a
IN clause and it requires multiple values then you may need to go for creating a
String of all values and pass it.

Solution 1: You can try INSTR function of ORACLE
which can check for a value inside the comma separated String
value.

SELECT myid
FROM
mytable
WHERE INSTR(stringOfnumbers,','|| myid ||',') !=
0;

This solution may not be faster as it will not use
index for the myid column.

Solution 2: Create a PL/SQL function (say
str2table ) which converts comma separated String value to a table.
To store
the data in a table we need to create a data type as table type and then use the
same type as return type for our function.

create or replace type
myTableType as table of number;

create or replace function str2tbl(
p_str in varchar2 ) return myTableType

as
l_str long default
p_str || ',';

l_n
number;
l_data
myTableType := myTabletype();

begin

loop
l_n
:= instr( l_str, ','
);
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+1 );
end
loop;
return l_data;

end;

Here is how you can use above function

select * from all_users
where
user_id in ( select *
from THE ( select cast(
str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual )
)

passing values to IN clause, JDBC values to IN clause, passing number to IN clause, list of numbers in IN clause