9

Is it possible to use SELECT FROM when using an associative array? I'm passing an array to a stored procedure through a .NET application, and I wanna be able to use that array as a condition when selecting from another table. Lets say I'm passing an array of IDs to the procedure, I wanna be able to do this:

select * from table1 where userID in (select column_value from array)

The type for the array is defined in the package:

type id_array is type of number index by pls_integer

ashtame
  • 91
  • 1
  • 1
  • 2

3 Answers3

16

Yes, it is possible, by wrapping the array with a pipelined function. Here's a good primer on pipelined functions:

http://www.oracle-developer.net/display.php?id=429

UPDATE: Oracle 12c now supports querying associative arrays using the TABLE operator, as long as the type is declared in a package spec: https://galobalda.wordpress.com/2014/08/02/new-in-oracle-12c-querying-an-associative-array-in-plsql-programs/

e.g.

select * from table1
where userID in (select column_value from table(array));
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
2

No, you can't select from PL/SQL arrays, since you use SQL in select from statements, though you can use DB defined Nested Tables types in SQL. This short article can help you get started.

Take a look a this simple synthetic exmple:

> create type temp_t as table of int;/   
Type created.
> select 'test' from dual where 1 in (select * from table(temp_t(1,2,3)));

'TES
----
test
andr
  • 1,626
  • 8
  • 10
  • So there's no way of accessing an associative array at all? Is it only used to pass data? – ashtame Oct 18 '10 at 18:51
  • is it possible to cast an associative array as a table? – ashtame Oct 18 '10 at 19:17
  • @ashtame not there's no way you can use associative array in SQL, only nested tables and varrays allowed. – andr Oct 19 '10 at 06:40
  • the issue is that i'm passing an associative array from vb.net to the stored procedure. Is there a way to cast an associative array into a table, or maybe a nested table? – ashtame Oct 19 '10 at 18:17
  • 1
    @ashtame If the indices in your associative array are not crucial for you then for sure you can make a nested table out of AA: just iterate through your array and populate a nested table collection in PL/SQL function – andr Oct 20 '10 at 06:44
-1

An example using PLSQL (to select from a nested table):

create type temp_r as OBJECT(
   temp_varchar2 varchar2(100),
   temp_number number(20)
   );
/

create type temp_t as TABLE of temp_r;
/   

set serveroutput on size 1000000
/

-- PLSQL starts here
declare
  temp_rec   temp_r := temp_r(null, null); -- empty constructor to initialize object
  temp_table temp_t := temp_t();           -- empty constructor to initialize object
  lv_ref_cursor     SYS_REFCURSOR; 

  lv_temp_varchar2 varchar(100);
  lv_temp_number   number(20);

begin
  temp_rec.temp_varchar2 := 'first';
  temp_rec.temp_number := 1;

  temp_table.extend;
  temp_table(1) := temp_rec;
  temp_table.extend;
  temp_table(2) := temp_r('second', 2);


     OPEN lv_ref_cursor FOR
        SELECT temp_varchar2, temp_number
        FROM   table(temp_table)
        where  temp_number = 1;

     fetch lv_ref_cursor into lv_temp_varchar2, lv_temp_number;
     close lv_ref_cursor;

  dbms_output.put_line('returns: ' || lv_temp_varchar2 || ', ' || lv_temp_number);

end;
/
Jason
  • 81
  • 1
  • 4