3

Say I have this table:

ID|Col1|Col2|Col3

1|Text that has 4000 chars|Text2 that has 4000 chars|Text3 that has 4000 chars

2|Text4 that has 4000 chars|Text5 that has 4000 chars|Text6 that has 4000 chars

3|Text7 that has 4000 chars|Text8 that has 4000 chars|Text9 that has 4000 chars

I am using listagg like so:

SELECT id,
       listagg(col1||col2||col3, ',') within group (order by id)
FROM table;

And I am encountering the error:

ORA-01489: result of string concatenation is too long

Upon researching, I found out using xmlagg could do the trick (link), but then realized the real problem lies in the concatenation of col1, col2 and col3 as it is only limited to 4000 chars, so doing xmlagg would still return the same error.

Has anyone figured this one out yet? Or there's no workaround for this? (link)

Update:

I updated the sample values on the table just to be clear (for Mr. Kumar to understand), and my expected output should be something like:

ID | Agg
1 | Text that has 4000 charsText2 that has 4000 charsText3 that has 4000 chars
2 | Text4 that has 4000 charsText5 that has 4000 charsText6 that has 4000 chars
3 | Text7 that has 4000 charsText8 that has 4000 charsText9 that has 4000 chars

Which apparently doesn't work.

Community
  • 1
  • 1
superigno
  • 994
  • 2
  • 12
  • 24
  • "*but then realized the real problem lies in the concatenation of col1, col2 and col3 as it is only limited to 4000 chars, so doing xmlagg would still return the same error.*" No. You need to concatenate the XMLAGG output of each column. For example, `rtrim(xmlagg(xmlelement(e,col1,',').extract('//text()') order by col1).GetClobVal(),',')||rtrim(xmlagg(xmlelement(e,col2,',').extract('//text()') order by col2).GetClobVal(),',')` – Lalit Kumar B Dec 02 '15 at 06:42
  • Nope. That would return: Text on col1 row1, Text on col1 row2, Text on col1 row3 Text on col2 row1, Text on col2 row2, Text on col2 row3 Text on col3 row1, Text on col3 row2, Text on col3 row3 Instead of: Text on col1 row1, Text on col2 row1, Text on col3 row1 Text on col1 row2, Text on col2 row2, Text on col3 row2 Text on col1 row3, Text on col2 row3, Text on col3 row3 – superigno Dec 02 '15 at 08:11
  • I tried it and it returned the values I expected it to return, as mentioned on my previous comment – superigno Dec 02 '15 at 08:19
  • The returned values are one,two,three...two hundred forty-nineone,two,thre...two hundred forty-nine instead of one, one, two, two, three, three..two hundred forty-nine,two hundred forty-nine – superigno Dec 02 '15 at 08:19
  • Nope. It didn't work. – superigno Dec 02 '15 at 08:20
  • You're just repeating what I've raised above. My problem there would be if the value of col1 or col2 is greater than 4000 chars, it will still raise the exception. – superigno Dec 02 '15 at 08:26
  • I think you are not getting my point. It should work in all cases(unless you directly concatenate two strings each of 4000 bytes as you cannot it is SQL limit). So, you need to do it like `SET LONG 2000000 set pagesize 50000 WITH DATA AS ( SELECT rpad('a',4000,'*') col1, rpad('b',4000,'*') col2 FROM dual ) SELECT rtrim(xmlagg(XMLELEMENT(e,col1,',').EXTRACT('//text()')).GetClobVal(),',') || rtrim(xmlagg(XMLELEMENT(e,col2,',').EXTRACT('//text()')).GetClobVal(),',') very_long_text FROM DATA; ` col1 and col2 are each 4000 bytes, the concatenation results in 8000 bytes – Lalit Kumar B Dec 02 '15 at 08:36
  • There you go. That's the answer I was looking for "unless you directly concatenate two strings each of 4000 bytes as you cannot it is SQL limit". That would've satisfied me. You even quoted me earlier "but then realized the real problem lies in the concatenation of col1, col2 and col3 as it is only limited to 4000 chars, so doing xmlagg would still return the same error." but then you provided uneccessary answers. Thanks anyway. – superigno Dec 02 '15 at 08:42
  • So that's the reason I gave you the new query which would concatenate the XMLAGG output to more than 4000 bytes. The above example concatenates the output to 8000 bytes. And those were not unnecessary answers as they were different. I spent all the time to help you. – Lalit Kumar B Dec 02 '15 at 08:46
  • Which did not satisfy the question. – superigno Dec 02 '15 at 08:47
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/96772/discussion-between-lalit-kumar-b-and-superigno). – Lalit Kumar B Dec 02 '15 at 08:49

2 Answers2

1

You can do it much simpler, since Oracle introduced SQL Semantics and LOBs some time ago.

SELECT ID, TO_CLOB(col1) || col2 || col3 AS very_long_text
FROM TABLE;

First element of || operator has to be a CLOB, then it works.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
1

I finally got it to work. What I did was to aggregate the columns before concatenating it, GROUP it as Mr. Kumar suggested and THEN aggregate them again to fix the ordering. Here's how:

WITH agg_tbl AS
(
SELECT id,
       rtrim(xmlagg(xmlelement(e,col1,',').extract('//text()').GetClobVal(),',')||rtrim(xmlagg(xmlelement(e,col1,',').extract('//text()').GetClobVal(),',')||rtrim(xmlagg(xmlelement(e,col1,',').extract('//text()').GetClobVal(),',') long_text
FROM table
GROUP BY col1
)
SELECT rtrim(xmlagg(xmlelement(e,long_text,chr(13)).extract('//text()').GetClobVal(),',') agg_value
FROM agg_tbl;

So instead of this:

agg_value
Text that has 4000 charsText4 that has 4000 charsText7 that has 4000 chars
Text2 that has 4000 charsText5 that has 4000 charsText8 that has 4000 chars
Text3 that has 4000 charsText6 that has 4000 charsText9 that has 4000 chars

I am now getting my desired result:

agg_value
Text that has 4000 charsText2 that has 4000 charsText3 that has 4000 chars
Text4 that has 4000 charsText5 that has 4000 charsText6 that has 4000 chars
Text7 that has 4000 charsText8 that has 4000 charsText9 that has 4000 chars
superigno
  • 994
  • 2
  • 12
  • 24