How can I collect multiple values into an array and later use the array with IN clause

Question

I need to do this inside a stored procedure in Oracle 10g/11g:

SELECT COLUM1 INTO SOME_ARRAY FROM TAB1 WHERE COLUMN2=P_COL2;

And then I want to be able to use that SOME_ARRAY in another query with IN clause, like this:

SELECT * FROM TAB2 WHERE TAB2.COLUMN1 IN SOME_ARRAY

Is this possible without needing to create a TYPE? Maybe a dynamic array? A little code will be appreciated.

TYPE is the Dynamic Array you look for.! or simply a sub-query? — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Jul 1, 2014 at 10:55
By sub-query you mean SELECT * FROM TAB2 WHERE TAB2.COLUMN1 IN (SELECT COLUM1 FROM TAB1 WHERE COLUMN2=P_COL2)? If so, I don't like that. Then could you please tell me how I would go the TYPE way? — Mikayil Abdullayev
– Mikayil Abdullayev, Commented Jul 1, 2014 at 11:00
Probably not possible, you would run into a PLS-00642 error, see dba-oracle.com/… — Erich Kitzmueller
– Erich Kitzmueller, Commented Jul 1, 2014 at 11:09

Maheswaran Ravisankar · Accepted Answer · 2014-07-01 11:18:33Z

1

Let's create a Type as an Oracle Object to be reference from both PL/SQL and SQL

CREATE TYPE SOME_ARRAY_REC AS OBJECT
(
   COLUM1 VARCHAR2(100)
);
/

Now, DB object as nested table of the type we created above

CREATE TYPE SOME_ARRAY_TAB AS TABLE OF SOME_ARRAY_REC;
/

The PL/SQL

DECLARE
      PL_ARRAY_REC TAB1%ROWTYPE;
      TYPE PL_ARRAY_TAB IS TABLE OF PL_ARRAY_REC  
                INDEX BY PLS_INTEGER;

      v_PL_ARRAY PL_ARRAY_TAB;

      v_ARRAY SOME_ARRAY_TAB := SOME_ARRAY_TAB();
BEGIN
      /* Bulk collect the records into local PL/SQL type */
      SELECT COLUM1 
      BULK COLLECT INTO v_PL_ARRAY
      FROM TAB1 WHERE COLUMN2=P_COL2;

      /* To use it in SQL we have to use a SQL compatible Type*/
      /* So copy the contents from local type to SQL compatible type */
      FOR I IN 1..v_PL_ARRAY.COUNT
      LOOP
           v_ARRAY.EXTEND;
           v_ARRAY(I) := SOME_ARRAY_REC(v_PL_ARRAY(I).COLUM1);
      END LOOP;

      /* Use TABLE() to cast the SQL compatible Nested table as normal table */
      SELECT * BULK COLLECT INTO SOME_VARRAY 
      FROM TAB2
      WHERE COLUMN1 IN (SELECT COLUM1 FROM TABLE(v_ARRAY));

END;
/

PS: I would rather prefer the SubQuery

answered Jul 1, 2014 at 11:18

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

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

1 Comment

Mikayil Abdullayev Over a year ago

I know, I would too, but it takes eternity to complete when I use subquery.

Collectives™ on Stack Overflow

How can I collect multiple values into an array and later use the array with IN clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related