78

I'm using Oracle SQL developer version 3.0.04. I attempted to use the function LISTAGG to group the data together..

    CREATE TABLE FINAL_LOG AS
    SELECT SESSION_DT, C_IP, CS_USER_AGENT,
    listagg(WEB_LINK, ' ')
        WITHIN GROUP(ORDER BY C_IP, CS_USER_AGENT) "WEB_LINKS"
        FROM webviews
        GROUP BY C_IP, CS_USER_AGENT, SESSION_DT
        ORDER BY SESSION_DT

However, I keep getting the error,

SQL Error: ORA-01489: result of string concatenation is too long

I'm pretty sure that the output may be more than 4000, since the WEB_LINK mentioned here is a concatenated value of url stem and url query.

Is there any way to go around it or is there any other alternative?

Derrick
  • 3,669
  • 5
  • 35
  • 50
user1874311
  • 843
  • 1
  • 11
  • 17

14 Answers14

102

You can accomplish similar functionality with the XMLAGG function:

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

This will return a clob value, so no limit on rows.

Jeromy French
  • 11,812
  • 19
  • 76
  • 129
Ankur Bhutani
  • 3,079
  • 4
  • 29
  • 26
  • 12
    Would be nice to see explanation on how this works. It takes quite a lot time with documentation. – miracle_the_V Jun 09 '15 at 08:28
  • 9
    this is a nice trick, but be aware that it seems to convert html special chars (eg, instead of ' you get ' ) – tbone Sep 08 '15 at 18:10
  • 8
    There is a limit in 11g/12c: 176TB per clob :) – Florin Ghita Dec 02 '15 at 06:54
  • @tbone XML special characters, probably. :) – GolezTrol Jul 12 '16 at 12:47
  • if you want to realize a **`distinct`** on a 4000 chars truncated result you could have a look here: http://stackoverflow.com/a/39325808/1915920 – Andreas Covidiot Sep 23 '16 at 12:36
  • 8
    someone please explain this answer. what is the point of `.EXTRACT('//text()')` and why `XMLELEMENT(E,colname,',')` received three parameter – Arashsoft Oct 20 '16 at 18:07
  • Try XMLAGG(XMLCDATA(colname)) to avoid & issue – snipsnipsnip Jan 31 '18 at 00:03
  • 21
    Just as a note, I tried this and the query **CRASHED OUT ENTIRE ORACLE INSTANCE**. Whoops. We're running Oracle Database 12c Release 12.1.0.1.0 - 64bit on Windows. So maybe, unlike me, try it in a non-production environment first. – erikbozic Feb 05 '18 at 13:50
  • This worked for me! A good and short solution instead of using user defined functions and all that.. – Delin Mathew Sep 24 '18 at 14:30
  • @erikbozic, please explain what is the error you were getting?? Give stats of your query and records. – Ankur Bhutani Jan 29 '20 at 21:58
  • @AnkurBhutani i'm afraid I can't tell you that. Changed jobs since then. But it was repeatable - did it 3 times as I recall. And there was no error. The process just died. No logs. Nothing. – erikbozic Jan 29 '20 at 22:25
36

Since the aggregates string can be longer than 4000 bytes, you can't use the LISTAGG function. You could potentially create a user-defined aggregate function that returns a CLOB rather than a VARCHAR2. There is an example of a user-defined aggregate that returns a CLOB in the original askTom discussion that Tim links to from that first discussion.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 7
    can you make an example? It is very difficult to understand the mess there – Toolkit Jan 19 '17 at 17:41
  • 18
    Starting with Oracle 12cR2, `listagg` supports an `on overflow` clause to truncate the result if it becomes too long: https://stackoverflow.com/a/46894364/312881 – Markus Winand Oct 23 '17 at 16:48
34

A new feature added in 12cR2 is the ON OVERFLOW clause of LISTAGG. The query including this clause would look like:

SELECT pid, LISTAGG(Desc, ' ' ON OVERFLOW TRUNCATE ) WITHIN GROUP (ORDER BY seq) AS desc
FROM B GROUP BY pid;

The above will restrict the output to 4000 characters but will not throw the ORA-01489 error.

These are some of the additional options of ON OVERFLOW clause:

  • ON OVERFLOW TRUNCATE 'Contd..' : This will display 'Contd..' at the end of string (Default is ... )
  • ON OVERFLOW TRUNCATE '' : This will display the 4000 characters without any terminating string.
  • ON OVERFLOW TRUNCATE WITH COUNT : This will display the total number of characters at the end after the terminating characters. Eg:- '...(5512)'
  • ON OVERFLOW ERROR : If you expect the LISTAGG to fail with the ORA-01489 error ( Which is default anyway ).

LISTAGG Enhancements in 12c R2

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
31

listagg got recently covered by the ISO SQL standard (SQL:2016). As part of that, it also got an on overflow clause, which is supported by Oracle 12cR2.

LISTAGG(<expression>, <separator> ON OVERFLOW …)

The on overflow clause supports a truncate option (as alternative to the default on overflow error behavior).

ON OVERFLOW TRUNCATE [<filler>] WITH[OUT] COUNT

The optional defaults to three periods (...) and will be added as last element if truncation happens.

If with count is specified and truncation happens, the number of omitted values is put in brackets and appended to the result.

More about listagg's on overflow clause: http://modern-sql.com/feature/listagg

Markus Winand
  • 8,371
  • 1
  • 35
  • 44
  • why Oracle not output listagg or concatenation result as blob by default? 4000 characters limitation will restrict many analysis queries. – Decula Jul 26 '19 at 22:12
23

You are exceeding the SQL limit of 4000 bytes which applies to LISTAGG as well.

SQL> SELECT listagg(text, ',') WITHIN GROUP (
  2  ORDER BY NULL)
  3  FROM
  4    (SELECT to_char(to_date(level,'j'), 'jsp') text FROM dual CONNECT BY LEVEL < 250
  5    )
  6  /
SELECT listagg(text, ',') WITHIN GROUP (
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long

As a workaround, you could use XMLAGG.

For example,

SQL> SET LONG 2000000
SQL> SET pagesize 50000
SQL> SELECT rtrim(xmlagg(XMLELEMENT(e,text,',').EXTRACT('//text()')
  2                     ).GetClobVal(),',') very_long_text
  3  FROM
  4    (SELECT to_char(to_date(level,'j'), 'jsp') text FROM dual CONNECT BY LEVEL < 250
  5    )
  6  /

VERY_LONG_TEXT
--------------------------------------------------------------------------------
one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen
,fifteen,sixteen,seventeen,eighteen,nineteen,twenty,twenty-one,twenty-two,twenty
-three,twenty-four,twenty-five,twenty-six,twenty-seven,twenty-eight,twenty-nine,
thirty,thirty-one,thirty-two,thirty-three,thirty-four,thirty-five,thirty-six,thi
rty-seven,thirty-eight,thirty-nine,forty,forty-one,forty-two,forty-three,forty-f
our,forty-five,forty-six,forty-seven,forty-eight,forty-nine,fifty,fifty-one,fift
y-two,fifty-three,fifty-four,fifty-five,fifty-six,fifty-seven,fifty-eight,fifty-
nine,sixty,sixty-one,sixty-two,sixty-three,sixty-four,sixty-five,sixty-six,sixty
-seven,sixty-eight,sixty-nine,seventy,seventy-one,seventy-two,seventy-three,seve
nty-four,seventy-five,seventy-six,seventy-seven,seventy-eight,seventy-nine,eight
y,eighty-one,eighty-two,eighty-three,eighty-four,eighty-five,eighty-six,eighty-s
even,eighty-eight,eighty-nine,ninety,ninety-one,ninety-two,ninety-three,ninety-f
our,ninety-five,ninety-six,ninety-seven,ninety-eight,ninety-nine,one hundred,one
 hundred one,one hundred two,one hundred three,one hundred four,one hundred five
,one hundred six,one hundred seven,one hundred eight,one hundred nine,one hundre
d ten,one hundred eleven,one hundred twelve,one hundred thirteen,one hundred fou
rteen,one hundred fifteen,one hundred sixteen,one hundred seventeen,one hundred
eighteen,one hundred nineteen,one hundred twenty,one hundred twenty-one,one hund
red twenty-two,one hundred twenty-three,one hundred twenty-four,one hundred twen
ty-five,one hundred twenty-six,one hundred twenty-seven,one hundred twenty-eight
,one hundred twenty-nine,one hundred thirty,one hundred thirty-one,one hundred t
hirty-two,one hundred thirty-three,one hundred thirty-four,one hundred thirty-fi
ve,one hundred thirty-six,one hundred thirty-seven,one hundred thirty-eight,one
hundred thirty-nine,one hundred forty,one hundred forty-one,one hundred forty-tw
o,one hundred forty-three,one hundred forty-four,one hundred forty-five,one hund
red forty-six,one hundred forty-seven,one hundred forty-eight,one hundred forty-
nine,one hundred fifty,one hundred fifty-one,one hundred fifty-two,one hundred f
ifty-three,one hundred fifty-four,one hundred fifty-five,one hundred fifty-six,o
ne hundred fifty-seven,one hundred fifty-eight,one hundred fifty-nine,one hundre
d sixty,one hundred sixty-one,one hundred sixty-two,one hundred sixty-three,one
hundred sixty-four,one hundred sixty-five,one hundred sixty-six,one hundred sixt
y-seven,one hundred sixty-eight,one hundred sixty-nine,one hundred seventy,one h
undred seventy-one,one hundred seventy-two,one hundred seventy-three,one hundred
 seventy-four,one hundred seventy-five,one hundred seventy-six,one hundred seven
ty-seven,one hundred seventy-eight,one hundred seventy-nine,one hundred eighty,o
ne hundred eighty-one,one hundred eighty-two,one hundred eighty-three,one hundre
d eighty-four,one hundred eighty-five,one hundred eighty-six,one hundred eighty-
seven,one hundred eighty-eight,one hundred eighty-nine,one hundred ninety,one hu
ndred ninety-one,one hundred ninety-two,one hundred ninety-three,one hundred nin
ety-four,one hundred ninety-five,one hundred ninety-six,one hundred ninety-seven
,one hundred ninety-eight,one hundred ninety-nine,two hundred,two hundred one,tw
o hundred two,two hundred three,two hundred four,two hundred five,two hundred si
x,two hundred seven,two hundred eight,two hundred nine,two hundred ten,two hundr
ed eleven,two hundred twelve,two hundred thirteen,two hundred fourteen,two hundr
ed fifteen,two hundred sixteen,two hundred seventeen,two hundred eighteen,two hu
ndred nineteen,two hundred twenty,two hundred twenty-one,two hundred twenty-two,
two hundred twenty-three,two hundred twenty-four,two hundred twenty-five,two hun
dred twenty-six,two hundred twenty-seven,two hundred twenty-eight,two hundred tw
enty-nine,two hundred thirty,two hundred thirty-one,two hundred thirty-two,two h
undred thirty-three,two hundred thirty-four,two hundred thirty-five,two hundred
thirty-six,two hundred thirty-seven,two hundred thirty-eight,two hundred thirty-
nine,two hundred forty,two hundred forty-one,two hundred forty-two,two hundred f
orty-three,two hundred forty-four,two hundred forty-five,two hundred forty-six,t
wo hundred forty-seven,two hundred forty-eight,two hundred forty-nine

If you want to concatenate multiple columns which itself have 4000 bytes, then you can concatenate the XMLAGG output of each column to avoid the SQL limit of 4000 bytes.

For example,

WITH DATA AS
  ( SELECT 1 id, rpad('a1',4000,'*') col1, rpad('b1',4000,'*') col2 FROM dual
  UNION
  SELECT 2 id, rpad('a2',4000,'*') col1, rpad('b2',4000,'*') col2 FROM dual
  )
SELECT ID,
       rtrim(xmlagg(XMLELEMENT(e,col1,',').EXTRACT('//text()') ).GetClobVal(), ',')
       || 
       rtrim(xmlagg(XMLELEMENT(e,col2,',').EXTRACT('//text()') ).GetClobVal(), ',') 
       AS very_long_text
FROM DATA
GROUP BY ID
ORDER BY ID;
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • 1
    does XMLAGG mess up special chars? – Toolkit Jan 19 '17 at 17:08
  • 1
    @Toolkit Yes, it does. – Lalit Kumar B Jan 20 '17 at 22:54
  • 2
    yeah i totally abandoned XMLAGG path, too many issues. I am just using a LOOP and concatenation, works perfect – Toolkit Jan 21 '17 at 12:23
  • Any pros and cons of using `cast as clob` or `to_clob()` vs. `GetClobVal()` worth knowing about ? – Amit Naidu Oct 28 '17 at 14:46
  • `GetClobVal()` is XMLType method to retrieve XML data, explicitly casting to CLOB is completely different in this context. If you are intending to use explicit casting instead of GetClobVal to retrieve XML data, then it is not recommended. – Lalit Kumar B Oct 30 '17 at 07:43
  • Using XMLAGG using the example above, the result has a trailing comma. Any ideas on how to prevent that? – tw1742 Jun 23 '18 at 20:07
  • @tw1742 You need to show me your use case. If you just want to get rid of the trailing comma, then simple use RTRIM function. – Lalit Kumar B Jun 24 '18 at 08:06
  • `rtrim` is inaccurate in general case, I prefer `substr` - see https://stackoverflow.com/questions/13795220/listagg-function-result-of-string-concatenation-is-too-long#comment117485522_56668004 – Tomáš Záluský Mar 03 '21 at 11:59
6

Adding on to the accepted answer. I ran into a similar problem and ended up using a user defined function that returned clob instead of varchar2. Here's my solution:

CREATE OR REPLACE TYPE temp_data FORCE AS OBJECT
(
    temporary_data NVARCHAR2(4000)
)
/

CREATE OR REPLACE TYPE temp_data_table FORCE AS TABLE OF temp_data;
/

CREATE OR REPLACE FUNCTION my_agg_func (p_temp_data_table IN temp_data_table, p_delimiter IN NVARCHAR2)
RETURN CLOB IS
  l_string CLOB;
BEGIN
  FOR i IN p_temp_data_table.FIRST .. p_temp_data_table.LAST LOOP
    IF i != p_temp_data_table.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_temp_data_table(i).temporary_data;
  END LOOP;
  RETURN l_string;
END my_agg_func;
/

Now, instead of doing

LISTAGG(column_to_aggregate, '#any_delimiter#') WITHIN GROUP (ORDER BY column_to_order_by)

I have to do this

my_agg_func (
    cast(
        collect(
            temp_data(column_to_aggregate)
            order by column_to_order_by
        ) as temp_data_table
    ),
    '#any_delimiter#'
)
Chiranjib
  • 1,763
  • 2
  • 17
  • 29
  • hi chiranjib, can u help me with the same issue here https://stackoverflow.com/questions/72413894/ora-01489-result-of-string-concatenation-is-too-long-in-oracle – Nad May 28 '22 at 11:54
  • what help are you expecting there? from your question, it seems like the list of image names is coming out to be very long and that's throwing the error. try implementing my solution as you have discovered the answer already. – Chiranjib May 30 '22 at 09:04
6

I could tolerate my field concatenated into multiple rows each less than the 4000 character limit - did the following:

with PRECALC as (select 
                 floor(4000/(max(length(MY_COLUMN)+LENGTH(',')))) as MAX_FIELD_LENGTH
                 from MY_TABLE)
select LISTAGG(MY_COLUMN,',') WITHIN GROUP(ORDER BY floor(rownum/MAX_FIELD_LENGTH), MY_COLUMN)
from MY_TABLE, PRECALC
group by floor(rownum/MAX_FIELD_LENGTH)
;
Ferdie
  • 91
  • 1
  • 2
  • 2
    Hey @Ferdie, this looks like a great workaround. Would you mind explaining a little bit about how it works? – Allen Qin Oct 20 '17 at 00:51
4

Managing overflows in LISTAGG

We can use the Database 12c SQL pattern matching function, MATCH_RECOGNIZE, to return a list of values that does not exceed limit.

Example code and more explanation in below link.

https://blogs.oracle.com/datawarehousing/entry/managing_overflows_in_listagg

Abhishek Maurya
  • 1,803
  • 1
  • 15
  • 12
2

In some scenarios the intention is to get all DISTINCT LISTAGG keys and the overflow is caused by the fact that LISTAGG concatenates ALL keys.

Here is a small example

create table tab as
select 
  trunc(rownum/10) x,
  'GRP'||to_char(mod(rownum,4)) y,
  mod(rownum,10) z
 from dual connect by level < 100;


select  
 x,
 LISTAGG(y, '; ') WITHIN GROUP (ORDER BY y) y_lst
from tab
group by x;


        X Y_LST                                                            
---------- ------------------------------------------------------------------
         0 GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3               
         1 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3         
         2 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3         
         3 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3         
         4 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3         
         5 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3         
         6 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3         
         7 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3         
         8 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3         
         9 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3         

If the groups are large, the repeated keys reach quickly the allowed maximal length and you get the ORA-01489: result of string concatenation is too long.

Unfortunately there is no support for LISTAGG( DISTINCT y, '; ') but as a workaround the fact can be used that LISTAGG ignores NULLs. Using the ROW_NUMBER we will consider only the first key.

with rn as (
select x,y,z,
row_number() over (partition by x,y order by y) rn
from tab
)
select  
 x,
 LISTAGG( case when rn = 1 then y end, '; ') WITHIN GROUP (ORDER BY y) y_lst,
 sum(z) z 
from rn
group by x
order by x;

         X Y_LST                                       Z
---------- ---------------------------------- ----------
         0 GRP0; GRP1; GRP2; GRP3             45 
         1 GRP0; GRP1; GRP2; GRP3             45 
         2 GRP0; GRP1; GRP2; GRP3             45 
         3 GRP0; GRP1; GRP2; GRP3             45 
         4 GRP0; GRP1; GRP2; GRP3             45 
         5 GRP0; GRP1; GRP2; GRP3             45 
         6 GRP0; GRP1; GRP2; GRP3             45 
         7 GRP0; GRP1; GRP2; GRP3             45 
         8 GRP0; GRP1; GRP2; GRP3             45 
         9 GRP0; GRP1; GRP2; GRP3             45

Of course the same result may be reached using GROUP BY x,y in the subquery. The advantage of ROW_NUMBER is that all other aggregate functions may be used as illustrated with SUM(z).

Community
  • 1
  • 1
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
1

Thank you for advices. I had the same problem when concatenate several fields, but even xmlagg not helped me - I still got the ORA-01489. After several attempts I found the cause and solution:

  1. Cause: one of fields in my xmlagg stores large text;
  2. Solution: apply to_clob() function.

Example:

rtrim(xmlagg(xmlelement(t, t.field1 ||'|'|| 
                           t.field2 ||'|'|| 
                           t.field3 ||'|'|| 
                           to_clob(t.field4),'; ').extract('//text()')).GetClobVal(),',')

Hope this help anybody.

Volkov Maxim
  • 191
  • 2
  • 7
  • Putting aside you are terminating particular strings with `; ` but trimming `,` on final string (which I believe is typo or leakage of obsolete revision), I would like to kindly notify all `rtrim`-based solutions are unprecise. `rtrim` trims _all_ characters from the end, which harms field content if it itself ends with `,`. Using `substr` rather than `rtrim` removes this drawback. (To make code more readable, avoid repeating long expression in `substr(longexpression,1,length(longexpression)-1)`, it is better to _prepend_ terminator _before_ each element and use `substr(longexpression,2)`.) – Tomáš Záluský Mar 03 '21 at 11:56
0

We were able to solve a similar issue here using Oracle LISTAGG. There was a point where what we were grouping on exceeded the 4K limit but this was easily solved by having the first dataset take the first 15 items to aggregate, each of which have a 256K limit.

More info: We have projects, which have change orders, which in turn have explanations. Why the database is set up to take change text in chunks of 256K limits is not known but its one of the design constraints. So the application that feeds change explanations into the table stops at 254K and inserts, then gets the next set of text and if > 254K generates another row, etc. So we have a project to a change order, a 1:1. Then we have these as 1:n for explanations. LISTAGG concatenates all these. We have RMRKS_SN values, 1 for each remark and/or for each 254K of characters.

The largest RMRKS_SN was found to be 31, so I did the first dataset pulling SN 0 to 15, the 2nd dataset 16 to 30 and the last dataset 31 to 45 -- hey, let's plan on someone adding a LOT of explanation to some change orders!

In the SQL report, the Tablix ties to the first dataset. To get the other data, here's the expression:

=First(Fields!NON_STD_TXT.Value, "DataSet_EXPLAN") & First(Fields!NON_STD_TXT.Value, "ds_EXPLAN_SN_16_TO_30") & First(Fields!NON_STD_TXT.Value, "ds_EXPLAN_SN_31_TO_45")

For us, we have to have DB Group create functions, etc. because of security constraints. So with a bit of creativity, we didn't have to do a User Aggregate or a UDF.

If your application has some sort of SN to aggregate by, this method should work. I don't know what the equivalent TSQL is -- we're fortunate to be dealing with Oracle for this report, for which LISTAGG is a Godsend.

The code is:

SELECT
LT.C_O_NBR AS LT_CO_NUM,
RT.C_O_NBR AS RT_CO_NUM,
LT.STD_LN_ITM_NBR, 
RT.NON_STD_LN_ITM_NBR,
RT.NON_STD_PRJ_NBR, 
LT.STD_PRJ_NBR, 
NVL(LT.PRPSL_LN_NBR, RT.PRPSL_LN_NBR) AS PRPSL_LN_NBR,
LT.STD_CO_EXPL_TXT AS STD_TXT,
LT.STD_CO_EXPLN_T, 
LT.STD_CO_EXPL_SN, 
RT.NON_STD_CO_EXPLN_T,
LISTAGG(RT.RMRKS_TXT_FLD, '') 
    WITHIN GROUP(ORDER BY RT.RMRKS_SN) AS NON_STD_TXT

FROM ...

    WHERE RT.RMRKS_SN BETWEEN 0 AND 15

GROUP BY 
    LT.C_O_NBR,
    RT.C_O_NBR,
    ...

And in the other 2 datasets just select the LISTAGG only for the subqueries in the FROM:

SELECT
LISTAGG(RT.RMRKS_TXT_FLD, '') 
    WITHIN GROUP(ORDER BY RT.RMRKS_SN) AS NON_STD_TXT

FROM ...

WHERE RT.RMRKS_SN BETWEEN 31 AND 45

...

... and so on.

saikumarm
  • 1,565
  • 1
  • 15
  • 30
  • 2
    People really should give reasons for downvoting. – David Brower Apr 22 '16 at 11:36
  • @DavidBrower - I totally agree - it almost seems some people just do it as they're angry at the world or something. IMHO, SO should flag people that down vote for moderator review! – JGlass Nov 08 '18 at 15:32
0

Short of using 12c overflow using the CLOB and substr will also work

rtrim(dbms_lob.substr(XMLAGG(XMLELEMENT(E,column_name,',').EXTRACT('//text()') ORDER BY column_name).GetClobVal(),1000,1),',')

  • 1
    Welcome to Stackoverflow. It would be better if you checkout [How to Answer](https://stackoverflow.com/help/how-to-answer) page for future endeavor at Stack overflow. -Thank you – Momin Apr 05 '19 at 15:13
0

To accomplish aggregation AND handle XML / HTML content AND Unicode characters, use the following:

SELECT uuid, XMLCAST(XMLAGG(XMLELEMENT(E, TO_NCLOB(text),'') ORDER BY uuid) AS NCLOB) AS text 
GROUP BY UUID
  • XMLCAST will prevent the XML / HTML from being encoded
  • TO_NCLOB will turn Unicode characters to "?" (not ideal, but better than the query breaking!)

If you only need to handle XML / HTML and don't need to worry about Unicode characters, you can use

SELECT uuid, XMLCAST(XMLAGG(XMLELEMENT(E, TO_CLOB(text),'') ORDER BY uuid) AS CLOB) AS text 
GROUP BY UUID

The only drawback with this approach is that you will only be able to select the grouped columns and the column you're aggregating. This will need to be placed in a Common Table Expression (CTE) and join it to the same table with the Group By column if you want to include the results with other columns.

Jim Bethancourt
  • 1,061
  • 11
  • 16
0

I am using custom function named as clob_agg using simply like:

select clob_agg(*detail_column*) 
from *table* 
group by *group_column*

Real example:

select length(clob_agg(x||'')) fullList
from (select level as x
      from dual
      connect by level < 40000)

The output is: 228887 ==> length of output :)

For define function (Use this in sqlDeveloper, dataGrip or some another editors can't execute sometimes):

CREATE OR REPLACE TYPE t_clob_agg AS OBJECT
(
    g_string clob,

    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_clob_agg)
        RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_clob_agg,
                                         value IN clob)
        RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateTerminate(self IN t_clob_agg,
                                           returnValue OUT clob,
                                           flags IN NUMBER)
        RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_clob_agg,
                                       ctx2 IN t_clob_agg)
        RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_clob_agg IS
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_clob_agg)
        RETURN NUMBER IS
    BEGIN
        sctx := t_clob_agg(NULL);
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_clob_agg,
                                         value IN clob)
        RETURN NUMBER IS
    BEGIN
        -- Concatenate string only when not already existing in the list (=unique)
        SELF.g_string := self.g_string || ',' || value;
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateTerminate(self IN t_clob_agg,
                                           returnValue OUT clob,
                                           flags IN NUMBER)
        RETURN NUMBER IS
    BEGIN
        returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_clob_agg,
                                       ctx2 IN t_clob_agg)
        RETURN NUMBER IS
    BEGIN
        SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
        RETURN ODCIConst.Success;
    END;


END;
/
SHOW ERRORS



CREATE OR REPLACE FUNCTION clob_agg(p_input clob)
    RETURN clob
    PARALLEL_ENABLE AGGREGATE USING t_clob_agg;
/
SHOW ERRORS
utrucceh
  • 1,076
  • 6
  • 11