ORA-01001: invalid cursorcreate or replace procedure DRAG_DATA_FROM_LCAM AS
v_ErrorText VARCHAR2(200);
EXCEPTION_ERROR VARCHAR2(200);
EXCEPTION_SUCCESS VARCHAR2(4000); FLAGDATE DATE;
SUMCOUNT NUMBER; --插入记录数begin
SUMCOUNT:= 0;
select max(l.successdate)
INTO FLAGDATE
from lcam_to_fwms l
where L.MARK = 'Y';
-- 资产数据插入临时表 设备表
--特征值
for v_row in (select A.*
from V_AM_ASSET_FEATURE_VALUE A
WHERE to_char(A.MODIFY_DATE, 'yyyy-mm-dd hh24:mi:ss') >
to_char(FLAGDATE, 'yyyy-mm-dd hh24:mi:ss')) LOOP
EXCEPTION_ERROR := '插入特征值临时表异常';
DELETE PUB_FEATURE_VALUE_TEST
WHERE FEATURE_VALUE_ID = SUBSTR(v_row.Feature_Value_Id, 5);
INSERT INTO PUB_FEATURE_VALUE_TEST
(FEATURE_VALUE_ID,
FEATURE_VALUE,
OBJECT_TYPE,
OBJECT_ID,
CLASSIFY_ID,
CLASSIFY_FEATURE_ID,
CREATOR_ID,
CREATOR)
VALUES
(SUBSTR(v_row.Feature_Value_Id, 5),
v_row.Feature_Value, --featureValue,
v_row.Object_Type, --objectType,
substr(v_row.Object_Id, 5),
(select min(pub_classify.classify_id)
from pub_classify
where pub_classify.alias_name = v_row.Alias_Name
and pub_classify.class_group_id = 1),
SUBSTR(v_row.Classify_Feature_Id, 5),
v_row.Creator_Id, --creatorId,
v_row.creator);
END LOOP;
....(20个类似这种for 的语句)
commit;
存储过程是在DBLINK的基础上使用的。。操作的数据多的时候一般就是上w条。。先突然报这个错误,求解~~~
v_ErrorText VARCHAR2(200);
EXCEPTION_ERROR VARCHAR2(200);
EXCEPTION_SUCCESS VARCHAR2(4000); FLAGDATE DATE;
SUMCOUNT NUMBER; --插入记录数begin
SUMCOUNT:= 0;
select max(l.successdate)
INTO FLAGDATE
from lcam_to_fwms l
where L.MARK = 'Y';
-- 资产数据插入临时表 设备表
--特征值
for v_row in (select A.*
from V_AM_ASSET_FEATURE_VALUE A
WHERE to_char(A.MODIFY_DATE, 'yyyy-mm-dd hh24:mi:ss') >
to_char(FLAGDATE, 'yyyy-mm-dd hh24:mi:ss')) LOOP
EXCEPTION_ERROR := '插入特征值临时表异常';
DELETE PUB_FEATURE_VALUE_TEST
WHERE FEATURE_VALUE_ID = SUBSTR(v_row.Feature_Value_Id, 5);
INSERT INTO PUB_FEATURE_VALUE_TEST
(FEATURE_VALUE_ID,
FEATURE_VALUE,
OBJECT_TYPE,
OBJECT_ID,
CLASSIFY_ID,
CLASSIFY_FEATURE_ID,
CREATOR_ID,
CREATOR)
VALUES
(SUBSTR(v_row.Feature_Value_Id, 5),
v_row.Feature_Value, --featureValue,
v_row.Object_Type, --objectType,
substr(v_row.Object_Id, 5),
(select min(pub_classify.classify_id)
from pub_classify
where pub_classify.alias_name = v_row.Alias_Name
and pub_classify.class_group_id = 1),
SUBSTR(v_row.Classify_Feature_Id, 5),
v_row.Creator_Id, --creatorId,
v_row.creator);
END LOOP;
....(20个类似这种for 的语句)
commit;
存储过程是在DBLINK的基础上使用的。。操作的数据多的时候一般就是上w条。。先突然报这个错误,求解~~~
问题都已经给你提示了 你写的 for i in (select ......) 的某个for 有问题有问题
for v_row in (select A.*
from V_AM_ASSET_FEATURE_VALUE A
WHERE to_char(A.MODIFY_DATE, 'yyyy-mm-dd hh24:mi:ss') >
to_char(FLAGDATE, 'yyyy-mm-dd hh24:mi:ss')) --直接这样比
A.MODIFY_DATE >FLAGDATE