Consider this script (which I've also put on Live SQL:
https://livesql.oracle.com/apex/livesql/s/hbfb7x0c47ubwjpygs3klr3wj )
CREATE TABLE t (
id NUMBER(10) GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY,
category NUMBER(10) NOT NULL,
counter NUMBER(10),
text VARCHAR2(10) NOT NULL
);
INSERT INTO t (category, text)
SELECT dbms_random.value(1, 10), dbms_random.string('a', 10)
FROM dual
CONNECT BY level <= 100;
DECLARE
v_text VARCHAR2(2000);
v_updated PLS_INTEGER := 0;
BEGIN
UPDATE t
SET counter = nvl(counter, 0) + 1
WHERE category = 1
RETURNING
listagg (text, ', ') WITHIN GROUP (ORDER BY text DESC),
count(*)
INTO
v_text,
v_updated;
COMMIT;
dbms_output.put_line('Rows updated: ' || v_updated);
dbms_output.put_line('Returned: ' || v_text);
END;
When I run it, I get something like this:
Rows updated: 8
Returned: EHjMWhyYYB, NeXOeOUOtl, eCKQeVNgii, vdfoaOEDbS, WnfPUiTNcZ, ciBYUHUgEe, ccZVEgaVAm, sPyXHepzae
No matter what I put in the WITHIN GROUP (ORDER BY ..) clause, the ordering is ignored and seems random. Is this a bug or a known restriction? Is there a workaround?
My guess is a bug (and I'll log it).
Pretty much as long as its been implemented, RETURNING has always been about simple expressions or "simple" aggregates (where I'm using the term 'simple' to be nothing that implies ordering), eg, we're not allowed to DISTINCT in the aggregation etc.
So I'd be willing to hypothesize that we have never implemented an *ordering* operation in the RETURNING clause without realising that LISTAGG is the first such aggregate that needs both aggregation AND order.
I modified your demo a bit - looks like its simply aggregated as encountered in the table, eg
SQL> CREATE TABLE t (
2 id NUMBER(10) GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY,
3 category NUMBER(10) NOT NULL,
4 counter NUMBER(10),
5 text VARCHAR2(10) NOT NULL
6 );
Table created.
SQL>
SQL> INSERT INTO t (category, text)
2 SELECT mod(rownum,3), chr(64+rownum)
3 FROM dual
4 CONNECT BY level <= 10;
10 rows created.
SQL>
SQL> select * from t;
ID CATEGORY COUNTER TEXT
---------- ---------- ---------- ----------
1 1 A
2 2 B
3 0 C
4 1 D
5 2 E
6 0 F
7 1 G
8 2 H
9 0 I
10 1 J
10 rows selected.
SQL>
SQL> set serverout on
SQL> DECLARE
2 v_text VARCHAR2(2000);
3 v_updated PLS_INTEGER := 0;
4 BEGIN
5 UPDATE t
6 SET counter = nvl(counter, 0) + 1
7 WHERE category = 1
8 RETURNING
9 listagg (text, ', ') WITHIN GROUP (ORDER BY text ),
10 count(*)
11 INTO
12 v_text,
13 v_updated;
14
15 COMMIT;
16 dbms_output.put_line('Rows updated: ' || v_updated);
17 dbms_output.put_line('Returned: ' || v_text);
18 END;
19 /
Rows updated: 4
Returned: A, D, G, J
PL/SQL procedure successfully completed.
SQL>
SQL> delete t;
10 rows deleted.
SQL> INSERT INTO t (category, text)
2 SELECT mod(rownum,3), chr(64+rownum)
3 FROM dual
4 CONNECT BY level <= 10
5 order by 2 desc; <====== changed order
10 rows created.
SQL>
SQL> select * from t;
ID CATEGORY COUNTER TEXT
---------- ---------- ---------- ----------
11 1 J
12 0 I
13 2 H
14 1 G
15 0 F
16 2 E
17 1 D
18 0 C
19 2 B
20 1 A
10 rows selected.
SQL>
SQL> set serverout on
SQL> DECLARE
2 v_text VARCHAR2(2000);
3 v_updated PLS_INTEGER := 0;
4 BEGIN
5 UPDATE t
6 SET counter = nvl(counter, 0) + 1
7 WHERE category = 1
8 RETURNING
9 listagg (text, ', ') WITHIN GROUP (ORDER BY text ),
10 count(*)
11 INTO
12 v_text,
13 v_updated;
14
15 COMMIT;
16 dbms_output.put_line('Rows updated: ' || v_updated);
17 dbms_output.put_line('Returned: ' || v_text);
18 END;
19 /
Rows updated: 4
Returned: J, G, D, A
PL/SQL procedure successfully completed.
Workaround is a bit clunky because the responsibility to sort the rows falls onto you, eg
SQL> delete t;
10 rows deleted.
SQL> INSERT INTO t (category, text)
2 SELECT mod(rownum,3), chr(64+rownum)
3 FROM dual
4 CONNECT BY level <= 10
5 order by 2 desc;
10 rows created.
SQL>
SQL> select * from t;
ID CATEGORY COUNTER TEXT
---------- ---------- ---------- ----------
31 1 J
32 0 I
33 2 H
34 1 G
35 0 F
36 2 E
37 1 D
38 0 C
39 2 B
40 1 A
10 rows selected.
SQL>
SQL> set serverout on
SQL> DECLARE
2 v_text VARCHAR2(2000);
3 v_updated PLS_INTEGER := 0;
4
5 l_list sys.odcivarchar2list := sys.odcivarchar2list();
6 BEGIN
7 UPDATE t
8 SET counter = nvl(counter, 0) + 1
9 WHERE category = 1
10 RETURNING text bulk collect into l_list;
11
12 select listagg(column_value,',') within group ( order by column_value)
13 into v_text
14 from table(l_list);
15
16 COMMIT;
17 dbms_output.put_line('Rows updated: ' || l_list.count);
18 dbms_output.put_line('Returned: ' || v_text);
19 END;
20 /
Rows updated: 4
Returned: A,D,G,J
PL/SQL procedure successfully completed.