| collection in where clause [message #39833] |
Thu, 15 August 2002 06:18  |
B. Post
Messages: 1
Registered: August 2002
|
Junior Member |
|
|
Hi,
I'm looking for a way to create a procedure (collectionA in , collectionB out) that returns a collection based on a query that uses an input-collection.
Something like this:
open collectionB for
select *
from table
where table.column1 in (collectionA);
Is this possible?
If so, how can this be accomplished?
|
|
|
|
| Re: collection in where clause [message #39834 is a reply to message #39833] |
Thu, 15 August 2002 08:56  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
For the IN operator, you'll need to first define a SQL type for that collection:
create or replace type tNumberTableType as table of number;
and then:
where column1 in
(select column_value
from table(cast(collectionA as tNumberTableType)));
To populate collectionB, you can use a bulk collect (as long as that collection is scalar - one-column, not a record). So, your query ends up looking something like:
select some_column bulk collect
into collectionB
from t
where column1 in
(select column_value
from table(cast(collectionA as tNumberTableType))); |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
