fetch across commits
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: fetch across commits

  1. #1
    Join Date
    Jun 2001
    Posts
    193
    regarding fetch across commits
    is the following valide? is it good?
    if it is not, what's other choice?

    declare
    cursor tab_row is select name,num_years_worked from names;
    my_row tab_row%rowtype;
    begin
    for my_row in tab_row loop
    if my_row.num_years_worked>5 then
    update names set eligible_for_award='Y' where current of tab_row;
    commit;
    end if;
    end loop;
    end;

    guru is on the way!!!!

  2. #2
    Join Date
    May 2001
    Posts
    70
    I would move the commit to after your loop.

    Although, if you are handling a large number of transactions you might want to set your rollback segment to a large rollback segment prior to running your package.

    Good Luck.

  3. #3
    Join Date
    Jul 2000
    Posts
    243
    Hi

    you can create a counter and commit after, say 2k rows if you have many rows to commit.

    declare
    cursor tab_row is select name,num_years_worked from names;
    my_row tab_row%rowtype;
    x number;
    begin
    x:= 0.0;
    for my_row in tab_row loop
    if my_row.num_years_worked>5 then
    update names set eligible_for_award='Y' where current of tab_row;
    x :x+1;
    if x = 2000
    then
    commit;
    x :=0;
    else
    null;
    end if;
    end if;
    end loop;
    end;


  4. #4
    Join Date
    Jun 2001
    Posts
    193
    what's the benefit by commit every 2000 rows?
    guru is on the way!!!!

  5. #5
    Join Date
    Apr 2001
    Posts
    108

    fetch across commits

    A "fetch across commit" can cause an ORA-01555 Snapshot too old error to occur. While Oracle allows the fetch across commit, it is not an ANSI standard. Possible solutions include:

    1) Rewrite the program to avoid a fetch across commit
    2) Commit less often
    3) Add an "order by 1" statement to the query. This creates a temp segment to hold the results of the order by and will not require a consistent read.

  6. #6
    Join Date
    Feb 2001
    Posts
    75
    Hi,

    The oracle documentation gives another work around. In the select, also get rowid and then use rowid to update the row and commit, instead of using where current of.

    Kailash Pareek

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hi

    Look at the link below you may have better ideas

    http://www.oracle.com/oramag/oracle/...o41asktom.html

    regards
    hrishy


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