passing an array into oracle sql and using the array

Question

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).

Harrison · Accepted Answer · 2011-02-21 18:59:38Z

3

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));

answered Feb 21, 2011 at 18:59

Harrison

9,1401 gold badge34 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

passing an array into oracle sql and using the array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related