pl/sql tables in where clause
Hi,
This is a very common requirement I am sure so I don't know why Oracle doesn't allow us to do this but here it goes:
I have an SQL statement that ideally should have the following construct
SELECT id
FROM table
WHERE column1 IN
AND
The pl/sql tables content varies and is passed to a function that then executes the query and returns the first valid row.
However, as mentioned, this is not supported so my first solution was to do the following
CURSOR C1 IS
SELECT id, column1
FROM table;
WHERE
FOR R1 IN C1 LOOP
LOOP over
IF R1.column1 = .value THEN
END IF;
END LOOP;
END LOOP;
RETURN valid_row;
In other words I just have the query return more rows and then filter out the invalid ones in my procedure. This works quite well as the are already very selective and it will return almost never more than 2 rows. However, it was pointed out to me that this is a bit insane programming and it needed to be done different, i.e. the query should select only the valid rows from the start and no extra processing should be needed so I wrote the following:
CURSOR C1 (p_list VARCHAR2) IS
SELECT id,
FROM table;
WHERE INSTR (p_list, column1) <> 0
AND
OPEN C1 (DBMS_UTILITY.table_to_comma())
FETCH C1 ...;
CLOSE C1;
In other words, I convert the into a comma seperated list (it is a bit more complicated then in the example but you should get the gest) and then pass this a VARCHAR2 to my query. The INSTR function then looks to see if the columns value matches any of the chars in my VARCHAR list. Again this works fine, and it seems to be more elegant than my first attempt, however this is twice as slow as my first example (selecting all and looping over them) and I have no idea why, anybody any ideas?
Also, is there maybe another way of dealing with queries that need PL/SQL Tables in the where criteria (not resorting to Dynamic SQL)?
