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

Managing User-Defined Exceptions in PL/SQL

Database version: Oracle 11g

There are two basic ways to raise a user-defined exception in PL/SQL.

Here's one way.

DECLARE
  my_exception EXCEPTION;
BEGIN
  RAISE my_exception;
END;
/

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 5

This method is simple, but it doesn't provide you with any information when the error is thrown. Here is a better method.

BEGIN
  raise_application_error(-20100,
                          'describe your exception here');
END;
/

ORA-20100: describe your exception here
ORA-06512: at line 3

Oracle gives us the somewhat stingy range of -20000 to -20999 (inclusive) for error codes that can be passed to raise_application_error. (Incidentally, you can also pass in decmial numbers such as -20001.142435.) But assuming I need to raise the same application error in multiple places, what's the best way for me to consistently use the same error number and the same error message?

The following is a simple framework that can help manage error codes and error messages. It has two components: a package that defines the exceptions and links them to an error number, and a table that associates error numbers with error messages.

Here's the table.

-- create table to relate error codes to error definitions
CREATE TABLE error_def (
       error_id NUMBER, 
       message VARCHAR2(1000), 
       comments VARCHAR2(1000), 
       CONSTRAINT error_def_pk PRIMARY KEY(error_id)
);

Something to consider: with some additional work you could tie a particular range of error numbers to a particular application. You would do this by creating a second table that links applications to error ranges (min/max). Then add a foreign key to the error_def table to tie the error to a particular application, and a before insert trigger to ensure that the error_id is within the valid range for its application.

Here's the package.

-- create a package to tie exceptions to numbers and messages
CREATE OR REPLACE PACKAGE errlib IS

  SUBTYPE errmsg IS error_def.message%TYPE;
  SUBTYPE errnum IS error_def.error_id%TYPE;

  FUNCTION f_errmsg(p_errnum_in IN errnum) RETURN errmsg;

  /* Write these three statements for each of your errors */
  invalid_status_errnum CONSTANT errnum := -20105;
  invalid_status EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_status,
                        -20105);

END errlib;
/

CREATE OR REPLACE PACKAGE BODY errlib IS

  FUNCTION f_errmsg(p_errnum_in IN errnum) RETURN errmsg IS
    l_errmsg errmsg;
  BEGIN
    SELECT error_def.message
    INTO   l_errmsg
    FROM   error_def
    WHERE  error_def.error_id = p_errnum_in;
    RETURN l_errmsg;
  EXCEPTION
    WHEN no_data_found THEN
      RETURN NULL;
  END f_errmsg;

END errlib;
/

Each of your exceptions will need to have three entries in the errlib spec. One downside of this approach is that it may invalidate other packages that use the errlib package, though you may be OK (depending on your version of Oracle) if add your new entries at the bottom of the spec.

Finally, here's how you set the error message for the exception.

-- add an application error message to the table
INSERT INTO error_def
  (error_id, message, comments)
VALUES
  (-20105, 'Something bad happened!', 'If you see this error, call Bill.');
COMMIT;

Test it!

-- test!
BEGIN
  raise_application_error(errlib.invalid_status_errnum,
                          errlib.f_errmsg(errlib.invalid_status_errnum));
END;
/

ORA-20105: Something bad happened!
ORA-06512: at line 3