功能:
从发件箱取出待发送的定时邮件,取出后将取走的数据标记为已被取走(字段get_flag更新为"MOVED")。结果:
运行后取走的数据更新正常,但是游标返回后报错"ORA-01002 违反读取顺序错误"请大侠给出解决方法,高分答谢CREATE OR REPLACE PROCEDURE P_VMS_QUERYTIMINGMAIL(
i_QueryCount IN NUMBER,
o_Ret OUT NUMBER, -- 返回结果码 0 成功 1失败
o_TimingMailTRef OUT SYS_REFCURSOR -- 待发送邮件列表
) AS v_MailinfoRec t_outbox_info%ROWTYPE;BEGIN
OPEN o_TimingMailTRef FOR SELECT * FROM t_mbx_outbox_mailinfo t
WHERE t.is_timing = 1
AND t.get_flag IS NULL
AND t.mail_status = 2
AND t.send_time <= SYSDATE
AND ROWNUM <= i_QueryCount
AND PARTCOL_USERNO = SUBSTR(USERNO, -3, 2);
LOOP
FETCH o_TimingMailTRef
INTO v_MailinfoRec;
-- 修改用户的定时发送状态
--UPDATE t_mbx_outbox_mailinfo SET get_flag = 'MOVED' WHERE mailid = v_MailinfoRec.Mailid;
-- COMMIT;
EXIT WHEN o_TimingMailTRef%NOTFOUND;
END LOOP; o_Ret := 0;EXCEPTION
WHEN OTHERS THEN
o_Ret := 1;
IF o_TimingMailTRef%ISOPEN THEN
CLOSE o_TimingMailTRef;
END IF;
OPEN o_TimingMailTRef FOR
SELECT NULL FROM dual WHERE 1 = 0;
P_COMM_ADDEXCEPTION('P_VMS_QUERYTIMINGMAIL', SQLERRM, SQLCODE, NULL);
END P_VMS_QUERYTIMINGMAIL;
从发件箱取出待发送的定时邮件,取出后将取走的数据标记为已被取走(字段get_flag更新为"MOVED")。结果:
运行后取走的数据更新正常,但是游标返回后报错"ORA-01002 违反读取顺序错误"请大侠给出解决方法,高分答谢CREATE OR REPLACE PROCEDURE P_VMS_QUERYTIMINGMAIL(
i_QueryCount IN NUMBER,
o_Ret OUT NUMBER, -- 返回结果码 0 成功 1失败
o_TimingMailTRef OUT SYS_REFCURSOR -- 待发送邮件列表
) AS v_MailinfoRec t_outbox_info%ROWTYPE;BEGIN
OPEN o_TimingMailTRef FOR SELECT * FROM t_mbx_outbox_mailinfo t
WHERE t.is_timing = 1
AND t.get_flag IS NULL
AND t.mail_status = 2
AND t.send_time <= SYSDATE
AND ROWNUM <= i_QueryCount
AND PARTCOL_USERNO = SUBSTR(USERNO, -3, 2);
LOOP
FETCH o_TimingMailTRef
INTO v_MailinfoRec;
-- 修改用户的定时发送状态
--UPDATE t_mbx_outbox_mailinfo SET get_flag = 'MOVED' WHERE mailid = v_MailinfoRec.Mailid;
-- COMMIT;
EXIT WHEN o_TimingMailTRef%NOTFOUND;
END LOOP; o_Ret := 0;EXCEPTION
WHEN OTHERS THEN
o_Ret := 1;
IF o_TimingMailTRef%ISOPEN THEN
CLOSE o_TimingMailTRef;
END IF;
OPEN o_TimingMailTRef FOR
SELECT NULL FROM dual WHERE 1 = 0;
P_COMM_ADDEXCEPTION('P_VMS_QUERYTIMINGMAIL', SQLERRM, SQLCODE, NULL);
END P_VMS_QUERYTIMINGMAIL;
Cause: This error means that a fetch has been attempted from a cursor which is no longer valid. Note that a PL/SQL cursor loop implicitly does fetches, and thus may also cause this error. There are a number of possible causes for this error, including: 1) Fetching from a cursor after the last row has been retrieved and the ORA-1403 error returned. 2) If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued will return the error. 3) Rebinding any placeholders in the SQL statement, then issuing a fetch before reexecuting the statement.
Action: 1) Do not issue a fetch statement after the last row has been retrieved - there are no more rows to fetch. 2) Do not issue a COMMIT inside a fetch loop for a cursor that has been opened FOR UPDATE. 3) Reexecute the statement after rebinding, then attempt to fetch again.
谢谢! LOOP
EXIT WHEN o_TimingMailTRef%NOTFOUND;
FETCH o_TimingMailTRef
INTO v_MailinfoRec;
-- 修改用户的定时发送状态
--UPDATE t_mbx_outbox_mailinfo SET get_flag = 'MOVED' WHERE mailid = v_MailinfoRec.Mailid;
-- COMMIT;
END LOOP;
FETCH o_TimingMailTRef
INTO v_MailinfoRec;
-- 修改用户的定时发送状态
EXIT WHEN o_TimingMailTRef%NOTFOUND;
UPDATE t_mbx_outbox_mailinfo SET get_flag = 'MOVED' WHERE mailid = v_MailinfoRec.Mailid;
END LOOP;
COMMIT;
我楼上的应该可以