ORA-01489: result of string concatenation is too long

Question

The sql query below is meant to extract data from the database as pipe delimited and spools it to a text file on unix

select a||'|'||b||'|'||c||'|'||d from table 
union
select a||'|'||b||'|'||c||'|'||d from table

It some times gives the ORA error ORA-01489: result of string concatenation is too long

This looks like occuring if the select exceeds 4000 limit

I tried using to_clob but this works only with "union all"

Is there a way i can get around this problem

Possible duplicate of [oracle - Result of string concatenation is too long](http://stackoverflow.com/questions/29776035/oracle-result-of-string-concatenation-is-too-long) — Lalit Kumar B, Dec 02 '15 at 06:45

score 7 · Answer 1 · answered Apr 26 '11 at 16:01

7

Do the union before the concatenation.

select to_clob(a) ||'|'|| to_clob(b) ||'|'|| to_clob(c) ||'|'|| to_clob(d) from
  (
   select a, b, c, d from table1
    union
   select a, b, c, d from table2
  )

answered Apr 26 '11 at 16:01

Dave Costa

47,262
8
56
72

score 3 · Answer 2 · answered Apr 26 '11 at 16:56

As you have found out, using to_clob has its limitations. And to be honest, I think you are using a fine tool (a RDBMS) as a blunt paleolithic weapon.

The easiest way to get around the problem is to do the concatenation in-situ, in your code, as opposed to doing it with SQL. There is a maximum length limit for concatenation operations in Oracle (4k length), and there is a limit on where you can use to_clob.

So if you have those two hard limits, the most sensible thing is to do what I suggested you (do the concatenation in code) instead of trying to subvert or find an almost-magical way to work around that.

select a, b, c, d from table A union select a, b, c, d from table B

Then take the resulting resultset (or whatever language-specific construct you use) and concatenate the fields in your application code.

score 0 · Answer 3 · answered May 18 '18 at 11:57

This way I use to find procedure

select name, LISTAGG(text, ' ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY name) from all_source where name in 
(select distinct name from all_source where type = 'PROCEDURE' and text like '%P_%') and text like '% OUT %' group by name;

The interesting point is that the table all_source have the information of every procedure in divided by rows, which do processing tricky. For that reason first, I concatenate the rows for every procedure, and second set the option ON OVERFLOW TRUNCATE for avoid the string overflow; of course it works for me because I only need the procedure declaration. In this case I'm looking for procedures that contain in the name "P_" and have attributes of type OUT. The same could be applied for search functions only changing type = 'PROCEDURE' by type = 'FUNCTION' Hope this help.

score 0 · Answer 4 · answered Jul 10 '23 at 21:05

When the Group field has too many rows of null value

LISTAGG( Text_Field , ',') OVER (PARTITION BY Group_Field) AS ConCate_Text

Usually we will get the "ORA-01489: result of string concatenation is too long." Therefor, to resolve this issue is try not to concatenate the rows with Group_Field = null.

ORA-01489: result of string concatenation is too long

4 Answers4

Linked