使用 wm_concat() 获取 ORA-22922(不存在的 LOB 值)或根本没有结果 [英] Getting ORA-22922 (nonexistent LOB value) or no result at all with wm_concat()

问题描述

(使用 Oracle 11.2)

(Using Oracle 11.2)

我有一个类似

wm_concat( distinct abc )

预期返回一些varchar2(4000)兼容结果.

that is expected to return some varchar2(4000) compatible result.

它导致ORA-00932:在我的选择中使用了一些coalesce( some_varchar_col, wm_concat( ... ) ) 不一致的数据类型.

It causes ORA-00932: inconsistent datatypes in my select used in some coalesce( 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"

(在视图中使用，但玩弄它表明，它与视图无关)

(The are used in a view, but playing around with it suggests, it is not related to the views)

根据列和其他运算符，我要么得到 N) 无结果或 O) ORA-22922:

select * from view_with_above_included where rownum <= 100

• N) 我的 Eclipse 数据资源管理器 JDBC 连接返回没有任何结果(没有没有结果的列，没有 (0 行影响)，只有查询时间统计).(这可能是内部异常未被如此对待?)

  • N) My Eclipse Data Explorer JDBC connection returns without any result (no columns without results, no (0 rows effected), only the query time statistics). (It could be an internal exception not treated as such?)

    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 个字符的聚合数据.

  but looking at the actual aggregated data does not show aggregated data that would exceed 1000 characters in length.

  推荐答案

  幸运的是，它与 11.2 起提供的 listagg( ... ) 函数配合使用(我们已经在运行)，所以我们不必进一步调查:

  Luckily, it works with the listagg( ... ) function provided since 11.2 (we are already running on), so we did not have to investigate further:

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

  (众所周知，wm_concat(...) 是一些内部的、官方不支持的函数.)

  (Where wm_concat(...) is, as one should know, some internal and officially unsupported function.)

  a 相当不错的解决方案(因为它没有那么臃肿)实现 distinct 功能是通过自引用正则表达式功能，它应该在许多情况下工作:

  a rather nice solution (because it is not so bloated) to implement the distinct functionality is via self-referencing regexp functionality which should work in many cases:

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

  (也许/希望我们将来会看到一些有效的 listagg( distinct abc ) 功能，它们会像 wm_concat 语法一样非常简洁和酷.例如这个使用 Postgres 的 string_agg( distinct abc )¹ )

  (Maybe/Hopefully we will see some working listagg( distinct abc ) functionality in the future, which would be very neat and cool like the wm_concat syntax. E.g. this is no problem since a long time with Postgres' string_agg( distinct abc )¹ )

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

  <小时>

  如果列表超过4000个字符，一个不能再使用listagg(再次ORA-22922).但幸运的是，我们可以在此处使用 xmlagg 函数(如此处所述).如果您想在此处在 4000 个字符截断的结果上实现不同，您可以注释(1)-marked线条.

  ---

  If the list exceeds 4000 characters, one cannot use listagg anymore (ORA-22922 again). But luckily we can use the xmlagg function here (as mentioned here). If you want to realize a distinct on a 4000-chars-truncated result here, you could outcomment the (1)-marked lines.

  -- 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
  

  这篇关于使用 wm_concat() 获取 ORA-22922(不存在的 LOB 值)或根本没有结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！