Oracle: How to create a function returning values for a "SELECT * FROM tab WHERE name IN (function())"

Use collections. Here you have an example http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html

Technically you can achieve using the function this way but doing this will cause index not to be used on code column on tablex and may affect performance .Using function index you can reduce performance impact

 CREATE OR REPLACE FUNCTION f_get_param(p_value1 IN VARCHAR2,p_name1 in VARCHAR2) return NUMBER DETERMINISTIC IS  l_count NUMBER; BEGIN  select count(1) into l_count from parameter_table where p_value =p_value1  and p_name=p_name1; if l_count > 0 then  return 1; else return 0; end if; end f_get_param;

AND use the select statement like this

SELECT * FROM tablexWHERE f_get_param(code,'A_04')=1;

EDIT 1:-Also to reduce the performance impact in database 10.2 and greater If the parameter_table is static you can use the DETERMINISTIC clause in the Function to say that the function returns the same value if called with same parameters every time

Please find the link on the article about using functions in SELECT statement

--DOES NOT WORKSELECT * FROM tablexWHERE code IN (f_get_param('A_04'));-- Try thisSELECT * FROM tablexWHERE code IN (select * from TABLE(f_get_param('A_04')));

You have to "CAST" a collection onto SQL TABLE.Also when you use cast you can also use inner joint:

SELECT * FROM tablex join TABLE(f_get_param('A_04') using (code);

I think - generally - your problem is called "Dynamic where clause". Try to search some articles about it on AskTom.