CODEWITHSUNDEEP
sqlstringoracle-databaseconcatenation
Warrior
Warrior

Reputation: 3304

Oracle : String Concatenation is too long

I have below SQL as a part of a view. In one of the schema I am getting "String Concatenation is too long" error and not able to execute the view.

Hence I tried the TO_CLOB() and now VIEW is not throwing ERROR, but it not returning the result as well it keep on running..

Please suggest....

Sql:

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;

Error:

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.

Upvotes: 2

Views: 7659

Answers (4)

oratom
oratom

Reputation: 291

I guess you need to write a small function to concatenate the strings into a CLOB, because even when you cast TO_CLOB() the LISTAGG at the end, this might not work.

Here´s a sample-function that takes a SELECT-Statement (which MUST return only one string-column!) and a separator and returns the collected values as a CLOB:

 CREATE OR REPLACE FUNCTION listAggCLob(p_stringSelect VARCHAR2
                                      , p_separator VARCHAR2)
   RETURN CLOB
AS
   cur SYS_REFCURSOR;
   s VARCHAR2(4000);
   c CLOB;   
   i INTEGER; 
BEGIN
   dbms_lob.createtemporary(c, FALSE);
   IF (p_stringSelect IS NOT NULL) THEN
      OPEN cur FOR p_stringSelect;
      LOOP
         FETCH cur INTO s;
         EXIT WHEN cur%NOTFOUND;
         dbms_lob.append(c, s || p_separator);
      END LOOP;
   END IF;
   i := length(c);
   IF (i > 0) THEN
      RETURN dbms_lob.substr(c,i-length(p_separator));
   ELSE
      RETURN NULL;
   END IF;
END;

This function can be used f.e. like this:

WITH cat AS (
   SELECT DISTINCT t1.category
     FROM lookup t1
   )
SELECT cat.category
     , listAggCLob('select t2.name from lookup t2 where t2.category = ''' || cat.category || '''', '|') allcategorynames  
  FROM cat;

Upvotes: 0

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

Xquery approach. Creating extra types or function isn't necessary.

with test_tab
     as (select object_name
           from all_objects
          where rownum < 1000)
   , aggregate_to_xml as (select xmlagg(xmlelement(val, object_name)) xmls from test_tab)
select xmlcast(xmlquery('for $row at $idx in ./*/text() return  if($idx=1) then $row else concat(",",$row)'
                passing aggregate_to_xml.xmls returning content) as Clob) as list_in_lob
  from aggregate_to_xml;

Upvotes: 0

MT0
MT0

Reputation: 167832

You can use the COLLECT() function to aggregate the strings into a collection and then use a User-Defined function to concatenate the strings:

Oracle Setup:

CREATE TYPE stringlist IS TABLE OF VARCHAR2(4000);
/

CREATE FUNCTION concat_List(
  strings IN stringlist,
  delim   IN VARCHAR2 DEFAULT ','
) RETURN CLOB DETERMINISTIC
IS
  value CLOB;
  i     PLS_INTEGER;
BEGIN
  IF strings IS NULL THEN
    RETURN NULL;
  END IF;
  value := EMPTY_CLOB();
  IF strings IS NOT EMPTY THEN
    i := strings.FIRST;
    LOOP
      IF i > strings.FIRST AND delim IS NOT NULL THEN
        value := value || delim;
      END IF;
      value := value || strings(i);
      EXIT WHEN i = strings.LAST;
      i := strings.NEXT(i);
    END LOOP;
  END IF;
  RETURN value;
END;
/

Query:

SELECT Iav.Item_Id AS Attr_Item_Id,
       CONCAT_LIST(
         CAST(
           COLLECT(
             La.Attribute_Name || '|~|' || Lav.Attribute_Value ||' '|| Lau.Attribute_Uom
             ORDER BY ICA.DISP_SEQ,LA.ATTRIBUTE_NAME
           )
           AS stringlist
         ),
         '}~}'
       ) AS ATTR
FROM   your_table
GROUP BY iav.item_id;

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

LISTAGG is limited to 4000 characters unfortunately. So you may want to use another approach to concatenate the values.

Anyway ...

It is strange to see LISTAGG which is a rather new feature combined with error-prone SQL1992 joins. I'd suggest you re-write this. Are the tables even properly joined? It looks strange that there seems to be no relation between Loc_Attributes and, say, Loc_Attribute_Values. Doesn't have Loc_Attribute_Values a Loc_Attribute_Id so an attribute value relates to an attribute? It would be hard to believe that there is no such relation.

Moreover: Is it guaranteed that your classification subquery doesn't return more than one record per attribute?

Here is your query re-written:

select 
  iav.item_id as 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
join loc_attribute_values lav 
       on lav.loc_attribute_value_id = iav.loc_attribute_value_id
       and lav.loc_attribute_id = iav.loc_attribute_id -- <== maybe?
join loc_attributes la 
       on la.loc_attribute_id = lav.loc_attribute_id 
       and la.loc_attribute_id = lav.loc_attribute_id -- <== maybe?
       and la.locale_id = 1
left join loc_attribute_uoms lau 
       on lau.loc_attribute_uom_id = iav.loc_attribute_uom_id
       and lau.loc_attribute_id = iav.loc_attribute_id -- <== maybe?
left join
(
  --  aggregation needed to get no more than one sortkey per item attribute?
  select ic.item_id, ca.attribute_id, min (ca.disp_seq) as disp_seq
  from item_classification ic
  join category_attributes ca on ca.taxonomy_tree_id = ic.taxonomy_tree_id
  where ic.default_category = 'y'
  group by ic.item_id, ca.attribute_id
) ica on ica.item_id = iav.item_id and ica.attribute_id = iav.attribute_id
group by iav.item_id;

Well, you get the idea; check your keys and alter your join criteria where necessary. Maybe this gets rid of duplicates, so LISTAGG has to concatenate less attributes, and maybe the result even stays within 4000 characters.

Upvotes: 1

Related Questions