pl/sql tables in where clause
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: pl/sql tables in where clause

  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Question 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)?

  2. #2
    Join Date
    Nov 2003
    Posts
    4
    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)?

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have a look at this AskTom thread. It should help you on your way.

    http://asktom.oracle.com/pls/ask/f?p...#1655409116816
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width