Получение ORA-22922 (несуществующее значение большого объекта) или вообще никакого результата с помощью wm_concat()

Question

(С использованием Oracle 11.2)

У меня довольно сложный SQL с чем-то вроде

wm_concat( distinct abc )

что ожидается вернуть некоторые varchar2(4000) совместимый результат.

Это приводит ORA-00932: inconsistent datatypes в моем выборе используется в некоторых 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"

(Они используются в представлении, но, поэкспериментируя с ним, можно предположить, что это не связано с представлениями)

В зависимости от столбца и других операторов я либо получаю N), либо нет результата, либо O) ORA-22922:

select * from view_with_above_included where rownum <= 100

• N) Мое соединение JDBC Eclipse Data Explorer возвращается без какого-либо результата (нет столбцов без результатов, нет (0 rows effected), только статистика времени запроса). (Это может быть внутреннее исключение, которое не рассматривается как таковое?)

• 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 символов.

Answer 1

К счастью, это работает с listagg( ... ) функция предоставляется с 11.2 (мы уже работаем), поэтому нам не нужно было продолжать расследование:

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

(Куда wm_concat(...) это, как следует знать, некоторая внутренняя и официально не поддерживаемая функция.)

---

довольно хорошее решение (потому что оно не так раздуто) для реализации distinct функциональность через самореференсную функцию регулярного выражения, которая должна работать во многих случаях:

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

(Может быть / Надеемся, мы увидим некоторые рабочие listagg( distinct abc ) функциональность в будущем, которая была бы очень опрятной и крутой, как wm_concat синтаксис. Например, это не проблема, так как долгое время с 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 функция здесь (как упомянуто здесь). Если вы хотите реализовать distinct на усеченный результат в 4000 символов, вы можете превзойти (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
    '\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