OraFAQ Forum: SQL & PL/SQL » Commit N-rows in PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Commit N-rows in PL/SQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Commit N-rows in PL/SQL [message #895]

Mon, 11 March 2002 12:02

Gerry Wright
Messages: 1
Registered: March 2002

Junior Member

I will running a procedure to loop through a table that contains over a million records. And I want to commit every 1000 records. How would I accomplish this?

Report message to a moderator

Re: Commit N-rows in PL/SQL [message #896 is a reply to message #895]

Mon, 11 March 2002 12:16

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

use a mod function.

something like this.... this block commits and gives an output for evey 5 record inside the loop.
the records that are not commited, are commited outside the loop.
----
declare
cursor c1 is select * from emp;
begin
for mag in c1 loop
exit when c1%notfound;
if mod(c1%rowcount,5)=0 then
commit;
dbms_output.put_line('commit inside the loop');
end if;
end loop;
commit;
dbms_output.put_line('commit_outside the loop');
end;
/

Report message to a moderator

Re: Commit N-rows in PL/SQL [message #1749 is a reply to message #896]

Sun, 26 May 2002 11:14

gajapathy
Messages: 1
Registered: May 2002

Junior Member

usage of rowcount to retrieve only one row which as empid which r different but same job
please send a syntax of rowcount function used in oracle-sqlplus 3.3

Report message to a moderator

Previous Topic:	Automatically assign the ID number to next row
Next Topic:	trace file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 20:06:27 CDT 2024