Getting ORA-22922 (nonexistent LOB value) or no result at all with wm_concat()

Question

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

Answer 1

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