Oracle Database Discussions

When using RETURNING LISTAGG(DISTINCT …) in a PL/SQL update or delete statement, the DISTINCT gets ignored.

I checked this in Oracle 19c, 21c and 26ai.

Here is an example:

CREATE TABLE mytable (col int);
INSERT INTO mytable VALUES (1);
INSERT INTO mytable VALUES (1);

DECLARE
  v_statement VARCHAR2(4000);
  v_values    VARCHAR2(4000);
BEGIN
  DELETE FROM mytable RETURNING LISTAGG(DISTINCT col, ',' ON OVERFLOW TRUNCATE) INTO v_values;
  DBMS_OUTPUT.PUT_LINE(v_values);
END;
/

Output is 1,1 but should be 1 of obviously.

Here is a link showing the faulty output for both UPDATE and DELETE: https://freesql.com/?compressed_code=H4sIAAAAAAAACsWQT2uDQBDF74Lf4d1iQG0sPTX04J%252BJXTBrWNf0GEwiyYLRkF0D%252FfZF2%252BRSKD0UehkY3pvh%252FV4sKJQEGUYZ4fRuqm1Tw9l1DVRrpnPbYrwgIcG4zO%252F6OsxKKuAEvzLYluch6g%252FPSFghGY8lVAtBshSc8RQZK2SYps5d9X1%252FCqWxrVV7gDq03aXe%252B%252BMbeVQaendRZwN97Ppmj643594gcLHtzdemEbgBVKtNXQ2XCcVZKMi2gOtGm8rUp7o1WIcifg3Fo%252FM0m82GqIN8rZq%252B1gC%252ByxGljA%252BucpUMrd1wC5IYKnsZ5w9ku65xMXEnyDnyNYlFlr9BipLHoaTpZ4e3BGOcJFoWm7yUq1L6q1JuMsbJuTmGSMSTuW09fLWsq1PtjvPvmRPKSBIWIl%252Fewf8D1fMojz8AeSRINbcCAAA%253D&code_language=PL_SQL&code_format=false