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

Answer 1

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 (',').

Answer 2

Use collect or write your own aggregation function.