Re: Infinite loops possible in PL/SQL?

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: 2000/05/22
Message-ID: <392A0CCB.A170599@ntsource.com>#1/1

The following reworking of your example will generate an infinite loop, although it is not likely that someone would program it:

create or replace procedure helpmeloop
is
  status NUMERIC;
  num NUMERIC;
BEGIN
  DBMS_OUTPUT.put_line('Beginning test...');   num := 1/0; -- throw 1st exception
  DBMS_OUTPUT.put_line('Test successful...'); EXCEPTION
WHEN OTHERS THEN
  BEGIN
    status := SQLCODE;

• num := 2/0; -- throw nested exception helpmeloop; END; END;

A more likely situation is to forget to exit from a loop, such as the following:

declare

   cursor c is select dummy from dual;
   v_dummy dual.dummy%type;
begin

   open c;
   loop

      fetch c into v_dummy;
      -- exit when c%notfound;

   end loop;
   close c;
end;

Both of the above programs generated infinite loops on NT4, 8.1.5. The real problem was how to stop the infinite loop once it started. The second infinite loop I was able to stop by connecting as a dba user and issuing the "alter system kill session 'sid,serial#' command where the sid and serial# are columns from the v$session view. The first infinite loop did not behave so nicely and I had to use the control panel to kill the service.

Frank Hubeny

Shmooth wrote:

> I'm trying to produce an infinite loop using PL/SQL. Is this possible?
>
> I'm studying for my OCP exam and my book mentions how I should do
> different things to avoid these dastardly loops that can be cause when
> an exception handler throws another exception.
>
> Example that does *not* loop infinitely:
> -----------------------------------------------
> DECLARE
> status NUMERIC;
> num NUMERIC;
> BEGIN
> DBMS_OUTPUT.put_line('Beginning test...');
> num := 1/0; -- throw 1st exception
> DBMS_OUTPUT.put_line('Test successful...');
> EXCEPTION
> WHEN OTHERS THEN
> BEGIN
> status := SQLCODE;
> num := 2/0; -- throw nested exception
> END;
> END;
Received on Mon May 22 2000 - 00:00:00 CDT