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 LOOPI 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 LOOPLoaded 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 tRun it and you get the error:
TYPE myrecord IS TABLE OF T%ROWTYPE;
BULK COLLECT INTO l_collection
FOR i IN l_collection.FIRST..l_collection.LAST LOOP
DECLAREChanged it to 1..COUNT
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 9
2 TYPE myrecord IS TABLE OF T%ROWTYPE;
3 l_collection MYRECORD;
5 SELECT *
6 BULK COLLECT INTO l_collection
7 FROM t;
8 FOR i IN 1..l_collection.COUNT LOOP
10 END LOOP;
PL/SQL procedure successfully completed.