-- The example below shows the use of Oracle analytic function 
-- COUNT(*) OVER (PARTITION BY
--                DECODE(MOD(level,2),0,'EVEN','ODD'))
-- to get counts split on the number type (num_type).  The
-- number string is then dynamically created.

-- Similar output can be obtained using  
-- Oracle function LISTAGG

DECLARE 
 CURSOR c_num IS
  SELECT level idx, DECODE(MOD(level,2),0,'EVEN','ODD') num_type,
   COUNT(*) OVER (PARTITION BY
                  DECODE(MOD(level,2),0,'EVEN','ODD')) cnts
  FROM DUAL CONNECT BY level <=10
  ORDER BY idx;
 
 v_oidx   SIMPLE_INTEGER := 0;
 v_eidx   SIMPLE_INTEGER := 0;
 v_odd    VARCHAR2(30);
 v_even   VARCHAR2(30);
 v_str    VARCHAR2(30);

 FUNCTION str_cc(i_val IN SIMPLE_INTEGER,
    i_cnt IN SIMPLE_INTEGER, i_str IN VARCHAR2)
  RETURN VARCHAR2 AS
  o_str  VARCHAR2(30);
 BEGIN
  IF i_val < i_cnt THEN
     o_str := i_str||', ';
  ELSE
     o_str := i_str;
  END IF;
  RETURN o_str;
 END;
BEGIN
 FOR num_rec IN c_num LOOP

  IF num_rec.num_type = 'ODD' THEN
      v_oidx := v_oidx +1;
      v_odd := v_odd||
               str_cc(v_oidx, num_rec.cnts, num_rec.idx);
  ELSE
      v_eidx := v_eidx +1;
      v_even := v_even||
                str_cc(v_eidx, num_rec.cnts, num_rec.idx);
  END IF;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('ODD  -> '||v_odd);
 DBMS_OUTPUT.PUT_LINE('EVEN -> '||v_even);
END;
/

=> Analytic Function Output