ORA-03113通信同道的文件结束
vabug 2004-09-13 08:21:46 用存储工程导入数据
begin
open s_rs for 'select DISTINCT jh,csrq from '||sourcetable||'@orcl.us.oracle.com';
loop
fetch s_rs into v_jh,v_csrq;
exit when s_rs%NOTFOUND;
v_jh:=trim(v_jh);
select importdata.nextval into v_ypph from dual;
insert into xyqdsj_tmp(ypph,jh,xyrq) values(v_ypph,v_jh,v_csrq);
v_sql:='insert into '||targettable||'('||targetitems||') select distinct '||v_ypph||','||sourceitems||',-2,1 from '||sourcetable||'@orcl.us.oracle.com where jh='||chr(39)||v_jh||chr(39)||' and csrq='||'to_date('||chr(39)||to_char(v_csrq,'YYYY-MM-DD')||chr(39)||','||chr(39)||'YYYY-MM-DD'||chr(39)||')';
EXECUTE IMMEDIATE v_sql;
/* v_sql:='select min(yysd1),max(yysd2) from '||sourcetable||'@orcl.us.oracle.com where jh='||chr(39)||v_jh||chr(39)||' and csrq='||'to_date('||chr(39)||to_char(v_csrq,'YYYY-MM-DD')||chr(39)||','||chr(39)||'YYYY-MM-DD'||chr(39)||')';
open max_rs for v_sql;
loop
fetch max_rs into yysd1,yysd2;
exit when max_rs%NOTFOUND;
end loop;
close max_rs;
*/
v_sql:='select distinct cwmc from '||sourcetable||'@orcl.us.oracle.com where jh='||chr(39)||v_jh||chr(39)||' and csrq='||'to_date('||chr(39)||to_char(v_csrq,'YYYY-MM-DD')||chr(39)||','||chr(39)||'YYYY-MM-DD'||chr(39)||')';
open cw_rs for v_sql;
loop
fetch cw_rs into v_cwmc;
exit when cw_rs%NOTFOUND;
end loop;
close cw_rs;
insert into bgxx_tmp(lxbh,sd1,sd2,tjph,bgm,zt,cwxx,YSJLM) values(lxbh,yysd1,yysd2,v_ypph,bgm,0,v_cwmc,ybgm);
end loop;
close s_rs;
end;
-----
insert into 语句是生成的,有时就发现ORA-03113错误,数据连接中断,有时又很好,请问大家碰到过没有,如何解决!