title: Oracle : String Concatenation is too long
body: <p>I have below SQL as a part of a view. In one of the schema I am getting "<strong>String Concatenation is too long</strong>" error and not able to execute the view.</p>
  
  <p>Hence I tried the <strong>TO_CLOB()</strong> and now VIEW is not throwing ERROR, but it not returning the result as well it keep on running.. </p>
  
  <h2>Please suggest....</h2>
  
  <p><strong>Sql:</strong></p>
  
  <pre><code>SELECT Iav.Item_Id Attr_Item_Id,
  LISTAGG(La.Attribute_Name
      ||'|~|'
      || Lav.Attribute_Value
      ||' '
      || Lau.Attribute_Uom, '}~}') WITHIN GROUP (
    ORDER BY ICA.DISP_SEQ,LA.ATTRIBUTE_NAME) AS ATTR
      FROM Item_Attribute_Values Iav,
        Loc_Attribute_Values Lav,
        Loc_Attribute_Uoms Lau,
        Loc_Attributes La,
        (SELECT *
        FROM Item_Classification Ic,
          CATEGORY_ATTRIBUTES CA
        WHERE IC.DEFAULT_CATEGORY='Y'
        AND IC.TAXONOMY_TREE_ID  =CA.TAXONOMY_TREE_ID
        ) ICA
      WHERE IAV.ITEM_ID             =ICA.ITEM_ID(+)
      AND IAV.ATTRIBUTE_ID          =ICA.ATTRIBUTE_ID(+)
      AND Iav.Loc_Attribute_Id      =La.Loc_Attribute_Id
      AND La.Locale_Id              =1
      AND Iav.Loc_Attribute_Uom_Id  =Lau.Loc_Attribute_Uom_Id(+)
      AND Iav.Loc_Attribute_Value_Id=Lav.Loc_Attribute_Value_Id
      GROUP BY Iav.Item_Id;
  </code></pre>
  
  <hr>
  
  <p><strong>Error:</strong></p>
  
  <pre><code>ORA-01489: result of string concatenation is too long
  01489. 00000 -  "result of string concatenation is too long"
  *Cause:    String concatenation result is more than the maximum size.
  *Action:   Make sure that the result is less than the maximum size.
  </code></pre>