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 functionselect * from all_users
where user_id in ( select *
from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) )
Posts
