我有一个oracle过程,调用是出现这两个错误.
存储过程是定义一个游标,游标通过dblink从另外一个数据库中的表中取记录,然后从游标中取得字段的值,然后插入到本地的一个表中,没5000条提交一次,最后更新这个表.但是会出错.
查了一下,本地表中只插入了130000条记录,应该有200多万的.
存储过程是定义一个游标,游标通过dblink从另外一个数据库中的表中取记录,然后从游标中取得字段的值,然后插入到本地的一个表中,没5000条提交一次,最后更新这个表.但是会出错.
查了一下,本地表中只插入了130000条记录,应该有200多万的.
Cause: An update occurred at a coordinated database without the coordinator
beginning a distributed transaction. This may happen if a stored procedure
commits and then performs updates, and the stored procedure is invoked
remotely. It could also happen if an external transaction monitor violates the XA
protocol.
Action: If the cause is the former, check that any commit is not followed by an
update.
ORA-02063 preceding stringstring from stringstring
Cause: An Oracle error was received from a remote database link.
Action: Refer to the preceding error message(s).
is
v_ErrorText varchar2(200);
i number; Cursor cur_yhda_C is
select a.SERV_ID as serv_id,a.APRODUCT_ID as aproduct_id,a.MPRODUCT_ID as mproduct_id,
a.KHD as khd,b.wx as wx,decode(d.acdm,null,'0',d.acdm) as acdm
from db_acct.bill_serv_statistics@orazw a,tbm_yd b,tbl_ac_dld d
where a.area_id = 'BQ' and a.total_fee > 0 and bill_cycle_id = billcycleid and a.SERV_TYPE_ID = '20'
and a.khd = b.dm and b.xq = 'BQ' and b.dm = d.dlddm(+);
Cursor cur_yhda_G is
select a.SERV_ID as serv_id,a.APRODUCT_ID as aproduct_id,a.MPRODUCT_ID as mproduct_id,
a.KHD as khd,b.wx as wx,decode(d.acdm,null,'0',d.acdm) as acdm
from db_acct.bill_serv_statistics@orazw a,tbm_yd b,tbl_ac_dld d
where a.area_id = 'BQ' and a.total_fee > 0 and bill_cycle_id = billcycleid and a.SERV_TYPE_ID = '10'
and a.khd = b.dm and b.xq = 'BQ' and b.dm = d.dlddm(+);begin
--导入远程用户档案表
i := 0;
for item_cur in cur_yhda_C loop
begin
insert into tbl_yhda(YHBH,NETTYPE,DH,TCH,SLD,WX,HWGSX,fzac,hwac)
values (item_cur.SERV_ID,'C',item_cur.APRODUCT_ID,item_cur.MPRODUCT_ID,
item_cur.KHD,item_cur.wx,item_cur.wx,item_cur.acdm,item_cur.acdm);
i := i+1;
if(mod(i,5000) = 0) then
commit;
end if;
end;
end loop;
commit;
i := 0;
for item_cur in cur_yhda_G loop
begin
insert into tbl_yhda(YHBH,NETTYPE,DH,TCH,SLD,WX,HWGSX,fzac,hwac)
values (item_cur.SERV_ID,'G',item_cur.APRODUCT_ID,item_cur.MPRODUCT_ID,
item_cur.KHD,item_cur.wx,item_cur.wx,item_cur.acdm,item_cur.acdm);
i := i+1;
if(mod(i,5000) = 0) then
commit;
end if;
end;
end loop;
commit;
--将用户档案表中代理点没有归属的纪录归属到县本级区域中心
begin
update tbl_yhda y set y.fzac = (select a.acdm from tbl_areacenter a where a.county = y.wx and a.baseflag = '1') where y.fzac = '0';
commit;
end;
Msg:= 'OK';
errno:= 1;
EXCEPTION
WHEN OTHERS THEN
errno:=-1;
v_errortext:=substrb(sqlerrm,1,200);
Msg:= v_errortext;
rollback;end Pro_InputYhda_All;