在Oracle中替代listagg? [英] alternative to listagg in Oracle?

问题描述

listagg是Oracle 11.2中引入的功能！现在，此功能正在分配给我们，我们正在从MySQL迁移到Oracle，并得到以下查询:

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;

据我们所知，

在MySQL中运行良好 在Oracle下困扰我们的是它返回VARCAR而不是我们需要的CLOB！ 文本很大，我们确实需要CLOB ！

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!

这是我想做的！

创建CLOB类型的CLOB_T表！

create a CLOB_T table of CLOB Type!

然后创建函数

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;

现在是否运行它:

  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;

我知道

ORA-22814:属性或元素值大于类型中指定的值

有什么解决办法吗?

谢谢你

推荐答案

使用收集或编写您自己的聚合函数.

这篇关于在Oracle中替代listagg?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！