Re: Cursor script hangs in a loop

On Wed, 04 Mar 1998 16:56:35 -0800, Jim Lake <jimlake_at_home.com> wrote:

>I am trying to get a PL/SQL script to increment a column in a table. The
>following script is what I have come-up with, but it runs in a loop, and
>is a bear to kill. I have moved the close cursor inside and outside the
>loop, but still the same results. Thanks in advance for any help.
>
>DECLARE
> JIM_COUNTER NUMBER(10) := 0;
> HOLD_REC_KEY NUMBER(10);
> CURSOR COUNT_CURSOR IS
> SELECT REC_KEY FROM TEST_COUNTER FOR UPDATE OF REC_KEY;
>BEGIN
> LOOP
> OPEN COUNT_CURSOR;
> FETCH COUNT_CURSOR INTO HOLD_REC_KEY;
> IF COUNT_CURSOR%FOUND THEN
> JIM_COUNTER := JIM_COUNTER + 1;
> UPDATE TEST_COUNTER SET REC_KEY = JIM_COUNTER;
> ELSE
> EXIT;
> END IF;
> CLOSE COUNT_CURSOR;
> END LOOP;
>COMMIT;
>END;
>

Adjust your PL/SQL block slightly to eliminate the infinite loop:

DECLARE
 JIM_COUNTER NUMBER(10) := 0;
 HOLD_REC_KEY NUMBER(10);
 CURSOR COUNT_CURSOR IS
  SELECT REC_KEY FROM TEST_COUNTER FOR UPDATE OF REC_KEY; BEGIN
 OPEN COUNT_CURSOR;
 LOOP
   FETCH COUNT_CURSOR INTO HOLD_REC_KEY;    EXIT WHEN COUNT_CURSOR%NOTFOUND
   JIM_COUNTER := JIM_COUNTER + 1;
   UPDATE TEST_COUNTER
      SET REC_KEY = JIM_COUNTER
    WHERE CURRENT OF COUNT_CURSOR;
 END LOOP;
 CLOSE COUNT_CURSOR;
 COMMIT;
END; An alternative to perform the same task:

BEGIN
   UPDATE TEST_COUNTER
      SET REC_KEY = REC_KEY + 1;
   COMMIT;
END; Bob Cunningham
bcunn_at_oanet.com Received on Fri Mar 06 1998 - 00:00:00 CST