Tag Archives: ORACLE 11g Release 2

Package Initialization – Changes between ORACLE 11g and ORACLE 12c

16 Wednesday May 2018

Posted by in ORACLE 11g Release 2, ORACLE 18c

≈ Leave a comment

Tags

, , ,

The package initialization block

When a session references a package item, the package is instantiated and initialized. Initializing means:

  • assigning values to public constants (constants defined in the package specification or body outside of packaged procedures and functions)
  • assigning values to public variables if values are specified in variable declaration
  • execute the initial block of the package body  
CREATE OR REPLACE PACKAGE test_init IS
   PROCEDURE show_values;
END test_init;
/

CREATE OR REPLACE PACKAGE BODY test_init IS
   g_before_error VARCHAR2(30) := 'NOT INITIALIZED';
   g_after_error  VARCHAR2(30) := 'NOT INITIALIZED';
   g_test         VARCHAR2(5);

   PROCEDURE show_values IS
   BEGIN
      sys.dbms_output.put_line('Before : ' || g_before_error);
      sys.dbms_output.put_line('After  : ' || g_after_error);
   END show_values;

-- INIT BLOCK
BEGIN
   g_before_error := 'INITIALIZED';
   g_test         := 'This will raise an error';
   g_after_error  := 'INITIALIZED';
END test_init;
/

What happens if the initialization block raises an error?

Now basically you get an error – as we would expect.

BEGIN
   test_init.show_values();
END;
/

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TEST.TEST_INIT", line 13
ORA-06512: at line 2
06502. 00000 - "PL/SQL: numeric or value error%s"

So far so good – but now, a significant thing has changed between ORACLE 11 and ORACLE 12.

What happens if we call the show_values procedure again?

ORACLE 12 (same in 18)

BEGIN
   test_init.show_values();
END;
/

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TEST.TEST_INIT", line 13
ORA-06512: at line 2
06502. 00000 - "PL/SQL: numeric or value error%s"

So in ORACLE 12 the initialization block is executed again. This indicates, that the package was not instantiated due to the error in the initialization block. 

ORACLE 11

BEGIN
   test_init.show_values();
END;
/

PL/SQL procedure successfully completed.

Before : INITIALIZED
After  : NOT INITIALIZED

In ORACLE 11 the packages has been instantiated even if the initialization run into an error. So a second call will not run the initialization again…

As you can see the second global variable (g_after_error) has not been changed by the initialization block whereas the first was.

 

What does this mean?

In my opinion the current (12c/18c) behaviour is much more consistent…but this change in behaviour can (and will) affect applications that did not care too much on whether the initialization run into an error in Oracle 11g – as a secondary call to a package item that does not depend on a correct initialization of the package worked..

 

 

 

ORACLE 12c Release 1 – Session Level Sequences

18 Wednesday Sep 2013

Posted by in ORACLE 11g Release 2

≈ Leave a comment

Tags

, , ,

A sequence that starts at the starting point with every session that accesses the sequence…what is this good for?

Now, I could think at things like logging, where we could give every step of a session a ascending step_id, which would be stored in the log table along with the session_id to which the step belonged. Or we could have a transaction which creates a series of entries in the database and within the transaction those entries are numbered for whatever purpose. Generating Primary Keys for a Global Temporary Table would be another field where those kinds of sequences could be used.

Create a session level sequence

A session level sequence is generated using the new SESSION key word. This will restart the sequence for every session.

CREATE SEQUENCE session_seq
   START WITH   1
   INCREMENT BY 1
   SESSION
/

Usage

Example filling a global temporary table using a session level sequence as primary key generator:

CREATE SEQUENCE session_seq
 START WITH   1
 INCREMENT BY 1
 SESSION
/

Sequence created.

CREATE GLOBAL TEMPORARY TABLE employees_temp_table (
    employee_id  NUMBER DEFAULT session_seq.nextval NOT NULL
   ,first_name VARCHAR2(20)
   ,last_name VARCHAR2(25)
   )
   ON COMMIT PRESERVE ROWS
/

Table created.

REM ===========================================================================
REM Add rows to table
REM ===========================================================================
INSERT INTO employees_temp_table (first_name, last_name)
SELECT first_name, last_name
  FROM hr.employees
 WHERE rownum < 20
/

19 rows created.

REM ===========================================================================
REM Show the results
REM ===========================================================================
SELECT *
  FROM employees_temp_table
/

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
          1 Ellen                Abel
          2 Sundar               Ande
          3 Mozhe                Atkinson
          4 David                Austin
          5 Hermann              Baer
          6 Shelli               Baida
          7 Amit                 Banda
          8 Elizabeth            Bates
          9 Sarah                Bell
         10 David                Bernstein
         11 Laura                Bissot
         12 Harrison             Bloom
         13 Alexis               Bull
         14 Anthony              Cabrio
         15 Gerald               Cambrault
         16 Nanette              Cambrault
         17 John                 Chen
         18 Kelly                Chung
         19 Karen                Colmenares

19 rows selected.

If another session does the same thing, the employee_id will start at 1 again.

Recursive WITH Clause (4)

23 Thursday May 2013

Posted by in ORACLE 11g Release 2

≈ Leave a comment

Tags

, , ,

How to implement CONNECT BY features

ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.

This Blog entry shows how to implement all the CONNECT BY features using the new recursive WITH clause.

Leaf Nodes

Using CONNECT BY the pseudo column CONNECT_BY_ISLEAF can be used to determine whether a node is a leaf node (has no children) or an inner node. The value of the pseudo column CONNECT_BY_ISLEAF is “0” for an inner node and “1” for leaf nodes.

SELECT LEVEL, ename, CONNECT_BY_ISLEAF leaf, empno, mgr
  FROM emp e
 WHERE deptno = 10
CONNECT BY PRIOR empno = mgr
 START WITH mgr is NULL
 ORDER SIBLINGS BY ename
/

LEVEL      ENAME      LEAF       EMPNO      MGR
---------- ---------- ---------- ---------- ----------
         1 KING                0       7839
         2 CLARK               0       7782       7839
         3 MILLER              1       7934       7782

Recursive WITH clause does not offer a built-in function that provides this information. Easiest way to get the information is using a subquery (in-/outside the WITH clause) that counts the number of child nodes.

WITH emps (ename, empno, mgr, lvl)
       AS (SELECT ename, empno, mgr, 1 AS lvl
             FROM emp
            WHERE mgr IS NULL
           UNION ALL
           SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
             FROM            emps m
                  INNER JOIN emp  e ON (e.mgr = m.empno)
            WHERE e.deptno = 10)
SEARCH DEPTH FIRST BY ename SET sorting
SELECT e.lvl, e.ename
     , 1 - LEAST((SELECT COUNT(*) FROM emp x WHERE x.mgr = e.empno),1) leaf
     , e.empno, e.mgr
  FROM emps e
 ORDER BY e.sorting
/

LEVEL      ENAME       LEAF      EMPNO       MGR
---------- ---------- ---------- ---------- ----------
         1 KING                0       7839
         2 CLARK               0       7782       7839
         3 MILLER              1       7934       7782

If the SEARCH clause uses DEPTH_FIRST, the analytic function LEAD can be used to determine whether the column LVL of next row in the result set has a higher value than the current row. If so, the current row is not a leaf otherwise the current row is a leaf.

WITH emps (ename, empno, mgr, lvl)
       AS (SELECT ename, empno, mgr, 1 AS lvl
             FROM emp
            WHERE mgr IS NULL
           UNION ALL
           SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
             FROM            emps m
                  INNER JOIN emp  e ON (e.mgr = m.empno)
            WHERE e.deptno = 10)
SEARCH DEPTH FIRST BY ename SET sortingSELECT lvl
      ,ename
      ,CASE
          WHEN lvl - LEAD(lvl) OVER (ORDER BY sorting) < 0 
             THEN 0 
          ELSE 1 
       END AS leaf 
     ,empno
     ,mgr 
  FROM emps 
 ORDER BY sorting 
/ 

LVL        ENAME      LEAF       EMPNO      MGR 
---------- ---------- ---------- ---------- ---------- 
         1 KING                0       7839 
         2 CLARK               0       7782       7839 
         3 MILLER              1       7934       7782

Recursive WITH clause (3)

03 Thursday Nov 2011

Posted by in ORACLE 11g Release 2

≈ Leave a comment

Tags

, , ,

How to implement CONNECT BY features

ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.

This Blog entry shows how to implement all the CONNECT BY features using the new recursive WITH clause.

Cycle Detection

If the data contains a cylce the query would run indefinitely. ORACLE detects these situations and lets the query fail.

UPDATE emp
   SET mgr = 7499
 WHERE empno = 7839
/

SELECT LEVEL, e.ename
  FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.empno = 7839
 ORDER SIBLINGS BY e.ename
/

ERROR:
ORA-01436: CONNECT BY loop in user data

Recursive suquery factoring throws a different error code with a similar message.

WITH r_emps (ename, empno, mgr, lvl, path, root)
         AS (SELECT e.ename, e.empno, e.mgr
                  , 1 as lvl
                  , '/' || e.ename AS path
                  , e.ename        AS root
               FROM emp e
              WHERE e.empno = 7839
             UNION ALL
             SELECT e.ename, e.empno, e.mgr
                  , m.lvl + 1                AS lvl
                  , m.path || '/' || e.ename AS path
                  , m.root
               FROM            emp    e
                    INNER JOIN r_emps m ON (m.empno = e.mgr))
SEARCH DEPTH FIRST BY ename SET sorting
SELECT e.lvl, e.ename, e.empno, e.mgr, e.path, e.root
  FROM r_emps e
 ORDER BY e.sorting
/

ERROR at line ..:
ORA-32044: cycle detected while executing recursive WITH query

NOCYLCE

Since ORACLE 10g the CONNECT BY clause knows the NOCYLCE attribute as well as the CONNECT_BY_ISCYCLE pseudo column to ignore cycles and detect nodes with a connection to a node already included in the result set (cycle).

SELECT  LEVEL
      , CONNECT_BY_ISCYCLE                AS CYCLE
      , e.ename
      , SYS_CONNECT_BY_PATH(e.ename, '/') AS path 
      , e.mgr
  FROM emp e
CONNECT BY NOCYCLE PRIOR e.empno = e.mgr
 START WITH e.empno = 7839
 ORDER SIBLINGS BY e.ename
/

LEVEL  CYCLE  ENAME     PATH                    MGR
-----  -----  --------  ---------------------  ----
    1  0      KING     /KING                   7499
    2  0      BLAKE    /KING/BLAKE             7839
    3  1      ALLEN    /KING/BLAKE/ALLEN       7698
    3  0      JAMES    /KING/BLAKE/JAMES       7698
    3  0      MARTIN   /KING/BLAKE/MARTIN      7698
    3  0      TURNER   /KING/BLAKE/TURNER      7698
    3  0      WARD     /KING/BLAKE/WARD        7698
    2  0      CLARK    /KING/CLARK             7839
    3  0      MILLER   /KING/CLARK/MILLER      7782
    2  0      JONES    /KING/JONES             7839
    3  0      FORD     /KING/JONES/FORD        7566
    4  0      SMITH    /KING/JONES/FORD/SMITH  7902
    3  0      SCOTT    /KING/JONES/SCOTT       7566

The cycle was detected at node ALLAN (CONNECT_BY_ISCYCLE returned the value 1) and further processing of this branch is stopped.
The recursive WITH clause also offers a clause to handle cycles.

WITH r_emps (ename, empno, mgr, lvl, path)
         AS (SELECT e.ename, e.empno, e.mgr, 1 AS lvl, '/' || e.ename AS path
               FROM emp e
              WHERE e.empno = 7839
             UNION ALL
     SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl, m.path || '/' || e.ename AS path
               FROM            r_emps m 
                    INNER JOIN emp    e ON (e.mgr = m.empno))
SEARCH DEPTH FIRST BY ename SET SORTING
CYCLE empno SET is_cycle TO 1 DEFAULT 0
SELECT e.lvl AS LEVEL, is_cycle AS CYCLE, e.ename, e.path ,e.mgr
  FROM r_emps e
 ORDER BY e.sorting
/

LEVEL  CYCLE  ENAME      PATH                      MGR
-----  -----  ---------  -----------------------  ----
    1      0  KING       /KING                    7499
    2      0  BLAKE      /KING/BLAKE              7839
    3      0  ALLEN      /KING/BLAKE/ALLEN        7698
    4      1  KING       /KING/BLAKE/ALLEN/KING   7499
    3      0  JAMES      /KING/BLAKE/JAMES        7698
    3      0  MARTIN     /KING/BLAKE/MARTIN       7698
    3      0  TURNER     /KING/BLAKE/TURNER       7698
    3      0  WARD       /KING/BLAKE/WARD         7698
    2      0  CLARK      /KING/CLARK              7839
    3      0  MILLER     /KING/CLARK/MILLER       7782
    2      0  JONES      /KING/JONES              7839
    3      0  FORD       /KING/JONES/FORD         7566
    4      0  SMITH      /KING/JONES/FORD/SMITH   7902
    3      0  SCOTT      /KING/JONES/SCOTT        7566
    4      0  ADAMS      /KING/JONES/SCOTT/ADAMS  7788

The difference between CONNECT BY and RECURSIVE WITH cycle detection is that with RECURSIVE WITH the cycle is decteted after the next recursion level wass processed. The erroneous node is repeated and the cycle flag is set one level lower than the CONNECT_BY_ISCYCLE pseudo column.

Recursive WITH clause (2)

19 Monday Sep 2011

Posted by in ORACLE 11g Release 2

≈ Leave a comment

Tags

, , ,

How to implement CONNECT BY features

 ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.
This Blog entry shows how to implement all the CONNECT BY features using the new recursive WITH clause.

Root and Path

CONNECT BY clause knows the CONNECT_BY_ROOT operator which returns the root(s) of a hierarchy. Furthermore the SYS_CONNECT_BY_PATH function may be used to get a path from the root to the current element within the hierarchy:

SELECT e.ename, e.empno, e.mgr
      ,SYS_CONNECT_BY_PATH(e.ename,'/') AS path
      ,CONNECT_BY_ROOT e.ename          AS root
  FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.mgr IS NULL
 ORDER SIBLINGS BY e.ename
/

ENAME    EMPNO   MGR  PATH                     ROOT
-------  -----  ----  -----------------------  ----
KING      7839        /KING KING               
BLAKE     7698  7839  /KING/BLAKE              KING
ALLEN     7499  7698  /KING/BLAKE/ALLEN        KING
JAMES     7900  7698  /KING/BLAKE/JAMES        KING
MARTIN    7654  7698  /KING/BLAKE/MARTIN       KING
TURNER    7844  7698  /KING/BLAKE/TURNER       KING
WARD      7521  7698  /KING/BLAKE/WARD         KING
CLARK     7782  7839  /KING/CLARK              KING
MILLER    7934  7782  /KING/CLARK/MILLER       KING
JONES     7566  7839  /KING/JONES              KING
FORD      7902  7566  /KING/JONES/FORD         KING
SMITH     7369  7902  /KING/JONES/FORD/SMITH   KING
SCOTT     7788  7566  /KING/JONES/SCOTT        KING
ADAMS     7876  7788  /KING/JONES/SCOTT/ADAMS  KING

Same result using the recursive WITH clause:

WITH r_emps (ename, empno, mgr, path, root)
         AS (SELECT e.ename, e.empno, e.mgr
                  , '/' || e.ename AS path
                  , e.ename        AS root
               FROM emp e
              WHERE e.mgr IS NULL
             UNION ALL
             SELECT e.ename, e.empno, e.mgr
                  , m.path || '/' || e.ename AS path
                  , m.root
               FROM            emp    e
                    INNER JOIN r_emps m ON (m.empno = e.mgr))
SEARCH DEPTH FIRST BY ename SET sorting
SELECT e.ename, e.empno, e.mgr, e.path, e.root
  FROM r_emps e
 ORDER BY e.sorting
/

Path and root are set to ename in the anchor query. While the path is enlarged along the hierarchy by adding separator and path the root is simply passed all the way down to the leafs.

Recursive WITH clause (1)

19 Monday Sep 2011

Posted by in ORACLE 11g Release 2, Uncategorized

≈ Leave a comment

Tags

, , ,

how to implement CONNECT BY features

ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.

This Blog entries show how to implement all the CONNECT BY features using the new recursive WITH clause.

Basic recursive query

CONNECT BY clause to show all employees starting with KING (who has no manager [mgr IS NULL]):

SELECT e.ename, e.empno, e.mgr
  FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.mgr IS NULL
/

Same result using the recursive WITH clause:

WITH r_emps (ename, empno, mgr)          AS (SELECT e.ename, e.empno, e.mgr
               FROM emp e
              WHERE e.mgr IS NULL
             UNION ALL
             SELECT e.ename, e.empno, e.mgr
               FROM            emp    e
                    INNER JOIN r_emps m ON (m.empno = e.mgr))
SELECT e.ename, e.empno, e.mgr
  FROM r_emps e
/

The first query block (anchor member) of the recursive WITH clause defines the root(s) of the hierarchy, the second the recursion. In the second block, we can see a join between the
anchor member and the emp table which is pretty much the same as the CONNECT BY clause in the traditional approach.

Formatting the result set

CONNECT BY queries often use the LEVEL pseudo column to format the output.

SELECT LEVEL, LPAD(' ',2*(LEVEL-1)) || e.ename AS ename
     , e.empno, e.mgr
  FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.mgr IS NULL
/

The same can be achieved using the following recursive WITH query:

WITH r_emps (ename, empno, mgr, lvl)
         AS (SELECT e.ename, e.empno, e.mgr, 1 AS lvl
               FROM emp e
              WHERE e.mgr IS NULL
             UNION ALL
            SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
               FROM            emp    e
                    INNER JOIN r_emps m ON (m.empno = e.mgr))
SELECT lvl, LPAD(' ',2*(lvl-1)) || e.ename AS ename
      ,e.empno, e.mgr
  FROM r_emps e
/

To simulate the LEVEL pseudo column using the recursive WITH clause, one has to add a virtual level column on the root elements, which will be incremented along the hierarchy.

Sorting the result set

The CONNECT BY clause has a dedicated ORDER BY clause (ORDER SIBLINGS BY) to sort the elements of a hierarchy.

SELECT LEVEL, LPAD(' ',2*(LEVEL-1)) || e.ename AS ename
     , e.empno, e.mgr
  FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.mgr IS NULL
 ORDER SIBLINGS BY e.ename
/

Same result can be achieved using the recursive WITH clause with the SEARCH clause.

WITH r_emps (ename, empno, mgr, lvl)
         AS (SELECT e.ename, e.empno, e.mgr, 1 AS lvl
               FROM emp e
              WHERE e.mgr IS NULL
             UNION ALL
             SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
               FROM            emp    e
                    INNER JOIN r_emps m ON (m.empno = e.mgr))
SEARCH DEPTH FIRST BY ename SET sorting
SELECT lvl, LPAD(' ',2*(lvl-1)) || e.ename AS ename
     , e.empno ,e.mgr
  FROM r_emps e
 ORDER BY e.sorting
/

SEARCH DEPTH FIRST will show the children before the siblings whereas SEARCH BREADTH FIRST would show the siblings before the children, the ename column is used as the order attribute within the hierarchy and assigned to the attribute sorting which can afterwards be used in the outermost query.

5 ways to aggregate columns into a comma separated string

05 Monday Oct 2009

Posted by in ORACLE 11g Release 2, Uncategorized

≈ Leave a comment

Tags

, , , , ,

Connect By

The first example uses a recursive query which starts with the first node of every group and only shows those rows, where the whole string has been aggregated (connect_by_isleaf = 1).

SELECT deptno
     , rn
     , TRIM(leading ',' FROM SYS_CONNECT_BY_PATH(ename,',')) 
  FROM (SELECT deptno
             , ename
             , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) rn
          FROM emp)
  WHERE connect_by_isleaf = 1
  START WITH rn = 1
 CONNECT BY PRIOR deptno = deptno
       AND PRIOR rn + 1 = rn
 /

XMLAGG

another solution is to use xml functions like xmlagg (aggregate) and xmlelement.

SELECT deptno
      ,RTRIM(XMLAGG(XMLELEMENT(e,ename,',').EXTRACT('//text()')),',') name
  FROM emp
 GROUP BY deptno
 /

 WM_CONCAT

one more solution even if not documented…

SELECT deptno, WM_CONCAT(ename)
  FROM emp
 GROUP by deptno
 /

and if you need the enames to be ordered…

SELECT deptno
     , WM_CONCAT(ename)
 FROM (SELECT deptno, ename 
         FROM emp 
        ORDER BY ename)
 GROUP by deptno
 /

 User defined aggregator

Another solution is to write your own group function

CREATE OR REPLACE TYPE ListAggregator IS OBJECT (
   vList VARCHAR2(4000)
  ,STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT ListAggregator) RETURN NUMBER
  ,MEMBER FUNCTION ODCIAggregateIterate (SELF  IN OUT ListAggregator
                                       , value IN     VARCHAR2 ) RETURN NUMBER
  ,MEMBER FUNCTION ODCIAggregateTerminate (self        IN OUT ListAggregator
                                         , returnValue    OUT VARCHAR2
                                         , flags       IN     NUMBER ) RETURN NUMBER
  ,MEMBER FUNCTION ODCIAggregateMerge (self IN OUT ListAggregator
                                     , ctx2 IN     ListAggregator ) RETURN NUMBER
 );
 /

CREATE OR REPLACE TYPE BODY ListAggregator IS
   STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT ListAggregator) RETURN NUMBER IS
   BEGIN
      sctx := ListAggregator(NULL);
      RETURN ODCIConst.SUCCESS;
   END ODCIAggregateInitialize;

   MEMBER FUNCTION ODCIAggregateIterate (self  IN OUT ListAggregator
                                       , value IN     VARCHAR2 ) RETURN NUMBER IS
   BEGIN
      IF value IS NOT NULL
      THEN
         self.vList := concat(concat(self.vList,', '),value);
      END IF;

      RETURN ODCIConst.SUCCESS;
   END ODCIAggregateIterate;

   MEMBER FUNCTION ODCIAggregateTerminate (self        IN OUT ListAggregator
                                         , returnValue    OUT VARCHAR2
                                         , flags       IN     NUMBER ) RETURN NUMBER IS
   BEGIN
      returnValue := LTrim(self.vList, ', ');
      RETURN ODCIConst.SUCCESS;
   END ODCIAggregateTerminate;

   MEMBER FUNCTION ODCIAggregateMerge (self IN OUT ListAggregator
                                     , ctx2 IN     ListAggregator) RETURN NUMBER IS
   BEGIN
      IF self.vList IS NULL
      THEN
         self.vList := ctx2.vList;
      ELSIF ctx2.vList IS NOT NULL
      THEN
         self.vList := concat(concat(self.vList,', '),ctx2.vList);
      END IF;

      RETURN ODCIConst.SUCCESS;
   END ODCIAggregateMerge;
END;
/

CREATE OR REPLACE FUNCTION LIST(vValue IN VARCHAR2) RETURN VARCHAR2
   PARALLEL_ENABLE
   AGGREGATE
   USING ListAggregator;
/

SELECT deptno
     , LIST(ename)
  FROM emp
 GROUP BY deptno
/

LISTAGG

and finally if you are on ORACLE 11R2, there is a built-in function to do list aggregation

SELECT LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename)
 FROM emp
 GROUP BY deptno
 /