Re: Cursor script hangs in loop

Hi,
Add EXIT condition within the loop.

EXIT WHEN COUNT_CURSOR%NOTFOUND;

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;
 EXIT WHEN COUNT_CURSOR%NOTFOUND; /*ADD HERE */   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; AMARENDRA 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;

--
*****************************************************************
AMARENDRA B NETTEM
Oracle Certified DBA (OCP)
Whittman-Hart Inc.,
311 South Wacker Drive, Suite 3500
Chicago, IL 60606.

Residence:
-----------
5039 N E River Road,
Apt. 1A
NORRIDGE, IL 60656


Ph.No. (708) 583 9870 (H)
       (312) 913 6758 (W)

 E-mail:nettama_at_charlie.cns.iit.edu,
        anettem_at_whittman-hart.com

Homepage: http://www.iit.edu/~nettama

****************************************************************
Opinions are mine and do not necessarily reflect those
of Whittman-hart Inc.