DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Infinite loop in cursor

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    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
    Sonali

  2. #2
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    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
    Rajeev Suri

  3. #3
    Join Date
    Jan 2001
    Posts
    318
    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
    Sonali

  4. #4
    Join Date
    Oct 2000
    Posts
    123
    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

  5. #5
    Join Date
    Jul 2000
    Posts
    243
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width