How to use collection in WHERE clause
I have a new procedure I'm trying to get working on Oracle 11g, working with table FOO_TEST
CREATE TABLE FOO_TEST (
"HOLDING_ID" NUMBER(10,0) NOT NULL ENABLE,
"BATCH_ID" NUMBER(10,0) NOT NULL ENABLE
create or replace
ID_LIST_IN IN DBMS_UTILITY.NUMBER_ARRAY
TYPE a_typ IS TABLE OF FOO_TEST.BATCH_ID%TYPE;
SELECT DISTINCT BATCH_ID
BULK COLLECT INTO BATCH_IDS
WHERE HOLDING_ID IN ID_LIST_IN;
The "WHERE HOLDING_ID IN ID_LIST_IN" clause is blowing up.
How do I make this work?
indexed position - code snippet below
FOR i IN l_tab.first .. l_tab.last LOOP
SET ROW = l_tab(i)
WHERE id = l_tab(i).id;
You lost me there. I know how to loop over the number_array but don't see how I'm going to get a distinct result set out of a loop construct.
Isn't there a way to cast the number_array or make Oracle see it as a table it can do a subselect on or something similar?
Click Here to Expand Forum to Full Width