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
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));
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
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;
/
