create table range
(para varchar2(100),
para varchar2(100)
);

 insert into range (vchar, para) values ('1,2,3,4,5,6,7,8,10' , 'catida');

Select vchar
  into mcatidA
  from range
  where para='catida';


--mcatid is numeric having value 5 in it

if to_char(mcatid) in (mcatida) then         

 return(1);
else
return(2);
end if;

CREATE OR REPLACE FUNCTION CATID_YN (msource char, mtarget char) return number is
mans number;
mcatidA varchar2(50);
begin
	
if mtarget='A' then
  Select ltrim(rtrim(vchar))
  into mcatidA
  from range
  where para='catida';
 

end if;

Select nvl(count(*),0)
into mans
from (
	select distinct regexp_substr(mcatidA,'[^,]+', 1, level) str
	from dual 
	connect by regexp_substr(mcatidA, '[^,]+', 1, level) is not null)
where str=msource;


return(mans);

end;
/