DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to use collection in WHERE clause

  1. #1
    Join Date
    May 2011
    Posts
    2

    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?

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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;

  3. #3
    Join Date
    May 2011
    Posts
    2

    ???

    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
  •  


Click Here to Expand Forum to Full Width