Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Pl/Sql cursor with For Update

    Hi All,

    I have declared one pl/sql cursor with ' For Update ' option and inside the executable portion I am trying to COMMIT one updated transaction.


    But while executing the block, it is showing an error message ' fetch out of sequence ' .

    I would like to know the reason for this and what is the solution for the same.


    Thanks

    Jayanta....

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Pl/Sql cursor with For Update

    The COMMIT releases the locks taken by the FOR UPDATE cursor, which means it no longer works. The solution is NOT to commit inside the cursor loop. Just commit once, afterwards.

  3. #3
    Join Date
    Jan 2004
    Posts
    153

    Re: Pl/Sql cursor with For Update

    Originally posted by jayanta_deb
    Hi All,

    I have declared one pl/sql cursor with ' For Update ' option and inside the executable portion I am trying to COMMIT one updated transaction.


    But while executing the block, it is showing an error message ' fetch out of sequence ' .

    I would like to know the reason for this and what is the solution for the same.


    Thanks

    Jayanta....
    From the site named 'www.orafeq.com' I have found the following code where Commit is there inside the loop.


    rem -----------------------------------------------------------------------
    rem Filename: plsloop.sql
    rem Purpose: Example: UPDATE/DELETE in a loop and commit very X records
    rem Handy for huge tables that cause rollback segment problems
    rem Date: 09-Apr-1999
    rem Author: Frank Naude, Oracle FAQ
    rem -----------------------------------------------------------------------

    declare
    i number := 0;
    cursor s1 is SELECT * FROM tab1 WHERE col1 = 'value1'
    FOR UPDATE;
    begin
    for c1 in s1 loop
    update tab1 set col1 = 'value2'
    where current of s1;

    i := i + 1; -- Commit after every X records
    if i > 1000 then
    commit;
    i := 0;
    end if;

    end loop;
    commit;
    end;
    /

    -- Note: More advanced users can use the mod() function to commit every N rows.
    -- No counter variable required:
    --
    -- if mod(i, 1000) then
    -- commit;
    -- end if;
    --

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Pl/Sql cursor with For Update

    That is poor code, indeed! The most advanced users don't do this at all. The writer doesn't even seem to be aware of s1%rowcount, which makes variable i redundant.

    Also as you can see for yourself, it doesn't work! :-

    PHP Code:
    SQLselect from tab1;

    COL1
    ----------
    value1
    value1
    value1

    SQL
    > declare
      
    2    i number := 0;
      
    3    cursor s1 is SELECT FROM tab1 WHERE col1 'value1'
      
    4                 FOR UPDATE;
      
    5  begin
      6    
    for c1 in s1 loop
      7        update tab1 set col1 
    'value2'
      
    8               where current of s1;
      
    9  
     10        i 
    := 1;              -- Commit after every X records
     11        
    if 1 then -- Changed 1000 to 1 as I only have 3 records!
     
    12           commit;
     
    13           i := 0;
     
    14        end if;
     
    15  
     16    end loop
    ;
     
    17    commit;
     
    18  end;
     
    19  /
    declare
    *
    ERROR at line 1:
    ORA-01002fetch out of sequence
    ORA
    -06512at line 6 
    Do you want to inform ORAFAQ that this code is cr*p or shall I?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Pl/Sql cursor with For Update

    I like this disclaimer on that site:

    Warning: These scripts are potentially dangerous and can destroy your database or system. Make sure you understand, customize and test all scripts to suite your needs and environment before using them.
    caveat emptor!

Posting Permissions

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