doing random update and etc....

[Deanna Tran_1]

currently the script below is doing commit for every row that was being insert..can you help me to come up with something like doing commit
for every 50 ...and also i want to perform
random updates at well...in addition, this loader insert the same data over and over again..i want to change it...too...



create PROCEDURE tester1 IS
numcount number:=0;
begin
while true loop
numcount:=numcount+1;
insert into employee1 values (numcount,'lkjljf','dlkjdeann',87970.80);
commit;
insert into employee2 values (numcount,'lkjljf','dlkjdeann',87970.80);
commit;
insert into employee3 values (numcount,'lkjljf','dlkjdeann',87970.80);
commit;
insert into employee4 values (numcount, 'dkjl','dlakdjfelsdjfe',10000);
commit;
insert into employee5 values (numcount, 'dldffalj','kjhgehoij',6700);
commit;
insert into employee6 values (numcount, 'dlfkajdfoer','ldifjoie',87509);
commit;
insert into employee7 values (numcount, 'opijo9jr','okdfetr',87834);
commit;
insert into employee8 values (numcount, 'p9i-lkdf','eoljedr',8760);
commit;
insert into employee9 values (numcount, 'lkdjfld','dfdflerj',98590);
commit;
insert into employee10 values (numcount, 'aldfkjaoeruljdkf','eiurykhdf',54295);
commit;
end loop;
END;
/

[Kenneth_19 · Accepted solution]

Not sure whether you are using Oracle or not, but from the syntax, it seems that my assumption is right.

You can use the SET AUTOCOMMIT command to help you to issue commit on a preset number of DMLs executed.

SET AUTOCOMMIT ON
Turns autocommit on.

SET AUTOCOMMIT OFF
Turns autocommit off (the default).

SET AUTOCOMMIT n
Commits changes after n SQL DML commands.

SET AUTOCOMMIT IMMEDIATE
Turns autocommit on.

Hope this can help to to solve the problem.

Regards,
Kenneth

[Dave Burton_1]

To commit after every 50 iterations through your loop, you can use your numcount variable like this:

if numcount / 50 = floor (rowcount / 50) then
commit;
end if;

Oracle publish a random package on their MetaLink website. Try this URL: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=1028249.6, or go to MetaLink and search for "random package".