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

问题描述

（使用Oracle 11.2）

我有一个非常复杂的SQL，例如

  wm_concat（distinct abc）
  

返回一些 varchar2（4000）兼容结果。

$ c> ORA-00932：不一致的数据类型在我的选择用于一些 coalesce（some_varchar_col，wm_concat（...））。

---

因此，我通过两种不同的方法尝试投放：

  dbms_lob.substr（...，4000） -  L）在unicode byte blow-up的情况下尝试了3000 b $ b cast（... as varchar2（4000）） -  C）在unicode字节炸毁的情况下尝试3000。
   
 
（在视图中使用，但是用它建议，它与视图无关）
 
 
 
 根据列和其他操作符，我得到N）没有结果或O） ORA-22922  ：
  select * from view_with_above_included其中rownum <= 100 
  
 $我的Eclipse数据浏览器JDBC连接返回没有任何结果（没有列没有结果，没有（0行受影响））$ e 
 
 
 < ，只有查询时间统计）。 （这可能是一个内部异常，不会被视为这样？）
  
 
 
 O）
  ORA-22922：nonexistent LOB值
 ORA-06512：在SYS.DBMS_LOB中，第1092行
 ORA-06512：第1行
  
 
 
  
 
 
 
 
 
 测试查询工作： 
   -  rownum< = 100以上问题
 select * from view_with_above_included其中rownum <= 10 
  
或
  select * from view_with_above_included 
  
但是查看实际的汇总数据不会显示长度超过1000个字符的汇总数据。
解决方案<幸运的是，它适用于 listagg（...）函数，因为 11.2  （我们已在运行），因此我们无需进一步调查：
  listagg（abc，'，'）in group（order by abc）
  
 c $ c> wm_concat（...）是，应该知道，一些内部和官方不支持的函数。）
 
 
 
 
 
 
 a  相当不错的解决方案 （因为它不那么so肿）实现 distinct 功能是通过自引用regexp功能在许多情况下工作：
  regexp_replace（
 listagg（abc，'，'）in group（order by abc）
，'（^ |，）（。+）（，\2）+'，'\1\2'）
  
 
 $ b b 
（也许/希望我们将看到一些有效的 listagg（distinct abc）功能，这将是非常整洁和酷像 wm_concat 语法。例如。这是没有问题，因为很长时间与Postgres的 string_agg（distinct abc）  1 ）
 
 
   

---

如果列表超过4000个字符，则不能再使用 listagg （ 22922 ）。
但幸运的是，我们可以在这里使用 xmlagg 函数（如这里）。
如果您想在4000个字符截断的结果上实现 distinct ，您可以取消<$> c $ c>（1）标记的行。

   - 在smallercase一切可能/应该是您的查询特殊的
  - 注释（1）实现一个截然不同的4000字符截断结果
 WITH cfg AS（
 SELECT 
'，'AS list_delim，
'（[^，] +）（，\1）*（，| $）'as list_dist_match， - 不同功能的regexp匹配
 '\1\3'AS LIST_DIST_REPL  -  regexp替换不同的功能
 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 
来自mytab，CFG 
  

(Using Oracle 11.2)

I have a rather complicated SQL with something like

wm_concat( distinct abc )

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

It causes ORA-00932: inconsistent datatypes in my select used in some coalesce( some_varchar_col, wm_concat( ... ) ).

---

So I tried casting it via two different methods:

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)

Depending on the column and other operators I either get N) no result or O) ORA-22922:

select * from view_with_above_included where rownum <= 100

• 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
  

---

Strangely the following test queries work:

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

or

select * from view_with_above_included

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

解决方案

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 )

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

---

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)+', '\1\2' )

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

---

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
    '\1\3'               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屋！