Return to Snippet

Revision: 12768
at March 27, 2009 09:24 by pollux


Initial Code
FUNCTION f_concatinate_list (p_cursor IN sys_refcursor)
      RETURN Varchar2(32787)
   IS
      v_return                  Varchar2(32787);
      v_temp                    Varchar2(4000);
   BEGIN
      LOOP
         FETCH p_cursor
          INTO v_temp;

         EXIT WHEN p_cursor%NOTFOUND;
         v_return := v_return || ',' || v_temp;
      END LOOP;

      RETURN LTRIM (v_return, ',');
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END f_concatinate_list;

-- CALLED THIS WAY:
SELECT      a.Id,
                   f_Concatinate_List
                       (CURSOR (
                               SELECT  To_Char(b.strValue)
                               FROM    table1 b
                               WHERE   b.Id = a.Id
                               )
                       )                              AS concatenate_str
       FROM        table1  a
       WHERE       a.Id = 7374
       GROUP BY    a.Id;

Initial URL
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

Initial Description
See url for other techniques

Initial Title
Grouping function that concatenates strings

Initial Tags

                                

Initial Language
PL/SQL