Oracle: How to create a function returning values

Question

I have a problem which I can't solve. Maybe you have an idea about how to solve it.

I do have a given parameter-table like this:

P_VALUE     P_NAME 
----------- ---------- 
X85         A_03      
XH1         A_04      
XH2         A_04      
XH3         A_04           
C84         A_05      

As you can see there are parameters with multiple entries. At the moment this parameters are used in this way:

SELECT * FROM tablex
WHERE code IN (SELECT p_value 
               FROM parameter_table
               WHERE p_name LIKE 'A_04');

As the query is very big these parameter sub-select are used very often. I was trying to implement a function in Oracle to get my parameters. This works very fine as long as there is just 1 row per parameter. When I want to use it in "IN-Statements", it won't work because functions just return a single value.

--WORKS
SELECT * FROM tablex
WHERE code = (f_get_param('A_03'));

--DOES NOT WORK
SELECT * FROM tablex
WHERE code IN (f_get_param('A_04'));

Please note that I need it for plain SQL statements, so procedures won't work as they are just good for PL/SQL.

I would be really thankful for good ideas or help!

Answer 1

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 tablex
WHERE 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

Answer 2

I think the actual solution to your problem is to simply join the two tables and create the appropriate indexes rather than invoking a PL/SQL function at all:

SELECT x.* FROM tablex x, parameter_table p
   WHERE x.code = p.p_value
   AND p.p_name LIKE '%A_04%';

Note that you also have a semantic error in your LIKE clause. You're not using the % sign therefore your LIKE 'A_04' is just the same as = 'A_04'

Answer 3

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

Answer 4

--DOES NOT WORK

SELECT * FROM tablex
WHERE code IN (f_get_param('A_04'));

-- Try this
SELECT * FROM tablex
WHERE 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.