I don't know about you, but I sometimes write code that (inadvertently, not on purpose) contains an infinite loop. So I run my program and Toad goes off into never-never land, with Oracle chewing up CPU cycles so intently that it is hard to connect as SYS and kill the session.
I hate that, don't you?
Now, there are two ways to address this problem:
1. Don't write code that contains infinite loops. Well, DUH! Of course not. I never want to do this intentionally, but of course the world (even the world of my code) does not always match my intentions.
2. Insert "killer logic" into the loop that forces termination of the loop after an excessive number of variations. I wrote a package (loop_killer) that makes it is easy to do precisely this.
Show me the code!
I certainly won't show you all the code in this entry. Download full source code from this zip file.
Here, however, is the specification of the package:
CREATE OR REPLACE PACKAGE loop_killer
/*
| File name: loop_killer.pkg
|
| Overview: Simple API to make it easier to insert code inside a loop
| to check for infinite or out of control loops and kill
| them after N iterations.
|
| Raises the infinite_loop_detected exception.
|
| Author(s): Steven Feuerstein
|
| Modification History:
| Date Who What
| 23-AUG-2007 SF Created package
*/
IS
e_infinite_loop_detected EXCEPTION;
c_infinite_loop_detected PLS_INTEGER := -20999;
PRAGMA EXCEPTION_INIT (e_infinite_loop_detected, -20999);
PROCEDURE kill_after (max_iterations_in IN PLS_INTEGER);
PROCEDURE increment_or_kill (by_in IN PLS_INTEGER DEFAULT 1);
FUNCTION current_count
RETURN PLS_INTEGER;
END loop_killer;
How do I use it?
The loop killer package is very straightforward:
· loop_killer.kill_after: a subprogram that tells the utility the limit of iterations after which the loop should be terminated. You call this program before you start the loop. It sets the "kill after" limit and also sets the internal counter to 1.