Cursor script hangs in loop 
Author Message
 Cursor script hangs in loop

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;



Sun, 20 Aug 2000 03:00:00 GMT
 Cursor script hangs in loop

Hi,

try:
DECLARE
 JIM_COUNTER NUMBER(10) := 0;
 CURSOR COUNT_CURSOR IS
  SELECT REC_KEY FROM TEST_COUNTER ;
BEGIN
        FOR TESTROW in COUNT_CURSOR LOOP
                 JIM_COUNTER := JIM_COUNTER + 1;
                 UPDATE TEST_COUNTER SET REC_KEY = JIM_COUNTER WHERE REC_KEY=TESTROW.REC_KEY;
        END LOOP;
COMMIT;
END;

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;

--

Regards

Matthias Gresz    :-)




Mon, 21 Aug 2000 03:00:00 GMT
 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

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;



Mon, 21 Aug 2000 03:00:00 GMT
 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.iit.edu/~nettama

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



Mon, 21 Aug 2000 03:00:00 GMT
 Cursor script hangs in loop

Put the open cursor and the close cursor out of the loop.

Best regards


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;



Mon, 21 Aug 2000 03:00:00 GMT
 
 [ 5 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