ForALL and Bulk Collections in PL/SQL

[JaxtheDog]

Good Afternoon,
I need some help understanding buld collections and their use in a forall statement. I want to update multiple rows in a table based on the results set of a query (cursor). A simplified example of my current statement is shown below. It's been thought several interations as I try to understand how the bulk collection and forall statements work. I'd appreciate any assistance you might give, including definitions of the various conventions in this type of action.

declare
cursor Item_Master_Export is select
item_master.item_no as item,
item_location.stock_area as Warehouse
from warehouse_info,
item_location,
item_master
where
warehouse_info.warehouse_flag='P'
and item_location.stock_area
=warehouse_info.warehouse_id
and item_master.item_no = item_location.item_no;

Type Item_Table is Table of Item_Master_Export%rowtype
INDEX BY pls_integer;

Eligible_items item_table;

begin
open Item_Master_Export;
fetch Item_Master_Export Bulk Collect into Eligible_Items;
close Item_Master_Export;

forall indx in eligible_items.first..eligible_items.last
Update item_location set last_extract_date=sysdate,
extract_req='N'
where item_location.item_no = item(indx)
and stock_area=warehouse(indx);

end;

I have tried Feuerstein's book, but he assumes more knowledge of the pieces than I bring to the table in his examples.

Thanks you
Jax

 

[JaxtheDog]

I have figured out how to acomplish this, and have some clues to what I was doing wrong. So I thought I would share them.

In my original forall, I tried to load all of the eligible items into a cursor and use it in the forall statement. I could not figure out how to get that to work.

Instead I created a virtual table to put the key fields I needed:
Type Item_list is table of varchar2(30) index by pls_integer;

Update_record item_list;

I then loaded the table with the item key as I processed the cursor in a loop in another part of this procedure (not included in original example):

update_record(line_counter) := items.item_no||' - '||items.warehouse;

when I was done with the loop, I used the following forall:

forall indx in update_record.first..update_record.last
Update item_location set last_extract_date=sysdate, extract_req='N' where item_no||' - '||stock_area = update_record(indx);

commit;

That worked. I would still like to know if I could have done this using the original cursor. I could not do the update as I processed the cursor records because I needed the entire export process to complete before I updated any records.

Thanks,
Jax