作业调用程序过程实现数据导入
create or replace procedure prc_receivenewdata
IS
/*声明游标*/
cursor cursor_purchid is select purchid from eetedm.mid_purchase@sccx.oracle.dblink t1
where flag = 0 for update of flag ;
pid purchase.purchid%type;
purchcount number(10);
begin
select count(*) into purchcount from eetedm.mid_purchase@sccx.oracle.dblink where flag = 0 ;
if purchcount> 0 then
open cursor_purchid;
loop
fetch cursor_purchid into pid;
EXIT WHEN cursor_purchid%NOTFOUND;
insert into purchase
select * from eetedm.mid_purchase@sccx.oracle.dblink where purchid = pid ;
insert into purchdetail(
PURCHID ,
LINENO ,
ILISTNUM ,
ORDERQTY ,
ORDERUNIT ,
UNITCOST ,
LINECOST ,
VENDORTASK ,
VENDORDATE ,
CIQDATE ,
ENTRYPROTDATE ,
ENTRYPORT ,
TOCOMPANY ,
TOCOMINCEPTOR ,
CHANGEBY ,
CHANGEDATE ,
MGID ,
ENTID ,
PAYTIME ,
BEDATE ,
PURCHPARENT ,
QLINENO ,
MODTYPE ,
PENTRYPROTDATE ,
PENTRYPORT ,
FORCURRENCY ,
FORUNITCOST ,
FORLINECOST ,
CHANGERATE ,
UNITCONV ,
INCEPTUNIT ,
INCEPTQTY ,
ARRIVEDATE ,
FREIGHTSTATUS ,
TYPEDESCR ,
MATERIALNAME ,
DELIVERYDATE ,
COMPLETEDATA ,
PRODUCTLETTER ,
FIRSTDELIVERDATE ,
COSTCODE ,
COSTCODENAME ,
UNICOSTCODE ,
PRODUCER ,
OBJECT ,
YHRATE
)
select * from eetedm.mid_purchdetail@sccx.oracle.dblink where purchid =pid ;
insert into statuslist
select * from eetedm.mid_statuslist@sccx.oracle.dblink where mainkey = pid ;
update eetedm.mid_purchase@sccx.oracle.dblink set flag = 1 where CURRENT of cursor_purchid ;
end loop;
/*释放游标*/
close cursor_purchid;
commit;
sp_write_log( ' 数据导入成功! ');
else
sp_write_log( ' 今天没有数据导入 ');
end if;
EXCEPTION
WHEN others THEN
sp_write_log( ' 数据导入时，发生异常，事务回滚！ ');
end;
看看这个程序过程为什么执行到一半就不对数据导入了
需求：利用database link 导入数据 操作三个表将另外一个数据库中同样表结构的数据抽取过来．为什么执行到一半就不做啦．．报错！！
[解决办法]
这么长……没耐心看了。报什么错？
把报的错帖出来直接对症下药好了
[解决办法]
出现了异常!
[解决办法]
..
