Monday, February 3, 2014
Committing Long running inserts after specific number of records
declare
cursor oldtab_csr is select * from mohe.MY_USER_LOG_20130721;
rec_count number := 1;
begin
for oldtab_rec in oldtab_csr loop
-----
begin
insert into mohe.MY_USER_LOG_INS
values
(oldtab_rec.LOG_SERIAL_NUM,
oldtab_rec.LOG_USER_ID,
oldtab_rec.LOG_ACTION_TYPE,
oldtab_rec.LOG_DATE,
oldtab_rec.TABLE_NAME,
oldtab_rec.FIELD_NAME_STRING,
oldtab_rec.OLD_VALUE_STRING,
oldtab_rec.NEW_VALUE_STRING,
oldtab_rec.CMP_NUM,
oldtab_rec.TRANS_SERIAL,
oldtab_rec.ACCOUNT_YEAR
);
exception
when others then
dbms_output.put_line('Unable to insert record '||oldtab_rec.LOG_SERIAL_NUM||' -
error encountered: '||sqlerrm);
end;
-----
rec_count := rec_count + 1;
if mod(rec_count,10000) = 0 then
commit;
end if;
end loop;
end;
No comments:
Post a Comment