HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
ORA-01489: result of string concatenation is too long
ORACLE STUFFS
26-Feb-2018
That happens when we want to aggregate/concatenate text fields and the summary length exceeds the 4000 limit.
There are various scenarios what do you want to achieve:
  • cut the result to 4000 characters
  • return as a CLOB
  • more complex tasks such as cut particular long parts of the text.

  • Following solution solves the first case, i.e. return first 4000 characters in the case of exceeding.

    Original statement:

    Select 
      L1.GroupID
     ,listagg(L1.PartOfTheText,',')within group(order by L1.TextOrderNR)  
    from SourceTable L1 
    group by L1.GroupID;


    Solution:

    With L1 as(
    SELECT 
      SRC.GroupID 
     ,SRC.NoteTypeID
     ,SRC.TextOrderNR
     ,sum(length(SRC.PartOfTheText)+1)over(partition by SRC.GroupID order by SRC.TextOrderNR,SRC.rowid) as LENNY
    FROM SourceTable SRC
    )
    Select 
      L1.GroupID
     ,listagg(L1.PartOfTheText,',')within group(order by L1.TextOrderNR)  
    from L1 
    where LENNY<=4001
    group by L1.GroupID
    ;



    Explanation:
    We eliminate parts of text exceeding 4000 characters totally using analytical function of length.
    We add rowid into the order by clause to assure cummulativeness in the case of TextOrderNR equality.
    We add 1 into each piece length to conseder delimiters length.
    We use 4001 as a limit because last delimiter won't appear.


    Solution to use substring of exceeding part too:

    With L1 as(
    SELECT 
      SRC.GroupID 
     ,SRC.NoteTypeID
     ,SRC.TextOrderNR
     ,sum(length(SRC.PartOfTheText)+1)over(partition by SRC.GroupID order by SRC.TextOrderNR,SRC.rowid)-(length(SRC.PartOfTheText)+1) as LENNY
    FROM SourceTable SRC
    )
    Select 
      L1.GroupID
     ,listagg(substr(L1.PartOfTheText,1,4000-LENNY),',')within group(order by L1.TextOrderNR)  
    from L1 
    where LENNY<4000
    group by L1.GroupID
    ;



    Explanation:
    We trim the part of text exceeding 4000 characters using analytical function of length but we subtract length of the current piece.
    We use less than 4000 as a limit to assure there would be at least one character from the last trimmed piece.

    Ludek Bob Jankovsky
    All Right Reserved © 2007, Designed by Bob Jankovsky