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.

·          loop_killer.increment_or_kill: call this subprogram inside your loop. It will either increment the counter or kill the loop if the increment has met the "kill after" value you provided earlier.

·          loop_killer.current_count: returns the current count in the iterations.

Things to keep in mind:

The loop is terminated by raising the loop_killer.e_infinite_loop_detected exception, which has the error code -20,999. You will also see a message displayed on your screen, as you will see below in the example.

Here is an example of using loop_killer to terminate a truly infinite loop:

Here's the DBMS_OUTPUT text from the termination:

Loop killer failure: Your loop exceeded 100 iterations.

Call stack below shows location of problem:

----- PL/SQL Call Stack -----

  object      line  object

  handle    number  name

26D69C20        29  package body QCTO1600_NEW.LOOP_KILLER

26F1C63C         6  anonymous block

Here's the code, in case you want to try it yourself:

BEGIN

   loop_killer.kill_after (100);

   LOOP

      DBMS_OUTPUT.put_line (loop_killer.current_count);

      loop_killer.increment_or_kill;

   END LOOP;

END;

/