Monday, December 8, 2008

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.

5 comments:

Saager Mhatre said...

FIRST and LAST were never intended to be used that way.

From Oracle® Database PL/SQL User's Guide and Reference: 5 Using PL/SQL Collections and Records

FIRST and LAST return the first and last (smallest and largest) index numbers in a collection that uses integer subscripts.

(emphasis mine)

The COUNT approach is also unreliable in case of sparse collections or collections with negative indices.

I've always preferred the more verbose...

i := coll.first;

loop
exit when i = coll.last;
// process coll(i)
i := coll.next(i);
end loop;

... but that fails for empty collections too! Need a better approach; back to the drawing board.

DomBrooks said...

>Need a better approach

i := coll.first;
while (l1 is not null)
loop
l1 := v1.next(l1);
end loop;


For sparse collections, you've always got the INDICES OF and VALUES OF clauses in later versions.

oraclenerd said...

@Saager @dom good points. If we were doing more with the collection (removing elements, adding elements) I would be so cavalier, that's for sure. But they are simple loops.

Something to be aware of though. Thank you.

mcohen01 said...

"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."

The real question here should be why are you doing these kinds of manipulations in the database? It's totally unsuited for it. You don't even have access to a step-through debugger when writing PL-SQL sprocs? You're reduced to printing out values to the console? This is an excellent example of the archaic, stone age programming model inherent in SQL stored procedures. This is exactly why application logic should be kept in the application, and only strictly data retrieval and manipulation should be done in the database. I realize that's probably an unpopular opinion with your regular readers, but unfortunately, far too often application logic that rightfully belongs in the application server is incorrectly implemented in the database layer.

oraclenerd said...

@mohen01

I've been waiting for you.

Toad has a pretty decent debugger and so do some of the other tools, but I don't use them. I prefer to rely on some type of logging (but we don't have it at the level I desire).

And of course "incorrectly" is a matter of opinion. Is it good for Google? Ebay? Amazon? Probably not. For the other 90% of applications out there? Absolutely. If you want something that will scale and has fewer moving parts, PL/SQL is the easiest way to go.

Sadly the world is riddled with Application Developers who refuse to learn how to properly leverage a database subsequently never learning how it could make their lives that much easier.