CODEWITHSUNDEEP
sqloracle-databasevarcharclob
Data-Base
Data-Base

Reputation: 8598

alternative to listagg in 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;

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

Upvotes: 16

Views: 68611

Answers (6)

Javed Khan
Javed Khan

Reputation: 11

-- Creating Clobe Type --
CREATE OR REPLACE TYPE "MSCONCATIMPL_CLOB" AS OBJECT (
    resultstring   CLOB,
    delimiter      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
);
/
-- Creating Clobe Type Body --

CREATE OR REPLACE TYPE BODY "MSCONCATIMPL_CLOB" IS
    STATIC FUNCTION odciaggregateinitialize ( io_srccontext IN OUT msconcatimpl_clob ) RETURN NUMBER
        IS
    BEGIN
        io_srccontext := msconcatimpl_clob(
            NULL,
            NULL
        );
        io_srccontext.delimiter := ' ';
        RETURN odciconst.success;
    END 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;

END;
/

-- Creating Clobe Function --

CREATE OR REPLACE FUNCTION ms_concat_clob ( input   VARCHAR2 ) RETURN CLOB
    PARALLEL_ENABLE
AGGREGATE USING msconcatimpl_clob;
/

Upvotes: 1

Ankur Bhutani
Ankur Bhutani

Reputation: 3219

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.

Upvotes: 5

Marco Zecca
Marco Zecca

Reputation: 57

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

Upvotes: 1

Codo
Codo

Reputation: 78825

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.

Upvotes: 2

Jon Heller
Jon Heller

Reputation: 36807

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;

Upvotes: 2

Colin Pickard
Colin Pickard

Reputation: 46643

Use collect or write your own aggregation function.

Upvotes: 18

Related Questions