Hi, TOm,

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 ?