fetch across commits

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;

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.

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;

what's the benefit by commit every 2000 rows?

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.

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

Hi

Look at the link below you may have better ideas

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

regards
hrishy