ORA-22922 :nonexistent LOB value解决方案

https://blog.csdn.net/xinxiangsui2008/article/details/51701621

背景：JAVA使用JDBC方式查询语句报错
开发人员提交一个错误给我， 问我是否有其它的查询语句方式，错误如下：
uncategorized SQLException for SQL [

select *
  from (select TMP_RESULT.*, ROWNUM RN
          from (SELECT FILE_ID,
                       TO_CHAR(WMSYS.WM_CONCAT(DISTINCT B.name)) name,
                       MIN(B.LAST_AUDIT_USERCODE) LAST_AUDIT_USERCODE,
                       MIN(B.LAST_AUDIT_USERNAME) LAST_AUDIT_USERNAME
                  FROM test B
                 GROUP BY file_id) TMP_RESULT)
 where RN >= ?
   and RN < ? ];   
   
SQL state [ 99999 ];
error code [ 22922 ];
ORA-22922 :nonexistent LOB value 
初步怀疑是JAVA方面的问题，经过开发人员分析跟踪过程中发现：
select *
  from (select TMP_RESULT.*, ROWNUM RN
          from (SELECT FILE_ID,
                       TO_CHAR(WMSYS.WM_CONCAT(DISTINCT B.name)) name,
                       MIN(B.LAST_AUDIT_USERCODE) LAST_AUDIT_USERCODE,
                       MIN(B.LAST_AUDIT_USERNAME) LAST_AUDIT_USERNAME
                  FROM test B
                 GROUP BY file_id) TMP_RESULT)
 where RN >= 1
   and RN < 10 ; 
   
以上语句在PL/SQL里是不会报错，但真正报错的SQL如下：
select TMP_RESULT.*, ROWNUM RN
          from (SELECT FILE_ID,
                       TO_CHAR(WMSYS.WM_CONCAT(DISTINCT B.name)) name,
                       MIN(B.LAST_AUDIT_USERCODE) LAST_AUDIT_USERCODE,
                       MIN(B.LAST_AUDIT_USERNAME) LAST_AUDIT_USERNAME
                  FROM test B
                 GROUP BY file_id) TMP_RESULT)

既然是ORACLE的问题,先找找问题出在哪,由前至后的SQL执行基本可以确认是ROWNUM引起的问题，怎么办？
尝试使用hint:/*+ materialize */

select TMP_RESULT.*, ROWNUM RN
          from (SELECT /*+ materialize */
                       FILE_ID,
                       TO_CHAR(WMSYS.WM_CONCAT(DISTINCT B.name)) name,
                       MIN(B.LAST_AUDIT_USERCODE) LAST_AUDIT_USERCODE,
                       MIN(B.LAST_AUDIT_USERNAME) LAST_AUDIT_USERNAME
                  FROM test B
                 GROUP BY file_id) TMP_RESULT)
仍然报错,好吧，继续换方式
select TMP_RESULT.*, ROWNUM RN
from 
(
with tmp as
(SELECT /*+ materialize */
                       FILE_ID,
                       TO_CHAR(WMSYS.WM_CONCAT(DISTINCT B.name)) name,
                       MIN(B.LAST_AUDIT_USERCODE) LAST_AUDIT_USERCODE,
                       MIN(B.LAST_AUDIT_USERNAME) LAST_AUDIT_USERNAME
                  FROM test B
                 GROUP BY file_id
)
       select * from tmp
) TMP_RESULT
不报错，要求开发人员按以上方式改写查询SQL，搞定。
（虽然没法从ORACLE层面解释原理,个人认为这是BUG,不过还好,能从改写SQL的方式解决问题,也还是可以接受的.