This discussion is archived
9 Replies Latest reply: Oct 10, 2012 7:44 AM by Stew Ashton RSS

Dynamic String Aggregation exceeding 4000byte length. Oracle 11g

metalray Newbie
Currently Being Moderated
Hello Guys,

We are using Oracle 11g

I have a problem with the string concatenation being too large. I looked around for a while
and people suggested custom functions (which is difficult due to restrictions in our db environemnt)
or using a clob datatype. Now I have not found a way how to use a clob with the below method.
The number of rows I have to merge varies with each IDNum group so it has to be dynamic

--ORA-01489: result of string concatenation is too long

SELECT LISTAGG(MYBIGTEXT,',') WITHIN GROUP( ORDER BY IDNum) A FROM ourtablewithbigtext ;

Thanks for any hints.
  • 1. Re: Dynamic String Aggregation exceeding 4000byte length. Oracle 11g
    BluShadow Guru Moderator
    Currently Being Moderated
    If you want more than 4000 characters then you have no choice but to create a function to aggregate the data to a clob result...
    create or replace type clobagg_type as object
      (
      text clob,
      static function ODCIAggregateInitialize(sctx in out clobagg_type) return number,
      member function ODCIAggregateIterate(self in out clobagg_type, value in clob) return number,
      member function ODCIAggregateTerminate(self in clobagg_type, returnvalue out clob, flags in number) return number,
      member function ODCIAggregateMerge(self in out clobagg_type, ctx2 in clobagg_type) return number
      );
    / 
    create or replace type body clobagg_type is
      static function ODCIAggregateInitialize(sctx in out clobagg_type) return number is
      begin
        sctx := clobagg_type(null) ;
        return ODCIConst.Success ;
      end;
      member function ODCIAggregateIterate(self in out clobagg_type, value in clob) return number is
      begin
        self.text := self.text || value ;
        return ODCIConst.Success;
      end;
      member function ODCIAggregateTerminate(self in clobagg_type, returnvalue out clob, flags in number) return number is
      begin
        returnValue := self.text;
        return ODCIConst.Success;
      end;
      member function ODCIAggregateMerge(self in out clobagg_type, ctx2 in clobagg_type) return number is
      begin
        self.text := self.text || ctx2.text;
        return ODCIConst.Success;
      end;
    end;
    / 
    create or replace function clobagg(input clob) return clob
      deterministic
      parallel_enable
      aggregate using clobagg_type;
    / 
    
    SQL> select trim(',' from clobagg(ename||',')) as enames from emp;
    
    ENAMES
    -----------------------------------------------------------------------------------
    SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as
      2    (select 'PFL' c1, 0 c2,110 c3 from dual union all
      3     select 'LHL', 0 ,111 from dual union all
      4     select 'PHL', 1, 111 from dual union all
      5     select 'CHL', 2, 111 from dual union all
      6     select 'DHL', 0, 112 from dual union all
      7     select 'VHL', 1, 112 from dual union all
      8     select 'CPHL', 0, 114 from dual union all
      9     select 'WDCL', 1, 114 from dual union all
     10     select 'AHL' ,2 ,114 from dual union all
     11     select 'NFDL', 3, 114 from dual)
     12  --
     13  -- end of test data
     14  --
     15  select trim(clobagg(c1||' ')) as c1, c3
     16  from (select * from t order by c3, c2)
     17  group by c3
     18* order by c3
    SQL> /
    
    C1                                     C3
    ------------------------------ ----------
    PFL                                   110
    LHL CHL PHL                           111
    DHL VHL                               112
    CPHL AHL NFDL WDCL                    114
    There is no doubt a method using XML functionality too, that someone will post or you may find if you google for it.
  • 2. Re: Dynamic String Aggregation exceeding 4000byte length. Oracle 11g
    Stew Ashton Expert
    Currently Being Moderated
    BluShadow was right, of course. Notice the ORDER BY clause in XMLAGG itself; it's the equivalent of the WITHIN GROUP part of LISTAGG.
    select deptno,
    SUBSTR(
      xmlcast(
        xmlagg(
          xmlelement(E, ',' || ename)
          order by ename
        ) AS CLOB
      ), 2
    ) enames
    from scott.emp
    group by deptno
    order by deptno;
  • 3. Re: Dynamic String Aggregation exceeding 4000byte length. Oracle 11g
    metalray Newbie
    Currently Being Moderated
    Hello BluShadow and Stew Ashton,

    Thanks for your replies. I did not know that there is no way
    around the new function and type definition. I can now sell this to the
    DBA's ;)

    Finally, I wanted to PIVOT the result with the new 11g PIVOT operator but
    came across this error:

    --ORA-56902: expect aggregate function inside pivot operation


    SELECT * FROM
    (
    select MsgType ,trim(' ' from clobagg(MYBIGTEXT||'')) as MYBIGTEXT
    from ourtablewithbigtext
    GROUP BY MsgType
    )
    PIVOT
    (
    MsgType
    for MsgType IN ('S','M')
    )

    There are basically two types of messages both occupying multiple rows.
    Since I have aggregated those rows now, I want to PIVOT them to see the
    two messages horizontally next to each other.
    Now I googled and read that I have to move the PIVOT inside the aggregation
    but I think its vital to aggregate first, otherwise I PIVOT x rows into x columns and
    not 1 rows into 1 columns like I can do after the aggregation.

    Thanks, my weekend is saved.
  • 4. Re: Dynamic String Aggregation exceeding 4000byte length. Oracle 11g
    Stew Ashton Expert
    Currently Being Moderated
    Pivoting requires aggregation, and CLOBs cannot be aggregated.

    One workaround is to just take the first 4000 bytes of the CLOB and treat it as a VARCHAR2:
    select * from (
      select DEPTNO,
      cast(
        SUBSTR(
          xmlcast(
            xmlagg(
              xmlelement(E, ',' || ename)
              order by ename
            ) as clob
          ), 2, 4000
        )
       as varchar2(4000)
      ) enames
      from SCOTT.EMP
      group by DEPTNO
    )
    PIVOT(min(ENAMES) ENAMES for DEPTNO in(10,20,30));
    
    10_ENAMES                            20_ENAMES                            30_ENAMES                          
    ------------------------------------ ------------------------------------ ------------------------------------
    CLARK,KING,MILLER                    ADAMS,FORD,JONES,SCOTT,SMITH         ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
  • 5. Re: Dynamic String Aggregation exceeding 4000byte length. Oracle 11g
    ranit B Expert
    Currently Being Moderated
    This works charm for me... please try & let me know if any issues...
    with t as
         (select 'PFL' c1, 0 c2,110 c3 from dual union all
          select 'LHL', 0 ,111 from dual union all
          select 'PHL', 1, 111 from dual union all
          select 'CHL', 2, 111 from dual union all
          select 'DHL', 0, 112 from dual union all
          select 'VHL', 1, 112 from dual union all
          select 'CPHL', 0, 114 from dual union all
          select 'WDCL', 1, 114 from dual union all
          select 'AHL' ,2 ,114 from dual union all
          select 'NFDL', 3, 114 from dual)
    select c3, rtrim(xmlagg(xmlelement(e,c1||'|')).extract('//text()'),'|') from t
    group by c3;
    gives
    110     PFL
    111     LHL|CHL|PHL
    112     DHL|VHL
    114     CPHL|NFDL|AHL|WDCL
  • 6. Re: Dynamic String Aggregation exceeding 4000byte length. Oracle 11g
    odie_63 Guru
    Currently Being Moderated
    Now I googled and read that I have to move the PIVOT inside the aggregation
    but I think its vital to aggregate first, otherwise I PIVOT x rows into x columns and
    not 1 rows into 1 columns like I can do after the aggregation.
    Actually, you can do both at the same time :
    SQL> select ltrim(
      2           xmlcast(
      3             xmlagg(case when deptno = 10 then xmlelement("e", ','||ename) end order by ename)
      4             as clob
      5           )
      6         , ','
      7         ) as dept10
      8       , ltrim(
      9           xmlcast(
     10             xmlagg(case when deptno = 20 then xmlelement("e", ','||ename) end order by ename)
     11             as clob
     12           )
     13         , ','
     14         ) as dept20
     15       , ltrim(
     16           xmlcast(
     17             xmlagg(case when deptno = 30 then xmlelement("e", ','||ename) end order by ename)
     18             as clob
     19           )
     20         , ','
     21         ) as dept30
     22  from scott.emp
     23  ;
     
    DEPT10                              DEPT20                                    DEPT30
    ----------------------------------- ----------------------------------------- --------------------------------------------
    CLARK,KING,MILLER                   ADAMS,FORD,JONES,SCOTT,SMITH              ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
     
  • 7. Re: Dynamic String Aggregation exceeding 4000byte length. Oracle 11g
    Stew Ashton Expert
    Currently Being Moderated
    Good one. I was looking at the trees and forgot the forest.

    I notice you use LTRIM(x, ',') instead of SUBSTR(x,2). Is that just a semantic choice or have you noticed a performance difference?

    I have a tiny preference for SUBSTR because you don't have to specify the delimiter again.
  • 8. Re: Dynamic String Aggregation exceeding 4000byte length. Oracle 11g
    metalray Newbie
    Currently Being Moderated
    Hello Stew Ashton, and ranit B      
    I tried both of your solution but now got that:
    --ORA-31061: XDB error: special char to escaped char conversion failed.                                                                                                                                                                                                                                                                                                                   
  • 9. Re: Dynamic String Aggregation exceeding 4000byte length. Oracle 11g
    Stew Ashton Expert
    Currently Being Moderated
    You are getting a character in the data that is not allowed in XML documents.

    See {message:id=4076401}

    Try this:
    SQL> alter session set events = '19119 trace name context off'
    /
    session SET altered.
    
    SQL> select xmlelement("test",chr(0)) from dual
    /
    Error starting at line 3 in command:
    select xmlelement("test",chr(0)) from dual
    Error report:
    SQL Error: ORA-31061: XDB error: special char to escaped char conversion failed.
    
    SQL> alter session set events = '19119 trace name context forever, level 0x100000'
    /
    session SET altered.
    
    SQL> select xmlelement("test",chr(0)) from dual
    /
    XMLELEMENT("TEST",CHR(0))                                                      
    --------------------------------------------------------------------------------
    <test>?</test>                                                                   
    
    SQL> alter session set events = '19119 trace name context forever, level 0x200000'
    /
    session SET altered.
    
    SQL> select xmlelement("test",chr(0)) from dual
    /
    XMLELEMENT("TEST",CHR(0))                                                      
    --------------------------------------------------------------------------------
    <test>& #x0000;</test>                                                            
    
    SQL> alter session set events = '19119 trace name context forever, level 0x400000'
    /
    session SET altered.
    
    SQL> select xmlelement("test",chr(0)) from dual
    /
    XMLELEMENT("TEST",CHR(0))                                                      
    --------------------------------------------------------------------------------
    <test></test>                                                                    
    
    SQL> alter session set events = '19119 trace name context off'
    /
    session SET altered.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points