I have a function that returns a comma deliminated list of numbers in single quotes. If i select myfunction() from dual i get the following:
What i need to do is select code which is a number from a table where the code value is in the result list myfunction() returns. Of course i get the error Invalid number. I have tried casting it the code in the select but then i find no results.
1) What i do and i get invalid number exception, because code is numeric and myfunction() return a string '1','2','3','4'
select * from mytable where code in (select myfunction() from dual);
2) What i tried to overcome it, no exception but no results show:
select * from mytable where to_char(code) in (select myfunction() from dual);
Is there some way to make this select work?