CODEWITHSUNDEEP
sql
navin
navin

Reputation: 41

ORA-01489: result of string concatenation is too long

ORA-01489: result of string concatenation is too long

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

Upvotes: 4

Views: 20195

Answers (4)

Sinji Yang
Sinji Yang

Reputation: 129

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.

Upvotes: 0

Luis Carlos
Luis Carlos

Reputation: 365

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.

Upvotes: 0

luis.espinal
luis.espinal

Reputation: 10529

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.

Upvotes: 3

Dave Costa
Dave Costa

Reputation: 48111

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
  )

Upvotes: 7

Related Questions