PL/SQL Collections: FIRST and LAST

I learned something new today while trying to debug a procedure that calls another procedure that calls another procedure (joy).

I kept getting an ORA-06052: PL/SQL: numeric or value error when I ran it. Initially I thought that the wrong value was being passed to the procedure. So I littered the code with DBMS_OUTPUT.PUT_LINE to find out where exactly it was failing when I noticed that it would stop just before looping through a collection.

So I put a:

DBMS_OUTPUT.PUT_LINE( 'collection count:  ' || l_collection.COUNT );

just before the loop. When I ran it, it showed a count of 0, hmmmm....

The loop used the FIRST/LAST syntax like this:

FOR i IN l_collection.FIRST..l_collection.LAST LOOP

I wonder if the fact that there aren't any elements have something to do with it?

So I rewrote it to use:

FOR i IN 1..l_collection.COUNT LOOP

Loaded it back into the database and voila! It worked.

I then had to let the original developer know so I wrote up a simple test case for it.

CREATE TABLE t
(
  id NUMBER,
  something_else VARCHAR2(20)
);

DECLARE
  TYPE myrecord IS TABLE OF T%ROWTYPE;
  l_collection MYRECORD;
BEGIN
  SELECT *
  BULK COLLECT INTO l_collection
  FROM t;

  FOR i IN l_collection.FIRST..l_collection.LAST LOOP
    NULL;
  END LOOP;
END;
/

Run it and you get the error:

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 9

Changed it to 1..COUNT

  1  DECLARE
  2    TYPE myrecord IS TABLE OF T%ROWTYPE;
  3    l_collection MYRECORD;
  4  BEGIN
  5    SELECT *
  6    BULK COLLECT INTO l_collection
  7    FROM t;
  8    FOR i IN 1..l_collection.COUNT LOOP
  9      NULL;
 10    END LOOP;
 11* END;
 12  /

PL/SQL procedure successfully completed.