Oracle - RETURNING combined with aggregate functions

Question

Oracle supports RETURNING clause which could be very useful.

For example for data:

CREATE TABLE t(Id INT, Val varchar2(50));

INSERT INTO t(Id, Val)
SELECT 10,'a' FROM dual
UNION ALL SELECT 20,'b' FROM dual
UNION ALL SELECT 30,'a' FROM dual
UNION ALL SELECT 40,'b' FROM dual;

Query:

DECLARE
   l_cnt INT;
BEGIN
   DELETE FROM t RETURNING COUNT(*) INTO l_cnt;
   DBMS_OUTPUT.put_line('l_cnt: ' || l_cnt);
END;

l_cnt: 4

It supports MIN/MAX/AVG/SUM/LISTAGG:

DECLARE
   l_max INT;
   l_min INT;
   l_str VARCHAR2(100);
BEGIN
   DELETE FROM t 
   RETURNING MAX(id), MIN(id), LISTAGG(id, ',') WITHIN GROUP(ORDER BY id) 
   INTO l_max, l_min, l_str;
   DBMS_OUTPUT.put_line('l_max:'||l_max||' l_min:'||l_min||' l_str:'|| l_str);
END;

l_max:40 l_min:10 l_str:10,20,30,40

Unfortunately when combined with DISTINCT keyword I get an error:

DECLARE
   l_distinct_cnt INT;
BEGIN
   DELETE FROM t 
   RETURNING COUNT(DISTINCT val) INTO l_distinct_cnt ;
   DBMS_OUTPUT.put_line('l_distinct_cnt:' || l_distinct_cnt );
END;

ORA-00934: group function is not allowed here

db<>fiddle demo

The question is why aggregate functions with DISTINCT are not allowed? I am looking for an answer drawing from official sources.

---

EDIT:

Please note that COUNT(DISTINCT ...) was only an example. Same behavior is for SUM(col)/SUM(DISTINCT col) and any aggregate function that supports DISTINCT keyword.

SUM(val) vs SUM(DISTINCT val)

Answer 1

First of all, documentation and actual functionality is a bit out of sync so "official sources" will not shed a light on the details.

Syntactic diagram for 10g R2 (https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm) is below

In 11g (https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/returninginto_clause.htm) this was split into two: static_returning_clause (for insert, update, delete) and dynamic_returning_clause (for execute immediate). We are interested in the one for DML.

So for 10g there was a single row expression which according to documentation is Expression that returns a single row of a table. It's a subtle question whether DML statement must affect a single row or single row can be derived after execution of the statement (say, by using aggregate functions). I assume the idea was to use this syntax when DML operation affects single row (as opposed to bulk collect into); not using aggregate functions which return single row for affected rows.

So aggregate functions in returning into clause are not documented clearly. Moreover, for 11g just a column name may appear after returning keyword, so even expression like abs(column_name) is not allowed not to mention aggregate_function(column_name), even though in reality it works.

So, strictly speaking, this functionality with aggregate functions is not documented, especially for 11g, 12c, 18c and you cannot rely on it.

Instead you can use "bulk collect into" (and set operator to get distinct set of the elements)

SQL> create type str_tab as table of varchar2(4000)
  2  /

Type created.

SQL> set serveroutput on
SQL> declare
  2    i int;
  3    a str_tab;
  4  begin
  5    delete from t returning val bulk collect into a;
  6    dbms_output.put_line('cnt all ' || a.count || ' cnt distinct ' || set(a).count);
  7    rollback;
  8  end;
  9  /
cnt all 4 cnt distinct 2

PL/SQL procedure successfully completed.

Pay also attention to error message. It clearly says

ORA-00934: group function is not allowed here

Not just "distinct is not allowed" like in this example

SQL> select listagg(distinct val) within group (order by val) str from t;
select listagg(distinct val) within group (order by val) str from t
       *
ERROR at line 1:
ORA-30482: DISTINCT option not allowed for this function