ORA-22922: nonexistent LOB value

ORA-22922: nonexistent LOB value

1 现象及错误信息

项目现场反馈报表查询系统执行一个查询并导出的功能，无法将查询结果导出，报错：文件太大，不能作为附件。而实际上数据量是很小的。

同时发来了执行的SQL语句。我连上环境执行，发现报的是ORA-22922错误。错误信息如下：

oerr ora 22922
22922, 00000, "nonexistent LOB value"
//  *Cause:  The LOB value associated with the input locator does not exist.
//           The information in the locator does not refer to an existing LOB.
//  *Action: Repopulate the locator by issuing a select statement and retry
//           the operation.

按照官方的提示信息来看，是在根据一定条件查询LOB时，没有找到LOB对象。 sql 语句由于是涉及到了业务表和逻辑，这里不展示。

2 分析

该SQL从一个视图中查询数据。而从视图本身来看是没有LOG对象的。

   select column_name,data_type from dba_tab_cols where owner='REP' AND TABLE_NAME='&view_name';

COLUMN_NAME          DATA_TYPE
-------------------- --------------------
CUSTOMER_NAME        VARCHAR2
CUSTOMER_ID          NUMBER
CUSTOMER_CODE        VARCHAR2
INVOICE_NO           VARCHAR2
BOOK_PAY             NUMBER
CASH_PAY             NUMBER
AMOUNT               NUMBER
PAYMENT_NO           VARCHAR2
NUM                  NUMBER
PAY_TYPE             VARCHAR2
CHG_ITEM_NAME        VARCHAR2
PAYMENT_DATE         DATE
ORG_NAME             VARCHAR2
OP_NAME              VARCHAR2
ACTION_NAME          VARCHAR2
OFFICE_ORG_ID        NUMBER
OPERATOR_ID          NUMBER

继续往下挖，这里面肯定是有LOB对象的。 发现该视图是从另外一张视图里查询的，继续挖， 最后发现一个视图的创建语句如下：

  select .. column_list ...
from rep.view_sk_1     t
left join 
( select  PAYMENT_NO, to_char(wm_concat(d.invoice_no))    invoice_no
from ....

这个视图里有 wm_concat 函数，该函数的返回值是LOB类型。 经测试，将该行注释掉以后，可以正常查询，至此可以肯定， 问题就是由该函数引起的。

wm_concat 函数是一个列转行的函数，将一列的多行数据转化为一行数据，比如

  salary
-------
  10000
   9000
   8500

经wm_concat 转换 select wm_concat(salary) from employee where depno=20; 可以转换成

  salary
-------
10000,9000,8500

并且以lob类型返回 。 在视图的创建语句中，也发现, 使用了to_char ，看来研发也注意到了这个问题，避免了使用LOB类型。 可是，他们并不知道的是, wm_concat 由于是Lob 类型，是不能进行group by ,distinct 以及union 共存的，因为会偶发ORA-22922错误 。 这里需要注意，是偶发，不是必然。这也算是oracle 的一个小bug吧。

在Oracle 11G 中，新推出了一个函数有相似的功能是listagg(column_name,'seprator') within group ( order by ..)

3 解决

将 to_char(wm_concat(d.invoice_no)) invoice_no 使用listagg 替换为 LISTAGG(d.invoice_no,',') WITHIN GROUP (ORDER BY PAYMENT_NO) INVOICE_NO 。 经测试，报错不再发生 。替换后的视图创建语句如下:

select  .. column_list ..
from rep.view_sk_1     t
left join
( select  PAYMENT_NO, LISTAGG(d.invoice_no,',') WITHIN GROUP (ORDER BY PAYMENT_NO)  invoice_no
from .....

4 知识总结

出现这个错误很多时候是使用了wmsys.wm_concat的同时使用了group ,distinct 或者union， 本来两者没有问题，问题在于：

  1. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0以后的版本wmsys.wm_concat查询出的是LOB类型
  2. oralce的SQL语句中若查询了LOB字段是不能使用distinct，union，和group by等关键字的。
  3. 并且在12C 以后，Oracle官方软件不再提供wm_concat函数，因此从兼容性上来看，也不建议使用wm_concat.

因此，以后在编写PLSQL时，留意一下该问题即可。

Author: halberd.lee

Created: 2020-07-01 Wed 21:11

Validate

posted @ 2020-07-01 21:12  halberd.lee  阅读(3785)  评论(0)  编辑  收藏  举报