9 Replies Latest reply: Oct 10, 2012 9:44 AM by Stew Ashton RSS

    Dynamic String Aggregation exceeding 4000byte length. Oracle 11g

    metalray
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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.