CODEWITHSUNDEEP
sqloracle-database
aggietech
aggietech

Reputation: 978

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

Upvotes: 1

Views: 3928

Answers (1)

Harrison
Harrison

Reputation: 9090

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

Upvotes: 3

Related Questions