passing an array into oracle sql and using the array

I am running into the following problem, I am passing an array of string into Oracle SQL, and I would like to retrieve all the data where its id is in the list ...

here开发者_运维知识库's what i've tried ...

OPEN O_default_values FOR 
SELECT  ID AS "Header",
        VALUE AS "DisplayValue", 
        VALUE_DESC AS "DisplayText"
FROM TBL_VALUES
WHERE ID IN I_id;

I_id is an array described as follows - TYPE gl_id IS TABLE OF VARCHAR2(15) INDEX BY PLS_INTEGER;

I've been getting the "expression is of wrong type" error. The I_id array can sometimes be as large as 600 records. My question is, is there a way to do what i just describe, or do i need to create some sort of cursor and loop through the array?

What has been tried - creating the SQL string dynamically and then con-cat the values to the end of the SQL string and then execute it. This will work for small amount of data and the size of the string is static, which will caused some other errors (like index out of range).

---

have a look at this link: http://asktom.oracle.com/pls/asktom/f?p=100:11:620533477655526::::P11_QUESTION_ID:139812348065

effectively what you want is a variable in-list with bind variables.

do note this:

"the" is deprecated. no need for it today.

TABLE is it's replacement

select * from TABLE( function );

---

since you already have the type, all you need to do is something similar to below:

OPEN O_default_values FOR 
SELECT  ID AS "Header",
        VALUE AS "DisplayValue", 
        VALUE_DESC AS "DisplayText"
FROM TBL_VALUES
WHERE ID IN (select column_value form  table(I_id));