Re: SQL question: function output appended to WHERE clause

On 21 May 2004 11:37:00 -0700, sharkdba_at_yahoo.com (Sharkie) wrote:

>Hello,
>
>I'm using Oracle 8.1.7 on a Solaris box.
>This might be a simple problem, not sure, but I really need this to work.
>
>Supposedly I have a temp table:
>SQL> desc temp
> Name Type
> --------- ----------------
> ID NUMBER(38)
> NAME VARCHAR2(100)
>
>and some sample values:
>
>SQL> select * from temp;
> ID NAME
>---------- ----------------
> 1 one
> 2 second value
> 3 three
> 4 fourth
> 5 five
>...
>
>I can easily select the rows I need manually through:
>select * from temp where id in (1,2,5);
>
>Now, let's say I have a function which returns '1,2,5'.
>
>SQL> select f_temp from dual;
>1,2,5
>
>Can I use this function to get the same results?
>These are not working:
>
>select * from temp where id in (select f_temp_from dual);
>select * from temp where id in f_temp;
>select * from temp where id in (f_temp);
>
>I get ORA-01722: invalid number, which I understand, since the returned value
>is a string, not a series of numbers. However having the function return
>a set of values as a string above, is it possible to use that in the query
>from temp table? Or any other solutions?
>
>btw. if the above works if the function returns only one number.

You could try CASTing your output to the TABLE type. Also dbms_utility has a comma_to_table, and table_to_comma procedure.

Usually using functions in selects, especially if the functions have their own selects is a safe recipe for performance problems, as the function's SELECT is considered recursive SQL and will *never* get merged in the main query

And you don't need temp tables in Oracle.

--
Sybrand Bakker, Senior Oracle DBA