wake-up-neo.com

alternative zu Listagg in Oracle?

listagg ist eine in Oracle 11.2 eingeführte Funktion! Jetzt nervt uns diese Funktion, wir migrieren von MySQL nach Oracle und wir haben diese Abfrage:

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;

dies funktioniert gut in MySQL, soweit wir wissen, was uns unter Oracle stört. Es gibt VARCAR und nicht CLOB zurück, wie wir es brauchen! Der Text ist riesig und wir brauchen es, um CLOB zu sein !

das habe ich versucht!

erstellen Sie eine CLOB_T-Tabelle vom Typ CLOB!

dann erstelle die funktion

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;

jetzt wenn ich es laufen lasse:

  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;

Ich bekomme

ORA-22814: Attribut- oder Elementwert ist größer als in Typ angegeben

gibt es eine Lösung dafür?

danke

14
Data-Base
16
Colin Pickard

WM_CONCAT hat für mich gearbeitet.

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

Ich habe es mit einem "Ersetzen" umwickelt, um ein anderes Elementtrennzeichen (';') als das von WM_CONCAT (',') verwendete anzugeben. 

3
Marco Zecca

Verwenden Sie xmlAgg. Das Beispiel wird unten gezeigt:

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

Dadurch wird ein Clob-Wert zurückgegeben, sodass keine benutzerdefinierte Funktion erstellt werden muss.

2
Ankur Bhutani

Möglicherweise möchten Sie benutzerdefinierte Aggregatfunktionen betrachten.

Verschiedene String-Aggregationstechniken werden gezeigt hier . Sie enthalten ein Beispiel für benutzerdefinierte Aggregatfunktionen.

2
Codo

Sie können den ORA-22814-Fehler beheben, indem Sie MULTISET anstelle von COLLECT verwenden:

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;
2
Jon Heller

- Erstellen eines Clobe-Typs - CREATE OR REPLACE TYPE "MSCONCATIMPL_CLOB" ALS OBJECT ( Ergebnisstring CLOB, Trennzeichen VARCHAR2 (10),

STATIC FUNCTION odciaggregateinitialize ( io_srccontext IN OUT msconcatimpl_clob ) RETURN NUMBER,

MEMBER FUNCTION odciaggregateiterate (
    self IN OUT msconcatimpl_clob,
    value   IN CLOB
) RETURN NUMBER,

MEMBER FUNCTION odciaggregateterminate (
    self            IN msconcatimpl_clob,
    o_returnvalue   OUT CLOB,
    i_flags         IN NUMBER
) RETURN NUMBER,

MEMBER FUNCTION odciaggregatemerge (
    self IN OUT msconcatimpl_clob,
    i_ctx2   IN msconcatimpl_clob
) RETURN NUMBER

); / - Erstellen eines Clus-Typs -

CREATE OR REPLACE TYPE KÖRPER "MSCONCATIMPL_CLOB" IST STATISCHE FUNKTION odciaggregateinitialize (io_srccontext IN OUT msconcatimpl_clob) RETURN NUMBER START. = msconcatimpl_clob ( NULL, NULL ); io_srccontext.delimiter: = ''; RETURN odciconst.success; ENDE odciaggregateinitialize;

MEMBER FUNCTION odciaggregateiterate (
    self IN OUT msconcatimpl_clob,
    value   IN CLOB
) RETURN NUMBER
    IS
BEGIN
    IF
        value IS NOT NULL
    THEN
        IF
            self.resultstring IS NULL
        THEN
            self.resultstring := self.resultstring || value;
        ELSE
            self.resultstring := self.resultstring
             || self.delimiter
             || value;
        END IF;
    END IF;

    RETURN odciconst.success;
END odciaggregateiterate;

MEMBER FUNCTION odciaggregateterminate (
    self            IN msconcatimpl_clob,
    o_returnvalue   OUT CLOB,
    i_flags         IN NUMBER
) RETURN NUMBER
    IS
BEGIN
    o_returnvalue := self.resultstring;
    RETURN odciconst.success;
END odciaggregateterminate;

MEMBER FUNCTION odciaggregatemerge (
    self IN OUT msconcatimpl_clob,
    i_ctx2   IN msconcatimpl_clob
) RETURN NUMBER
    IS
BEGIN
    IF
            self.resultstring IS NULL
        AND
            i_ctx2.resultstring IS NOT NULL
    THEN
        self.resultstring := i_ctx2.resultstring;
    ELSIF
        self.resultstring IS NOT NULL
    AND
        i_ctx2.resultstring IS NOT NULL
    THEN
        self.resultstring := self.resultstring
         || self.delimiter
         || i_ctx2.resultstring;
    END IF;

    RETURN odciconst.success;
END odciaggregatemerge;

ENDE;/

- Erstellen einer Clobe-Funktion -

CREATE OR REPLACE FUNKTION ms_concat_clob (Eingang VARCHAR2) RETURN CLOB PARALLEL_ENABLE AGGREGATE USING msconcatimpl_clob; /

0
Javed Khan