Re: Cursor script hangs in a loop

From: Andrew Barnett <nobody_at_nospam.bp.com>
Date: 1998/03/05
Message-ID: <01bd47dd$a7a9a7a0$ac6964a1@azmelw1358.mel.az.bp.com>#1/1

try walking through your code. you open a cursor, fetch a row and exit if not found. you then close the cursor and go around again. well, the same row will be fetched the next time around - it's not going to disappear, so your loop will never terminate. also, your update is going to hit every row in the table, as you have no where clause - is this what you want?

assuming you want to put 1 in the first row, 2 in the second etc:

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;
   FETCH COUNT_CURSOR INTO HOLD_REC_KEY;    while COUNT_CURSOR%FOUND
   LOOP

      JIM_COUNTER := JIM_COUNTER + 1;
      UPDATE TEST_COUNTER SET REC_KEY = JIM_COUNTER where current of
count_cursor;
      FETCH COUNT_CURSOR INTO HOLD_REC_KEY;

   END LOOP;
   CLOSE COUNT_CURSOR;
   COMMIT;
END; or

DECLARE
   JIM_COUNTER NUMBER(10) := 0;
   CURSOR COUNT_CURSOR IS
      SELECT REC_KEY FROM TEST_COUNTER FOR UPDATE OF REC_KEY; BEGIN
   for count_record in COUNT_CURSOR
   LOOP

      JIM_COUNTER := JIM_COUNTER + 1;
      UPDATE TEST_COUNTER SET REC_KEY = JIM_COUNTER where current of

count_cursor;

   END LOOP;
   COMMIT;
END; or even

UPDATE TEST_COUNTER SET REC_KEY = ROWNUM;

-- 
Andrew - Wizzard

barnetaj_at_bp.com

Jim Lake <jimlake_at_home.com> wrote in article
<34FDF843.D47D17AE_at_home.com>...


> 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