ORA-01489: result of string concatenation is too long 01489. 00000 - "result of string concatenation is too long"

Question

I have this query but I got an ORA-01489 Error : ORA-01489: result of string concatenation is too long 01489. 00000 - "result of string concatenation is too long", Is it posible resolve this problem maybe setting a system variable ?

SELECT "USER_PRIMARY_sector","LOGIN","FIRST_NAME","LAST_NAME","sector_ROLE"
FROM (
SELECT user_primary_sector,login, first_name,  last_name,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(rights,' / '))
       KEEP (DENSE_RANK LAST ORDER BY curr),' / ') AS sector_ROLE
      FROM  
        (SELECT  login,
              first_name,  
              last_name,
              user_primary_sector,
              rights,
              ROW_NUMBER() OVER (PARTITION BY login ORDER BY rights) AS curr,
              ROW_NUMBER() OVER (PARTITION BY login ORDER BY rights) -1 AS prev
        FROM  (select   member0_.login,  member0_.first_name first_name, sector2.sector_name user_primary_sector,  member0_.last_name last_name,
                        CONCAT(CONCAT(sector.sector_name, ' - '), role3_.role_name) rights 
 from
  TLC_DEVICES.t_member member0_
 inner join  TLC_DEVICES.t_user member0_1_    on member0_.member_id=member0_1_.user_id
 inner join  TLC_DEVICES.t_playable_role playedrole1_    on member0_.member_id=playedrole1_.user_id
 inner join  TLC_DEVICES.t_sector_role sectorrole2_    on playedrole1_.sector_role_id=sectorrole2_.sector_role_id
 inner join  TLC_DEVICES.t_role role3_    on sectorrole2_.role_id=role3_.role_id
 inner join  TLC_DEVICES.t_sector sector    on sectorrole2_.sector_id=sector.sector_id
 inner join  TLC_DEVICES.t_sector sector2    on sector2.sector_id=member0_1_.primary_sector_id
 where    current_date between playedrole1_.start_date and playedrole1_.end_date
 order by sector.sector_name
  ))
GROUP BY login, first_name,  last_name, user_primary_sector
CONNECT BY prev = PRIOR curr AND login = PRIOR login
START WITH curr = 1
)
ORDER BY user_PRIMARY_sector, FIRST_NAME, LAST_NAME;

It will help us to help you if you remove bits of your complex query until it still gives the error at it's simplest form. — Chris Pickford, Feb 17 '16 at 09:27

score 1 · Accepted Answer · answered Feb 17 '16 at 09:22

One option would be to set the MAX_STRING_SIZE system variable to EXTENDED. From the Oracle documentation:

Variable-length character string having maximum length size bytes or characters. You must specify size for VARCHAR2. Minimum size is 1 byte or 1 character. Maximum size is:

32767 bytes or characters if MAX_STRING_SIZE = EXTENDED
4000 bytes or characters if MAX_STRING_SIZE = STANDARD

So if your MAX_STRING_SIZE is currently set to STANDARD, then you will get the ORA-01489 error if your concatenated string exceeds 4000 bytes or characters. Here is how you can make the change:

ALTER SYSTEM SET MAX_STRING_SIZE = EXTENDED;

If your MAX_STRING_SIZE be already set to EXTENDED, then you will have to find another way to cope with such long strings.

That is only possible in `12c` while OP is on `11g`. for any application, it is not a good design if the string aggregation is exceeding that limit at database level. Application use **CLOB** data type for such huge requirements of storage. — Lalit Kumar B, Feb 17 '16 at 09:25
@LalitKumarB He marked this correct which either means he is using `11g` and didn't test it, or is actually using `12c`. I will delete if he comes back. — Tim Biegeleisen, Feb 17 '16 at 09:29

ORA-01489: result of string concatenation is too long 01489. 00000 - "result of string concatenation is too long"

1 Answers1