12 Replies Latest reply on Jun 8, 2016 4:28 PM by Andreas Weiden

    How to use the LISTAGG in Oracle 10g?

    Xuhaiduo

      Hi All,

           The LISTAGG analytic function was introduced in Oracle database 11g Release 2,but I want to use it in Oracle 10g.How to solve it ?

           Thanks very much!

        • 1. Re: How to use the LISTAGG in Oracle 10g?
          BluShadow

          Well as you say, it was introduced in 11g, so how do you expect to be able to use it in 10g?

           

          In 10g you would use a connect by query, with a sys_connect_by_path call to aggregate the data in a similar way... e.g.

           

          SQL> ed
          Wrote file afiedt.buf

            1  select deptno, ltrim(sys_connect_by_path(empno,','),',') as empnos
            2  from   (select deptno
            3                ,empno
            4                ,row_number() over (partition by deptno order by empno) as rn
            5          from   emp
            6         )
            7  where connect_by_isleaf = 1
            8  connect by deptno = prior deptno
            9         and rn = prior rn+1
          10* start with rn = 1
          SQL> /

              DEPTNO EMPNOS
          ---------- --------------------------------------------------
                  10 7782,7839,7934
                  20 7369,7566,7788,7876,7902
                  30 7499,7521,7654,7698,7844,7900

          • 2. Re: How to use the LISTAGG in Oracle 10g?
            Kalpataru

            Is your issue is related to Oracle Forms 10g or Database 10g ?

            Where you want to use this in forms or database ?

            As it is introduced in Oracle Database 11g Release 2 then how can you use it oracle 10g ?

            BluShadow already given you the alternative from 10g.

            You can also check this may help you String Concatenation

            • 3. Re: How to use the LISTAGG in Oracle 10g?
              Manu.

              Dear,

               

              You cannot use LISTAGG function in ig database. Instead try using the WM_CONCAT or use the SYS_CONNECT_BY_PATH analytical function.

               

               

              SELECT <field_1>, SUBSTR(SYS_CONNECT_BY_PATH(<group_field>, ','), 2) SO_NO
              FROM(SELECT <field_1>, <group_field>, COUNT(*) OVER (PARTITION BY <field_1>) CNT, ROW_NUMBER() OVER (PARTITION BY <field_1> ORDER BY <group_field>) SEQ
                   FROM DELIVERY_NOTE_DETAIL
                   WHERE <group_field> IS NOT NULL
                   GROUP BY <field_1>, <group_field>)
              WHERE SEQ = CNT START WITH SEQ = 1 CONNECT BY PRIOR SEQ + 1 = SEQ AND PRIOR <field_1> = <field_1>;
              

               

              Hope this helps.

               

              Manu.

              • 4. Re: How to use the LISTAGG in Oracle 10g?
                usman_noshahi

                take this query as sample data.

                 

                SELECT Level + 1 value1
                From   Dual
                       CONNECT BY Level < 10 ;  
                

                 

                Now Try this.

                 

                 

                SELECT Wm_Concat(value1)
                FROM
                       (
                              SELECT Level + 1 value1
                              FROM   Dual CONNECT BY Level < 10
                       );
                

                 

                 

                LISTAGG_ALTERNATIVE                                                           

                --------------------------------------------------------------------------------

                2,3,4,5,6,7,8,9,10 

                 

                Later you can replace "," with any special character you need.

                Hope it works.

                • 5. Re: How to use the LISTAGG in Oracle 10g?
                  Xuhaiduo

                  Dear,

                       Thank you very much firstly .I have seen  a card called 'Listagg function not working in Oracle form builder 11g'.You said that it was possible to create DB procedure/Function and call that .But now you tell me to use WM_CONCAT or  SYS_CONNECT_BY_PATH.If creating DB pocedure/Function can help me to use LISTAGG function in Oracle 10g?Of course,if it not ,I have to try  using the WM_CONCAT or  the SYS_CONNECT_BY_PATH analytical function.

                  • 6. Re: How to use the LISTAGG in Oracle 10g?
                    Xuhaiduo

                    Thank you very much.Maybe I have to use the  Wm_Concat instead of Listagg.

                    • 8. Re: How to use the LISTAGG in Oracle 10g?
                      Manu.

                      Dear,

                       

                      If your database version is 11g, then only you can use the LISTAGG function. And if you want to use these analytical functions (WM_CONCAT and SYS_CONNECT_BY_PATH) in forms, sorry to say that you cannot do that. For this you have to use DB procedure or need to create a record group using these functions.

                       

                      Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems.

                       

                       

                      Manu.

                      • 9. Re: How to use the LISTAGG in Oracle 10g?
                        BluShadow

                        WM_CONCAT is an undocumented function and Oracle will not support your product if you use it.

                        In fact I think it may be one of the features removed (or changed) in 12c, so you could end up with broken software after an database upgrade.

                         

                        You should never use undocumented functions.

                        Even Tom Kyte himself says so... Re: DISTINCT not working with  wmsys.wm_concat

                         

                        If anyone ever recommends to use WM_CONCAT, then they are sorely mistaken and likely have written code/application that will break in the future.

                        • 10. Re: How to use the LISTAGG in Oracle 10g?
                          BluShadow

                          Manu. wrote:

                           

                          Dear,

                           

                          If your database version is 11g, then only you can use the LISTAGG function. And if you want to use these analytical functions (WM_CONCAT and SYS_CONNECT_BY_PATH) in forms, sorry to say that you cannot do that. For this you have to use DB procedure or need to create a record group using these functions.

                           

                          Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems.

                           

                           

                          Manu.

                           

                          No need for a database procedure/function.  It could be a view on the database, which is simply queried from within Forms.  Forms doesn't need to know the SQL being used on the database, which is what the view will provide.

                          • 11. Re: How to use the LISTAGG in Oracle 10g?
                            usman_noshahi

                            Thanks BlueShadow for this info, I didn't consider the documented property of WM_CONCAT. Next time I will take care of this.

                            • 12. Re: How to use the LISTAGG in Oracle 10g?
                              Andreas Weiden

                              There is an alternative for LISTAGG which also works in 10G, check this Rows to String: Tom Kyte's STRAGG