ora-22990错误解决

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

前提：表内存在数据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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25702/viewspace-1035418/