oracle11g – 使用wm_concat()获取ORA-22922(不存在的LOB值)或根

我有一个相当复杂的SQL

wm_concat( distinct abc )

预计会返回一些varchar2(4000)兼容的结果.

它导致ORA-00932：在某些coalesce中使用的select中的数据类型不一致(some_varchar_col,wm_concat(…)).

所以我尝试通过两种不同的方法来构建它：

dbms_lob.substr( ...,4000 )  -- L) tried even with 3000 in case of "unicode byte blow-up"
cast( ... as varchar2(4000))  -- C) tried even with 3000 in case of "unicode byte blow-up"

(在视图中使用,但玩它表示,它与视图无关)

根据列和其他运算符,我得到N)没有结果或O)ORA-22922：

select * from view_with_above_included where rownum <= 100

> N)我的Eclipse Data Explorer JDBC连接返回时没有任何结果(没有没有结果的列,没有(0行受影响),只有查询时间统计信息). (这可能是一个内部例外,不是这样处理的？)
> O)

ORA-22922: nonexistent LOB value
ORA-06512: in "SYS.DBMS_LOB",line 1092
ORA-06512: in line 1

奇怪的是,以下测试查询工作：

-- rownum <= 100 would already cause the above problems
select * from view_with_above_included where rownum <= 10

要么

select * from view_with_above_included

但是查看实际的聚合数据并不会显示长度超过1000个字符的聚合数据.

listagg( abc,',' ) within group ( order by abc )

(正如人们应该知道的那样,wm_concat(…)是一些内部和官方不支持的功能.)

实现不同功能的rather nice solution(因为它不是那么臃肿)是通过自引用正则表达式功能实现的,这在许多情况下应该起作用：

regexp_replace( 
  listagg( abc,' ) within group ( order by abc ),'(^|,)(.+)(,2)+','12' )

(也许/希望我们将来会看到一些有效的listagg(不同的abc)功能,它会像wm_concat语法一样非常整洁和酷.例如,这是没有问题,因为很长一段时间与Postgres的string_agg(distinct abc)1)

-- 1: postgres sql example:
select string_agg( distinct x,' ) from unnest('{a,b,a}'::text[]) as x`

如果列表超过4000个字符,则不能再使用listagg(再次使用ORA-22922).
但幸运的是我们可以在这里使用xmlagg函数(如here所述).
如果你想在这里实现4000-chars截断结果的不同,你可以取消注释(1)标记的行.

-- in smallercase everything that could/should be special for your query
-- comment in (1) to realize a distinct on a 4000 chars truncated result
WITH cfg AS ( 
  SELECT 
    ','                  AS list_delim,'([^,]+)(,1)*(,|$)' AS list_dist_match,-- regexp match for distinct functionality
    '13'               AS LIST_DIST_REPL  -- regexp replace for distinct functionality
  FROM DUAL
)
SELECT
  --REGEXP_REPLACE( DBMS_LOB.SUBSTR(             -- (1)
  RTRIM( XMLAGG( XMLELEMENT( E,mycol,listdelim ).EXTRACT('//text()') 
  ORDER BY mycol ).GetClobVal(),LIST_DELIM ) 
  --,4000 ),LIST_DIST_MATCH,LIST_DIST_REPL )  -- (1)
  AS mylist
FROM mytab,CFG

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!