Oracle - In CLAUSE question when using with multiple values, making it dynamic

Question

I just spent an hour on google and here trying to get a straight answer for how to do this in Oracle. What I need is the ability to use select in clause that constructed automatically such as

select col1 from table1 where id.table IN ('1','2','3');

where the id values are passed to the stored procedure inside the array. The associative array has been defined as such:

 TYPE varchar_array_type IS TABLE OF VARCHAR2 (40)      INDEX BY BINARY_INTEGER;

Is there a simple, concrete way to do that? thanks

Answer 1

Unfortunately, if your collection type is defined in PL/SQL (rather than SQL), you cannot use it in SQL because the SQL engine doesn't know how to handle it.

If instead you defined the collection type in SQL, i.e.

CREATE TYPE varchar_tbl
    IS TABLE OF varchar2(40);

Then you can do something like

SELECT col1
  FROM table1 t1
 WHERE t1.id IN (SELECT column_value
                   FROM TABLE( <<variable of type varchar2_tbl>> ) )

depending on the Oracle version-- the syntax for using collections in SQL has evolved over time-- older versions of Oracle had more complex syntax.

You can convert a PL/SQL associative array (your VARCHAR_ARRAY_TYPE) to a SQL nested table collection in PL/SQL, but that requires iterating through the associative array and filling the nested table, which is a bit of a pain. Assuming that the VARCHAR_TBL nested table collection has been created already

SQL> CREATE OR REPLACE TYPE varchar_tbl
         IS TABLE OF varchar2(40);

you can convert from the associative array to the nested table and use the nested table in a SQL statement like this (using the SCOTT.EMP table)

declare
  type varchar_array_type
    is table of varchar2(40)
       index by binary_integer;
  l_associative_array varchar_array_type;
  l_index             binary_integer;
  l_nested_table      varchar_tbl := new varchar_tbl();
  l_cnt               pls_integer;
begin
  l_associative_array( 1 ) := 'FORD';
  l_associative_array( 10 ) := 'JONES';
  l_associative_array( 100 ) := 'NOT A NAME';
  l_associative_array( 75 ) := 'SCOTT';
  l_index := l_associative_array.FIRST;
  while( l_index IS NOT NULL )
  loop
    l_nested_table.EXTEND;
    l_nested_table( l_nested_table.LAST ) :=
             l_associative_array( l_index );
    l_index := l_associative_array.NEXT( l_index );
  end loop;
  SELECT COUNT(*)
    INTO l_cnt
    FROM emp
   WHERE ename IN (SELECT column_value
                     FROM TABLE( l_nested_table ) );
  dbms_output.put_line( 'There are ' || l_cnt || ' employees with a matching name' );
end;

Because converting between collection types is a bit of a pain, however, you would generally be better off just using the nested table collection (and passing that to the stored procedure) unless there is a particular reason that the associative array is needed.