Cursor script hangs in loop 
Author Message
 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

Quote:

> 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)



Homepage: http://www.***.com/ ~nettama

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



Mon, 21 Aug 2000 03:00:00 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Cursor script hangs in loop

2. Cursor script hangs in a loop

3. Need help with a loop script (won't loop)

4. cursor within a cursor (looping)

5. Multiple cursors - second cursor hangs when opened

6. SQL LOOP HANGS

7. Computer hangs in while loop

8. Pro*COBOL + PL/SQL: ORACLE 7.2.3 hangs/loops

9. PCs hung in loop using sbterm

10. SKIP should go to EOF, but gets hung in infinite loop

11. Running loops using cursors...

12. Need your advice: Strange output using REPLACE function and CURSOR to loop variable


 
Powered by phpBB® Forum Software