-
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)?
-
Hmmm, the queries didn't come through quite right, here it goes again:
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 _pl/sql table_
AND _some other criteria_
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 _some other criteria_
FOR R1 IN C1 LOOP
LOOP over _pl/sql table_
IF R1.column1 = _pl/sql table_.value THEN
_valid row_
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 _some other criteria_ 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 _some other criteria_
OPEN C1 (DBMS_UTILITY.table_to_comma(_pl/sql table_))
FETCH C1 ...;
CLOSE C1;
In other words, I convert the _pl/sql table_ 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 (without resorting to Dynamic SQL)?
-
Have a look at this AskTom thread. It should help you on your way.
http://asktom.oracle.com/pls/ask/f?p...#1655409116816
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|