PL/SQL function stops running after loop

Question

I feel like this code has been working all day but all of a sudden it doesn't work anymore and I can't seem to find the solution.

This Loop is inside a big function so I'll only post this:

FOR i in 0..t_strGroups.count
LOOP
  newStudentNumber := newStudentNumber || t_strGroups(i) || ' ';
  dbms_output.put_line(newStudentNumber); -- this outputs fine
END LOOP;
dbms_output.put_line('test'); --this line doesn't output

When testing where the function stopped working I found that after this loop I can no longer output lines. Does anyone know what might have happened here?

Thanks in advance!

the loop is inside begin end; .. correct ? and you are returning the value ? — Moudiz
– Moudiz, Commented Nov 18, 2015 at 13:46
yup, the weird thing is that it worked all day, I'm trying to reverse the collection later in the code, but it does no longer reach that part of the function. — Goos van den Bekerom
– Goos van den Bekerom, Commented Nov 18, 2015 at 13:47
A PL/SQL block won't simply stop executing. Perhaps an exception is being thrown somewhere inside the loop and swallowed by a poorly written exception handler that isn't shown here. Perhaps you're just not seeing the last line because your GUI isn't grabbing the last line from the dbms_output buffer. Perhaps your dbms_output buffer just isn't big enough. — Justin Cave
– Justin Cave, Commented Nov 18, 2015 at 13:48
Logically It seems correct , why dont you add an exception to catch the error ? — Moudiz
– Moudiz, Commented Nov 18, 2015 at 13:49
I guess that newStudentNumber exceeded its maximum size, resulting in an exception. Try outputting t_strGroups.count before the loop and i within the loop. — Erich Kitzmueller
– Erich Kitzmueller, Commented Nov 18, 2015 at 13:57

Goos van den Bekerom · Accepted Answer · 2015-11-19 07:36:25Z

2

After thinking a bit more about this I found the answer.

In the loop I loop from 0 to t_strGroups.count. Because the first position of my collection is 0. but collection.count returns the ammount of values in the collection.

I have 5 values in my collection so collection.count returns 5. however, the last value in my collection has position 4. So when the loops gets to itteration 5 the code will crash. Since position 5 does not exist.

I guess it is just stupid of mine to overlook this. And I thought about removing this question. But I decided to post this answer. Maybe it can help someone some day!

EDIT: According to the comment of Jeffrey Kemp the code doesn't crash it raises a NO_DATA_FOUND Exception. So it would also be smart to write an exception handler for that.

edited Nov 19, 2015 at 7:36

answered Nov 18, 2015 at 14:08

Goos van den Bekerom

1,4933 gold badges20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeffrey Kemp Over a year ago

Accessing a non-existent element in a collection shouldn't "crash", it should just raise NO_DATA_FOUND.

Goos van den Bekerom Over a year ago

@JeffreyKemp thanks for that addition. I'll catch that exception.

Collectives™ on Stack Overflow

PL/SQL function stops running after loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related