Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select from PLSQL Associative array?

Tags:

plsql

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

like image 620
ashtame Avatar asked Oct 18 '10 17:10

ashtame


2 Answers

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));
like image 114
Jeffrey Kemp Avatar answered Sep 20 '22 14:09

Jeffrey Kemp


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
like image 22
andr Avatar answered Sep 18 '22 14:09

andr