Re: Cursor script hangs in a loop

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/03/05
Message-ID: <34ff028b.49429305@192.86.155.100>#1/1

A copy of this was sent to Jim Lake <jimlake_at_home.com> (if that email address didn't require changing) On Wed, 04 Mar 1998 16:56:35 -0800, you 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;
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
tkyte_at_us.oracle.com
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. Received on Thu Mar 05 1998 - 00:00:00 CST