CREATE TABLE items ( item_id INTEGER, item_name VARCHAR2(100) );
Let's say we're constructing a collection of items in memory that we then want to write to the item table. It seems sensible to use an associative array and index our collection by the item ID. But we run into issues when it comes to writing that collection to our table:
DECLARE TYPE items_aat IS TABLE OF items.item_name%TYPE INDEX BY PLS_INTEGER; l_items items_aat; -- for using FORALL with a sparse collection TYPE indices_aat IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER; l_item_indices indices_aat; BEGIN l_items(100) := 'item1'; l_item_indices(100) := TRUE; l_items(200) := 'item2'; l_item_indices(200) := TRUE; FORALL itr IN INDICES OF l_item_indices INSERT INTO items (item_id, item_name) VALUES (itr, l_items(itr)); -- error! END; / ORA-06550: line 22, column 8: PLS-00430: FORALL iteration variable ITR is not allowed in this context
It's complaining about my usage of itr as the item_id value. Why can't I use the value of the iterator like that? I have no idea.
The same error is generated if you have a dense collection and the more typical FORALL usage:
DECLARE TYPE items_aat IS TABLE OF items.item_name%TYPE INDEX BY PLS_INTEGER; l_items items_aat; BEGIN l_items(1) := 'item1'; l_items(2) := 'item2'; FORALL itr IN l_items.first .. l_items.last INSERT INTO items (item_id, item_name) VALUES (itr, l_items(itr)); -- error! END; / ORA-06550: line 14, column 8: PLS-00430: FORALL iteration variable ITR is not allowed in this context
The only way that I know to get around this error-- so long as I'm using a sparsely defined associative array with FORALL-- is to simply use item ID both as the value and index for the indices collection. I don't like this at all.
DECLARE TYPE items_aat IS TABLE OF items.item_name%TYPE INDEX BY PLS_INTEGER; l_items items_aat; -- for using FORALL with a sparse collection TYPE indices_aat IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER; l_item_indices indices_aat; BEGIN l_items(100) := 'item1'; l_item_indices(100) := 100; l_items(200) := 'item2'; l_item_indices(200) := 200; FORALL itr IN INDICES OF l_item_indices INSERT INTO items (item_id, item_name) VALUES (l_item_indices(itr), l_items(itr)); END; / PL/SQL procedure successfully completed
No comments:
Post a Comment