Using array values in SQL

[sam93]

Hi,

I want to pass an array of values (say product #s) from Java front end. The number of values in the array is not fixed. I can receive these values in my Oracle backend through a table type used as parameter in a procedure. The question is how can I then use these values in a SQL query. I need to execute a query as follows: SELECT * FROM PRODUCTS WHERE PRODUCT_NO IN (list of values passed in from the array). The way I see it possible is to put this query in a loop:
LOOP
SELECT * FROM PRODUCTS WHERE PRODUCT_NO = <next value in the PL/SQL table>
END LOOP

Is there a more elegant way to do it?

Thanks.

 

[lewisp]

You could create a comma-delimited string containing all your values, then use a reference cursor.

DECLARE
  cur_typ IS REF CURSOR;
  your_cur cur_typ;
BEGIN
  OPEN your_cur FOR 'SELECT * FROM PRODUCTS WHERE PRODUCT_NO IN (' ||
                    your_string_variable ||
                    ')';
.
.
.

Note that your string variable will be limited to 32767 chars.