ORA-01002 违反读取顺序错误(在线等，高分答谢)

[CD2]

功能:
从发件箱取出待发送的定时邮件，取出后将取走的数据标记为已被取走(字段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;

[Richard345265669]

支持一下!

[Adebayor]

顶

[Tomac]

ORA-01002: fetch out of sequence
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.

[Tomac]

更改为下面的实验下.
谢谢!

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;

[CD2]

楼上的兄台，我必须做UPDATE，你怎么能给我注释掉呢？

[doer_ljy]

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;

[robin_ares]

你想呀，你更新一条记录，你的查询语句就可能会多查出一条记录
这样循环的次数就和缓存里游标的条数不一致了

[robin_ares]

根据4楼，应该是説在游标打开时，不要做COMMIT
我楼上的应该可以

[Tomac]

[Quote=引用 5 楼 CD2 的回复:]
楼上的兄台，我必须做UPDATE，你怎么能给我注释掉呢？
[/Quote]

仔细一点看吧,你自己给出的代码就是注释掉的,我只是复制而已.