4

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

sarsnake
  • 26,667
  • 58
  • 180
  • 286

1 Answers1

5

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • thanks, that's the easy part. how do I dump my array values into that varchar_tbl? I tried few things, all giving me errors. This should be easy but for some reason is horrifically awkward. Ugh. – sarsnake Dec 15 '10 at 23:41
  • also, please explain what you mean by "if your collection type is defined in PL/SQL (rather than SQL), you cannot use it in SQL". Everything we do is inside stored procedures. Please keep that in mind, thanks. – sarsnake Dec 15 '10 at 23:44
  • @gnomixa - Posted an example of converting between collection types. You can use collection types defined in SQL (i.e. CREATE TYPE) in both SQL and PL/SQL since PL/SQL is built on top of SQL. You can only use collection types defined in PL/SQL in PL/SQL-- you cannot use those types in SQL. – Justin Cave Dec 16 '10 at 00:14
  • thanks for your code. But still not helping. How do I create a temporary table INSIDE my stored procedure(or rather DEFINE the temporary table type)? Is that possible? Or do I have to do through the SQL prompt. thanks again. Can't I just easily create the temporary table in my stored proc??? – sarsnake Dec 16 '10 at 00:21
  • You cannot create the SQL collection type in PL/SQL (barring heroic effort and a ton of dynamic SQL & PL/SQL). You need to create the SQL collection type once as a first-order object and then you can reference it in whatever stored procedures you'd like. You can declare an instance of the SQL collection type in your stored procedure just as I did here in my anonymous PL/SQL block (l_nested_table is an instance of the varchar_tbl collection type). – Justin Cave Dec 16 '10 at 00:26