ITPub博客

首页 > 数据库 > Oracle > ora-22990错误解决

ora-22990错误解决

原创 Oracle 作者:nmgzw 时间:2019-06-27 07:48:05 0 删除 编辑

问题发生的原因描述如下:

前提:表内存在数据msgid=-1的记录。

Session 1:执行后台作业pltcm,由于这个时候表内存在记录,所以执行存储过程内的update动作

Session 2: 执行delete 表名where msgid=-1;

Session 1:在第二次循环的时候,由于session 1没有提交,所以未能锁住数据,即vmsgid is null,所以执行insert操作,这时候的v_clob_pltcm仍然为上次循环执行update留下的定位器,所以会导致在执行insert这部分代码的时候,会产生跨事务使用lob定位器的错误,也就是ora-22990错误。

[@more@]

1、下面给出一个测试样例及解决办法:

1、 新建立测试表

Create table tcq (mstgid number,state number, payload clob);

2、建立测试存储过程如下:

CREATE OR REPLACE PROCEDURE pltcm
IS
v_buffer_pltcm CLOB;
v_clob_pltcm CLOB;
vmsgid NUMBER;
BEGIN
LOOP
BEGIN
SELECT msgid, payload
INTO vmsgid, v_clob_pltcm
FROM tcq
WHERE msgid = -1
FOR UPDATE NOWAIT;
EXCEPTION
WHEN OTHERS
THEN
IF (SQLCODE = -54)
THEN
DBMS_OUTPUT.put_line ('ERROR');
END IF;
END;

DBMS_OUTPUT.put_line ('wait for insert');
DBMS_LOCK.sleep (60);

IF vmsgid IS NULL
THEN
BEGIN
INSERT INTO tcq
(msgid, state, payload
)
VALUES (-1, 1, EMPTY_CLOB ()
)
RETURNING payload
INTO v_clob_pltcm;

v_buffer_pltcm :=
'M|P|'
|| TO_CHAR (SYSDATE, 'yyyymmdd||hh24miss')
|| TO_CHAR ('|PTCM|0041|3100|0|3100|||');
DBMS_LOB.WRITE (v_clob_pltcm,
LENGTH (v_buffer_pltcm),
1,
v_buffer_pltcm
);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
ELSE
v_buffer_pltcm :=
'M|P|'
|| TO_CHAR (SYSDATE, 'yyyymmdd||hh24miss')
|| TO_CHAR ('|PTCM|0041|3100|0|3100|||');
DBMS_LOB.WRITE (v_clob_pltcm,
LENGTH (v_buffer_pltcm),
1,
v_buffer_pltcm
);

UPDATE tcq
SET state = 1
WHERE msgid = -1;

DBMS_OUTPUT.put_line ('loop ' || DBMS_LOB.ISOPEN (v_clob_pltcm));
END IF;

COMMIT;
DBMS_OUTPUT.put_line ('loop out' || DBMS_LOB.ISOPEN (v_clob_pltcm));

DBMS_LOCK.sleep (60);
END LOOP;
END pltcm;

13、 分别打开两个会话:

Session1: exec pltcm;

Session2: delete tcq where msgid=-1;

过几秒就会在session1看到返回ora-22990错误

1、4、 解决办法,在程序开始处,通过dbms_lob在每次循环开始构造新的 lob定位器,修改程序代码如下/* Formatted on 2010/07/22 12:32 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE pltcm
IS
v_buffer_pltcm CLOB;
v_clob_pltcm CLOB;
vmsgid NUMBER;
cnt NUMBER;
BEGIN
LOOP
DBMS_LOB.createtemporary (v_clob_pltcm, TRUE, 2);
DBMS_LOB.createtemporary (v_buffer_pltcm, TRUE, 2);

SELECT COUNT (*)
INTO cnt
FROM tcq
WHERE msgid = -1;

IF cnt = 0
THEN
BEGIN
INSERT INTO tcq
(msgid, state, payload
)
VALUES (-1, 1, EMPTY_CLOB ()
)
RETURNING payload
INTO v_clob_pltcm;

v_buffer_pltcm :=
'M|P|'
|| TO_CHAR (SYSDATE, 'yyyymmdd||hh24miss')
|| TO_CHAR ('|PTCM|0041|3100|0|3100|||');
DBMS_LOB.WRITE (v_clob_pltcm,
LENGTH (v_buffer_pltcm),
1,
v_buffer_pltcm
);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
ELSE
BEGIN
SELECT msgid, payload
INTO vmsgid, v_clob_pltcm
FROM tcq
WHERE msgid = -1
FOR UPDATE NOWAIT;

v_buffer_pltcm :=
'M|P|'
|| TO_CHAR (SYSDATE, 'yyyymmdd||hh24miss')
|| TO_CHAR ('|PTCM|0041|3100|0|3100|||');
DBMS_LOB.WRITE (v_clob_pltcm,
LENGTH (v_buffer_pltcm),
1,
v_buffer_pltcm
);

UPDATE tcq
SET state = 1
WHERE msgid = -1;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END IF;

COMMIT;
END LOOP;
END pltcm;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25702/viewspace-1035418/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2002-10-18

  • 博文量
    71
  • 访问量
    65027