Wednesday, January 11, 2012

FORALL iteration variable ... not allowed in this context

Oracle 11g
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