User defined aggregate function not being recognized as "group by" function

Question

select * from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

I know there are numerous ways of aggregating strings.
My needs are somewhat unique of please do not suggest alternate approaches.
I believe the following should work but it does not

I believe all of the code follows patterns I found in Oracle documentation

-- create type

CREATE OR REPLACE TYPE t_clob_agg_comma AS OBJECT
(
  g_string  clob,
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_clob_agg_comma)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_clob_agg_comma,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_clob_agg_comma,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_clob_agg_comma,
                                         returnValue  OUT  CLOB,
                                         flags        IN   NUMBER)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_clob_agg_comma,
                                     ctx2  IN      t_clob_agg_comma)
    RETURN NUMBER
);
/
show errors

-- create type body

CREATE OR REPLACE TYPE BODY t_clob_agg_comma IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_clob_agg_comma)
    RETURN NUMBER IS
  BEGIN
    sctx := t_clob_agg_comma(NULL);
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_clob_agg_comma,
                                       value  IN      VARCHAR2)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_clob_agg_comma,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_clob_agg_comma,
                                         returnValue  OUT  CLOB,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_clob_agg_comma,
                                     ctx2  IN      t_clob_agg_comma)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
show error

-- create function

CREATE OR REPLACE FUNCTION clob_agg (p_input VARCHAR2)
RETURN clob
PARALLEL_ENABLE AGGREGATE USING t_clob_agg_comma;
/
SHOW ERRORS

--error

SELECT department_id, clob_agg(last_name) over(order by last_name)  AS employees
FROM   demo.employee
group by department_id;

--SELECT department_id, clob_agg(last_name) over(order by last_name)  AS employees                             *
--ERROR at line 1:
--ORA-00979: not a GROUP BY expression

--error

SELECT department_id, clob_agg(last_name) within group order by last_name  AS employees
FROM   demo.employee
group by department_id;

--error

SELECT department_id, clob_agg(last_name) over(partition by department_id)  AS employees
FROM   demo.employee
group by department_id;

--SELECT department_id, clob_agg(last_name) within group order by last_name  AS employees                                    --                                                 *
--ERROR at line 1:
--ORA-00923: FROM keyword not found where expected

--return multiple rows per department

SELECT department_id, clob_agg(last_name) over(partition by department_id order by last_name)  AS employees
FROM   demo.employee;

--error

SELECT department_id, clob_agg(last_name) over(partition by department_id order by last_name)  AS employees
FROM   demo.employee
group by department_id;
--SELECT department_id, clob_agg(last_name) over(partition by department_id order by last_name)  AS
--                               *
--ERROR at line 1:
--ORA-00979: not a GROUP BY expression

--does not sort, does not return department_id

SELECT clob_agg(last_name)   AS employees
FROM   demo.employee
group by department_id;

--does not sort

SELECT department_id, clob_agg(last_name)  AS employees
FROM   demo.employee
group by department_id;

Answer 1

It seems you don't understand the difference between aggregate functions (such as SUM()) and their analytic versions (such as SUM() OVER()), yet. Let me try to explain:

You can select the salary per employee from your table. You get one row per employee.

select name, salary from employee;

Or you aggregate your rows, so you don't get one record per employee anymore:

select avg(salary) from employee;

As you are aggregating your rows (by using an aggregate function) without a GROUP BY clause, you get only one result row containing the aggregate over all selected records. In the example we select the avarage salary in the table. We cannot show the employee name or the employee's department in the same result, because we are no longer talking about employees, but about the aggregated row over all employees.

With GROUP BY we can aggregate per group, e.g. get one line per department and show the aggregates per department, say the avarage salary per department:

select department, avg(salary) from employee group by department;

We still cannot show the employee's name in the results, because again we are no longer talking about employees, but about departments here. There can be many different employee names per department. All we can show is what we group by (the department) and aggregates over the records per department (i.e. use aggregate functions as avg(salary) or min(name) etc.).

With the analytic functions we do something different; we show the originally selected rows, but show additional aggregates over these rows. So it is one record per employee, as in the first query above, but with additional columns. Here we show the employee's salary along with the avarage salary over all selected records:

select name, salary, avg(salary) over () from employee;

And here we do the same, but show the avarage salary over all selected records per employee's department instead:

select name, salary, avg(salary) over (partition by department) from employee;

At last there is the rare case when we combine record aggregation and analytic functions. We show one row per department (aggregation with group by) and along with the avarage salary for each department, we show the minimum avarage salary of all selected departments:

select department, avg(salary), min(avg(salary)) over ()
from employee 
group by department;

We cannot use, say, avg(salary) over () in this query. We have already aggregated the rows with group by department, so there is no one salary in the resulting rows that we can run our analytic avg over. We can only use it on aggregates, such as min(name) or avg(salary) or on the grouped columns (here: department).

And no matter what your query, whether with GROUP BY, DISTINCT, or whatever: if you want your result rows sorted, you must use ORDER BY at the end of the query. Otherwise, even if your results happen to be sorted, there is no guarantee for this to be the case.

Answer 2

All errors stem from using the aggregate function incorrectly.

• 1. error: You are not using clob_agg as an aggregation function, which would be clob_agg(last_name), but as an analytic function: clob_agg(last_name) over(order by last_name). This means you get one value per row, but last_name is not aggregated, hence not part of your result row.
• 2. error: WITHIN GROUP is something special for LISTAGG. I doubt that it works with any other aggregate function.
• 3. error: same as 1. error.
• 4. error: same as 1. error.

Then:

--does not sort, does not return department_id
SELECT clob_agg(last_name)   AS employees
FROM   demo.employee
group by department_id;

You are not selecting department_id, so it is not returned naturally. It does not sort, because there is no ORDER BY clause.

And:

--does not sort
SELECT department_id, clob_agg(last_name)  AS employees
FROM   demo.employee
group by department_id;

Same problem: no ORDER BY clause.