-
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
PROCEDURE FOO_TEST_SP(
ID_LIST_IN IN DBMS_UTILITY.NUMBER_ARRAY
)
AS
TYPE a_typ IS TABLE OF FOO_TEST.BATCH_ID%TYPE;
BATCH_IDS a_typ;
BEGIN
SELECT DISTINCT BATCH_ID
BULK COLLECT INTO BATCH_IDS
FROM FOO_TEST
WHERE HOLDING_ID IN ID_LIST_IN;
END;
The "WHERE HOLDING_ID IN ID_LIST_IN" clause is blowing up.
How do I make this work?
-
indexed position - code snippet below
Code:
FOR i IN l_tab.first .. l_tab.last LOOP
UPDATE forall_test
SET ROW = l_tab(i)
WHERE id = l_tab(i).id;
END LOOP;
-
???
stecal,
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|