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


(if that email address didn't require changing)

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;

This is called an infinite loop if TEST_COUNTER has at least one row.  What are
you trying to do?  Unless test_counter is empty, this will always get the first
row, update it, and get the first row, update it, get the first row, update it
(and so on)....

Its hard to kill cause its in an infinite loop and a really tight one at that.
Not only that but if test_counter has lots of rows both the SELECT for UPDATE
and the UPDATE are doing lots of work.

Thomas Kyte

Oracle Government
Herndon VA

http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.



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

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




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 a loop

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;

Adjust your PL/SQL block slightly to eliminate the infinite loop:

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;
 LOOP
   FETCH COUNT_CURSOR INTO HOLD_REC_KEY;
   EXIT WHEN COUNT_CURSOR%NOTFOUND
   JIM_COUNTER := JIM_COUNTER + 1;
   UPDATE TEST_COUNTER
      SET REC_KEY = JIM_COUNTER
    WHERE CURRENT OF COUNT_CURSOR;
 END LOOP;
 CLOSE COUNT_CURSOR;
 COMMIT;
END;

An alternative to perform the same task:

BEGIN
   UPDATE TEST_COUNTER
      SET REC_KEY = REC_KEY + 1;
   COMMIT;
END;

Bob Cunningham



Tue, 22 Aug 2000 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Cursor script hangs in loop

2. Cursor script hangs in 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