this doesn't seem to work with associative arrays? Is this a nested table
only feature?
"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
news:42663CF0.183B_at_yahoo.com...
> 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"
>
> ------------------------------------------------------------
Received on Tue May 03 2005 - 11:51:23 CDT
