我有以下代码摘录，它将使用触发器和要由触发器调用的存储过程将数据插入三个表中。触发器和过程都已成功编译。
CREATE OR REPLACE TRIGGER trigger_insert_attr AFTER
INSERT ON BSCS_WORK_SYNC_INFO FOR EACH ROW BEGIN
bscs_rateplan_sync(:new.project_id , :new.tmcode);
update BSCS_WORK_SYNC_INFO set comp_date=SYSDATE where
project_id=:new.project_id;
END trigger_insert_attr;
/
CREATE OR REPLACE
PROCEDURE bscs_rateplan_sync
(
tmcode_list IN VARCHAR2,
project_id IN VARCHAR2
)
AS
BEGIN
EXECUTE IMMEDIATE 'delete from ecm_mpulktm1 where
project_id='||project_id ;
EXECUTE IMMEDIATE 'delete from ecm_mpulktm2 where
project_id='||project_id ;
EXECUTE IMMEDIATE 'delete from ecm_fup_tariff_work where
project_id='||project_id ;
EXECUTE immediate 'insert into ecm_mpulktm1 select '||project_id||' , m1.*
from sysadm.mpulktm1@to_bscsprd_rpt m1 where tmcode in
('||tmcode_list||')'
;
EXECUTE immediate 'insert into ecm_mpulktm2 select '||project_id||' , m1.*
from sysadm.mpulktm2@to_bscsprd_rpt m1 where tmcode in
('||tmcode_list||')'
;
EXECUTE immediate 'insert into ecm_fup_tariff_work select '||project_id||'
, m1.* from sysadm.fup_tariff_work@to_bscsprd_rpt m1 where tmcode in
('||tmcode_list||')' ;
END;
/
但是，当数据插入表BSCS_WORK_SYNC_INFO时，出现以下错误：
SQL错误：ORA-00933：SQL命令未正确结束ORA-06512：在“ ECMREPORT.BSCS_RATEPLAN_SYNC”，第6行ORA-06512：在“ ECMREPORT.TRIGGER_INSERT_ATTR”，第2行ORA-04088：在执行触发器'ECMREPORT时出错。 TRIGGER_INSERT_ATTR'
有人可以帮助解决该错误吗？我对PL / SQL相对较新，因此会请求帮助来解决上述错误。表BSCS_WORK_SYNC_INFO中的数据将由Web服务填充。该过程执行后，还要更新表中的数据。
“快速而肮脏的”修复
CREATE OR REPLACE TRIGGER trigger_insert_attr
AFTER INSERT ON BSCS_WORK_SYNC_INFO FOR EACH ROW
BEGIN
bscs_rateplan_sync(:new.project_id, :new.tmcode);
update bscs_work_sync_info set comp_date = SYSDATE where project_id = :new.project_id;
END trigger_insert_attr;
/
CREATE OR REPLACE
PROCEDURE bscs_rateplan_sync
( tmcode_list IN VARCHAR2
, project_id IN VARCHAR2 )
AS
BEGIN
delete from ecm_mpulktm1 where project_id = bscs_rateplan_sync.project_id;
delete from ecm_mpulktm2 where project_id = bscs_rateplan_sync.project_id;
delete from ecm_fup_tariff_work where project_id = bscs_rateplan_sync.project_id;
execute immediate '
insert into ecm_mpulktm1
select :project_id, m1.*
from sysadm.mpulktm1@to_bscsprd_rpt m1
where tmcode in ('||nvl(bscs_rateplan_sync.tmcode_list,'null')||')'
using in bscs_rateplan_sync.project_id;
execute immediate '
insert into ecm_mpulktm2
select :project_id, m1.*
from sysadm.mpulktm2@to_bscsprd_rpt m1
where tmcode in ('||nvl(bscs_rateplan_sync.tmcode_list,'null')||')'
using in bscs_rateplan_sync.project_id;
execute immediate '
insert into ecm_fup_tariff_work
select :project_id, m1.*
from sysadm.fup_tariff_work@to_bscsprd_rpt m1
where tmcode in ('||nvl(bscs_rateplan_sync.tmcode_list,'null')||')'
using in bscs_rateplan_sync.project_id;
END;
/
适当的修复
... 将会
bscs_rateplan_sync.tmcode_list到集合中并将该集合绑定到静态SQL来完全删除动态SQL，bscs_work_sync_info.comp_date到更新bscs_work_sync_info()存储的proc并声明触发器以调用存储的proc而不是匿名PLSQL块。本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句