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.
Labels: debug, plsql
