oracle11g同步,oracle11g同库数据同步问题

我写了一个同步数据库的存储过程，缺陷肯定是存在的，问题是在执行批量insert时，出现问题是是不能准确判断是哪个插入操作表时引起的。

create or replace procedure test_wb_t1(fdate     in varchar,

sqlresult out varchar,

oresult   out number) is

vi_result   number(10);

validatemsg varchar2(32);

insertsql   varchar(1000);

selectsql   varchar(100);

tempresult  varchar(30);

tablename   varchar(30);

deletesql   varchar(1000);

cursor syn_table is

select table_name

from user_all_tables

where tablespace_name = 'CAFCS'

and table_name like '%_BASE%';

begin

select count(t_id)

into vi_result

from scott.t_impdatastatus

where status = 1

and to_char(to_date(IMP_EDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') = fdate;

oresult := vi_result;

validatemsg := '验证数据是否同步完成';

if vi_result <> 2 then

oresult := -1;

dbms_output.put_line('同步未完成');

end if;

insertsql := 'insert into cafc.';

selectsql := 'select * from scott.';

for cursor_result in syn_table loop

begin

tempresult := cursor_result.table_name;

tablename  := substr(tempresult,

0,

INSTR(tempresult, '_BASE', 1, 1) - 1);

insertsql  := 'insert into ' || tempresult ||

' (select * from scott.' || tablename || ')';

dbms_output.put_line(insertsql);

execute immediate insertsql;

end;

end loop;

commit;

sqlresult := tablename;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

oresult := -3;

end test_wb_t1;