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