Oracle - in CLAUSE question when used with multiple values, making it dynamic

Question

I just spent an hour on google and tried to get a direct answer on how to do this in Oracle. I need the ability to use the select in function, which is automatically created, for example

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

where id values ​​are passed to the stored procedure inside the array. An associative array is defined as such:

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

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

Answer 1

Unfortunately, if your collection type is defined in PL / SQL (not SQL), you cannot use it in SQL because the SQL engine does not know how to handle it.

If instead you defined a 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 version of Oracle - the syntax for using collections in SQL has evolved over time - older versions of Oracle have a more complex syntax.

You can convert the PL / SQL associative array (your VARCHAR_ARRAY_TYPE) into a nested SQL table table in PL / SQL, but this requires iterating through the associative array and populating the nested table, which is a little painful. Assuming VARCHAR_TBL collection of nested tables has already been created

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

you can convert from an associative array to a nested table and use the nested table in an 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; 

Since converting between collection types is a bit of a pain, you would usually be better off using only a nested collection of tables (and passing it to a stored procedure) unless there is a special reason why an associative array is needed.