功能:
从发件箱取出待发送的定时邮件,取出后将取走的数据标记为已被取走(字段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;
解决方案 »
- 求取得表中的所有字段,并进行补位的存储过程
- 该怎么学习Oracle数据库
- 能否用一条sql语句实现下列逻辑?
- ORA-01461: can bind a LONG value only for insert into a LONG column
- 在sqlplusw中随便乱写select * from **** 返回结果:2 无论select什么结果都是2..... 即使不存的表也是
- 如何在触发器里再查找该表的数据?
- 求大神:如何监控oracle特定表的数据变化情况
- 各位,有没有MD5的加密函数啊,在ORACLE数据库中有的话发给我好了 [email protected]
- 奇怪!!!oracle占用了所以的linux系统CUP资源!
- oracle设置了访问白名单,白名单外ip访问记录
- 两个强悍的SQL需求,希望大家能给予宝贵意见
- 求一条SQL语句!!!谢谢大家!!!!!!!!!!!!!!!!
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;
我楼上的应该可以