Re: Cursor script hangs in loop

Hi Jim,

    It seems to me your troubles with this script are two: 1) Within the loop you're both opening and closing the cursor. This can be okay when you're trying to change the result of the cursors select but, here you aren't doing that (ie where clause). Move both outside the loop. 2) You're not using "exit when count_cursor%notfound;". Usually this is inserted just below the FETCH. The logic here says this will never stop.? You're always changing items in a table (test_counter) that is read on index from the beginning. As long as there is one item within the table, it'll never reach the %notfound condition. No wonder you've got trouble killing it. Though it's been written as if it has one, it has no condition to end. Greg

Jim Lake 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;
Received on Thu Mar 05 1998 - 00:00:00 CST