Hi,Tom

I read your answer as follows ...



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


create or replace
function in_list( p_string in varchar2 ) return myTableType
as
l_string long default p_string || ',';
l_data myTableType := myTableType();
n number;
begin
loop
exit when l_string is null;
n := instr( l_string, ',' );
l_data.extend;
l_data(l_data.count) :=
ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
l_string := substr( l_string, n+1 );
end loop;

return l_data;
end;


select * from all_users where username in
( select *
from THE ( select cast( in_list('OPS$TKYTE, SYS, SYSTEM')
as mytableType ) from dual ));


=========
It returns two rows of SYS and SYSTEM.

I think the next one would return one row but nothing.

this one returns null
select * from all_users where username in
( select *
from THE ( select cast( in_list('OPS$TKYTE, SYS, SYSTEM')
as mytableType ) from dual ) where rownum =1);

But the same query (I think) return one. why is that?

select * from all_users where username in
(
select *
from THE (
select cast( in_list('TKYTE, SYS, SYSTEM') as mytableType) from dual
)
where rownum =1
);