Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    1

    Unanswered: Issue with Listagg function

    Hi All,

    I'm trying to concatenate the values in a column using Listagg function. I get an error when the result crosses the lenght of Varchar2. Below is the error.
    "ORA-01489: result of string concatenation is too long".

    Is there a way to retrieve only the first 4000 characters in the result. Like do a substring and restrict the length of the result to 4000 characters so that I get rid of this error.

    Thanks in advance

    Kind Regards,
    Selvam S

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by selvam85 View Post
    Hi All,
    Is there a way to retrieve only the first 4000 characters in the result. Like do a substring and restrict the length of the result to 4000 characters so that I get rid of this error.
    You can calculate a cumulative sum of length, and select only these records
    where length<=4000, here isan example:

    Code:
    select listagg( lev, ',' ) within group (order by lev)
    from
    ( select level lev
      from dual
      connect by level <= 5000
    );
    
    Error report:
    SQL Error: ORA-01489: result of string concatenation is too long
    
    
    
    select listagg( lev, ',' ) within group (order by lev)
    from
    (
      select lev,
             sum( length( lev ) + 1) 
                over ( order by lev rows between unbounded preceding and current row) lngth
      from
      ( select level lev
        from dual
        connect by level <= 5000
      )
    )
    where lngth <= 4000
    ;
    
    
    LISTAGG(LEV,',')WITHINGROUP(ORDERBYLEV)
    -----------------------------------------------------------------------------
    1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,......

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •