alternative to listagg in Oracle?

Question

listagg is a function introduced in Oracle 11.2! now this function is bugging us allot, we are migrating from MySQL to Oracle and we have this query:

SELECT
    p_id,
    MAX(registered) AS registered,
    listagg(MESSAGE, ' ') within GROUP (ORDER BY registered) AS MESSAGE
  FROM
    umm_parent_id_remarks_v m
  GROUP BY
    m.p_id;

is works fine in MySQL as far as we know what bugging us is under Oracle it returns VARCAR and not CLOB as we need! the text is huge and we do need it to be CLOB!

here is what I tried to do!

create a CLOB_T table of CLOB Type!

then create the function

create or replace
function listaggclob (t in clob_t) 
  return clob
as 
  ret clob := '';
  i   number;
begin
  i := t.first;
  while i is not null loop
    if ret is not null then
      ret := ret || ' ';
    end if;
    ret := ret || t(i);
    i := t.next(i);
  end loop;
  return ret;
end;

now if I run it:

  SELECT
        p_id,
        MAX(registered) AS registered,
        listaggclob(cast(collect (MESSAGE) as clob_t)) MESSAGE
      FROM
        umm_parent_id_remarks_v m
      GROUP BY
        m.p_id;

I get

ORA-22814: attribute or element value is larger than specified in type

is there any solution for it?

thanks you

Solution 1

Use collect or write your own aggregation function.

Solution 2

WM_CONCAT worked for me.

SELECT replace(WMSYS.WM_CONCAT(myTable.name), ',', ';')
FROM myTable
GROUP BY myTable.id

I wrapped it with a "replace" to specify a different item separator (';') from the one used by WM_CONCAT (',').

Solution 3

Use xmlAgg, example is shown below:

SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()') ORDER BY colname).GetClobVal(),',') AS LIST
FROM tablename;

This will return clob value and so no need to create custom function.

Solution 4

You might want to look at user-defined aggregate functions.

Differnt string aggregation techniques are shown here. They include an example for user-defined aggregate functions.

Solution 5

You can solve the ORA-22814 error by using MULTISET instead of COLLECT:

SELECT
    p_id,
    MAX(registered) AS registered,
    listaggclob(cast(multiset(
        select MESSAGE
        from umm_parent_id_remarks_v
        where umm_parent_id_remarks_v.p_id = m.p_id
    ) as clob_t)) MESSAGE
  FROM
    umm_parent_id_remarks_v m
  GROUP BY
    m.p_id;

View more solutions

Comments

[Data-Base]

I did write my own function and I updated my question, please review my question again, thanks

[N West]

Good set of options there. Is there any info on which are faster?

[Codo]

I don't have any info about performance. I guess it mainly depends on the overall execution plan and how they fit into that execution plan. So it might differ from query to query.

[Colin Pickard]

I don't know why you're getting that error - it's probably worth posting it as a new question though, you will get more people looking at it that way.

[Jon Heller]

Note that WM_CONCAT is not available in 12c, Express Edition, and any database where Workspace Manager is not installed.

[Anyul Rivas]

it is not supported nor documented nowadays

[viv kumar]

XMLAGG is very slow.

[Ankur Bhutani]

Is there any fast alternative?