Infinite loop in cursor

Here is a example ( Note its not a puzzle, it's just something one of our customers wrote which landed up in infinite loop in SQL server, this could happen with Oracle too..)

This example might not be syntactically correct.. but shows what they did..this is a part of a stored procedure (Oracle version 8.0.6 on NT)

DECLARE CURSOR Temp_work_cursor
IS
SELECT Temp_work_ID, Temp_work_level
FROM branchtemp
where Temp_work_level > 5
and temp_date between x and y;

Begin
OPEN Temp_work_cursor ;
Loop
FETCH Temp_work_cursor
INTO inOld_ID, inLevel;
EXIT WHEN Temp_work_cursor%NOTFOUND;

For date between x and y
begin
insert into branchtemp(Temp_work_ID, temp_work_level, temp_date)
values(temP_work_id_seq.nextval, 5, x);
x= x+1
end for loop;

End loop;
CLOSE Temp_work_cursor;
End ;

--------------------------------------------------
What this is doing is selecting a records from a table by opening a cursor for perticular date range and then inserting records to the same table for that date range.


Now my question is if the cursor fetches all the rows which satisfies the where clause at 1st before opening it, it will not go in infinite loop right because it will have fixed number of rows before processing ?
But if the cursor is fetching records from a table after each loop

OPEN Temp_work_cursor ;
Loop
...
...
...
End loop;

In this case it will go in infinite loop right , because the next time it fetches records from the table it will have more records matching the condition and so on...


Note: this is cursor technically might not make sense because I took out other logic to make it simple.


So how doses cursor work ? Does it keep records in memory the 1st time you declare and those are the only rows it will process or does it selects from the table after each loop ?


thanks a lot

Cursor will fetch the records at --

OPEN Temp_work_cursor ;

so, I don't think this will make it go into infinite loop. There must be another place that is causing the problem.

The FOR LOOP looks syntactically wrong but as you said that you simplified the code...

- Rajeev

Could this be possible that when the cursor was opened with that table data it locked the table and so the insert on the same table was not possible and so everthing looked dead...

thanks
Sonali

Do one thing:

Use dbms_output.put_line() or or some other fuctions suitable for SQL server to display the values of x each time to determine if it is infinite or dead process.

Take care

Hi sonaliak

"Could this be possible that when the cursor was opened with that table data it locked the table and so the insert on the same table was not possible "

not in oracle! if you whant the table looked, you have to explicitly look the table the cursor is based upon.

also, after you fetched all the rows no change on this rows in the table will affect the rows in your programe, so if you need to prevent this from happening you have to lock the table.