Re: Bulk Collect into Associative Array question

Jack Addington wrote:
>
> I have an associative array full of primary keys from a table. I need to
> populate two other arrays with other column data from the same table. Is
> there a bulk collect syntax I can use that uses my existing array of keys?
>
> I have it working using the following:
>
> for idx in atbl_field.first .. atbl_field.last loop
> select f.field_short_name,
> f.field_value_type
> into ltbl_field_name(idx), ltbl_field_type(idx)
> from epm_field f
> where f.field_id = atbl_field(idx);
>
> but that means x trips to the server.
>
> I was looking for something along the lines of
>
> select x,y
> bulk collect into tx, ty
> from table
> where field in tkeys
>
> Since I can't use forall with a select statement what else could I do? I
> don't really want to do a fake update statement with returning bulk into ...
>
> thx
>
> jack

You can turn the array into a nested table object type, and then query it directly, so (in pseduo-code)

create type X is table of Y;

rows X;

rows(1) := ...
rows(2) := ...
etc

select *
from my_table
where pk in ( select cast(rows as X ) from dual );

or thereabouts

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------