5 Replies Latest reply: Jan 6, 2013 2:01 PM by 975959 RSS

    String Aggregation with CLOB type

      I should compare to query result from two different DB version: Oracle 11.1 and 11.2.
      What I'd like to perform is a string aggregation. I read for 11.2 there is LISTAGG function, while for 11.1 I could use WMSYS.WMCONCAT.

      A doubt is that I should aggregate CLOB type. Is it possible, or are there any limitations for the above functions?

      Many thanks,
        • 1. Re: String Aggregation with CLOB type
          ranit B
          Stefanik74 wrote:
          What I'd like to perform is a string aggregation. I read for 11.2 there is LISTAGG function, while for 11.1 I could use WMSYS.WMCONCAT.
          Who said you can use WMSYS.WMCONCAT ? It is never advisable coz. that is not a documented function.

          Try this...
          -- "added recently"
          Ranit>> desc tb_x;
           Name                                      Null?    Type
           ----------------------------------------- -------- ----------------------------
           TXT                                                CLOB
           ID                                                 NUMBER
          Ranit>> select * from tb_x;
          TXT                                                                                      ID                                                                                                                                 
          -------------------------------------------------------------------------------- ----------                                                                                                                                 
          aaaaaaaaaaaaaaaa_1                                                                        1                                                                                                                                 
          aaaaaaaaaaaaaaaa_2                                                                        1                                                                                                                                 
          aaaaaaaaaaaaaaaa_3                                                                        1                                                                                                                                 
          bbb_1                                                                                     2                                                                                                                                 
          bbb_2                                                                                     2                                                                                                                                 
          bbb_3                                                                                     2                                                                                                                                 
          c_1                                                                                       3                                                                                                                                 
          c_2                                                                                       3                                                                                                                                 
          8 rows selected.
          Ranit>> select
            2  id,
            3  RTRIM(
            4  XMLAgg(XMLElement(e,txt||';')).extract('//text()')
            5  ,
            6  ';') as "str_agg"
            7  from
            8  tb_x
            9  GROUP By id;
          Edited by: ranit B on Jan 6, 2013 9:53 AM
          -- added the table 'tb_x' structure - a NUMBER and a CLOB column
          • 2. Re: String Aggregation with CLOB type
            A doubt is that I should aggregate CLOB type
            [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions089.htm#SQLRF30030]Listagg returns varchar2 or raw.
            Maybe using [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF30048]recursive with could produce some useful results.
            both not directly available on 11.1.
            It's not clear what your exact intentions are or how query result comparison relates to clob aggregation:
            Query results from 11.2 are inserted into some table in 11.1 to be compared with query results from 11.1 or vice versa or ...


            • 3. Re: String Aggregation with CLOB type
              Frank Kulash

              LISTAGG returns a VARCHAR2, which can't be longer than 4000 bytes.
              For a user-defined function that returns a CLOB, see {message:id=4000754}
              This seems to behave like WM_CONCAT. (Since WM_CONCAT is undocumented, we don't really know what it does.)
              • 4. Re: String Aggregation with CLOB type
                Stew Ashton
                ranit B wrote:
                Who said you can use WMSYS.WMCONCAT ? It is never advisable coz. that is not a documented function.
                You are right, we should avoid undocumented functions. Don't you think we should also avoid deprecated functions?

                The EXTRACT (XML) function is deprecated.

                To the original poster: I suggest this alternative that does CLOBs.
                WITH DATA AS (
                  SELECT RPAD(TO_CHAR(LEVEL,'fm00'),20,TO_CHAR(LEVEL,'fm00')) TXT
                  FROM DUAL
                  CONNECT BY LEVEL <= 9
                SELECT SUBSTR(
                      xmlelement(E, ',' || txt)
                      ORDER BY TXT
                    ) AS CLOB -- or AS VARCHAR2(4000)
                  ), 2
                ) txt_agg
                from data;
                Note the ORDER BY: it's optional, but almost always useful.

                Edited by: Stew Ashton on Jan 6, 2013 8:34 AM
                • 5. Re: String Aggregation with CLOB type
                  Thanks for your informations.