ORA-01422: exact fetch returns more than requested number of rows in RETURNING INTO

Question

I have the following sql (oracle) that removes all rows from a table except the 100 newest.

DELETE FROM my_table tab_outer
WHERE tab_outer.rowid IN (
    -- Fetch rowids of rows to delete
    SELECT rid FROM (
        SELECT rownum r, rid FROM (
            SELECT tab.rowid rid
                FROM my_table tab
                ORDER BY tab.created_date DESC
            )
        )
        -- Delete everything but the 100 nesest rows
        WHERE r > 100
)
-- Return newest date that was removed
RETURNING max(tab_outer.created_date) INTO :latestDate

This code sometimes gives a ORA-01422: exact fetch returns more than requested number of rows claiming that more than one row was inserted into latestDate. How is this possible? The aggregate function (max) in the RETURNING INTO clause should ensure that only one row is returned, no? Could it have anything to do with the explicit use or rowid (I don't see how)?

Answer 1

I thought it was not possible to use aggregates in the returning clause, as I had never tried it and it isn't mentioned in the documentation, but it actually works (11gr2) !!

See below in PL/SQL:

SQL> CREATE TABLE my_table (created_date DATE);

Table created

SQL> INSERT INTO my_table
  2     (SELECT SYSDATE + ROWNUM FROM dual CONNECT BY LEVEL <= 500);

500 rows inserted

SQL> DECLARE
  2     latestDate DATE;
  3  BEGIN
  4     DELETE FROM my_table tab_outer
  5     WHERE tab_outer.rowid IN (
  6         -- Fetch rowids of rows to delete
  7         SELECT rid FROM (
  8             SELECT rownum r, rid FROM (
  9                 SELECT tab.rowid rid
 10                     FROM my_table tab
 11                     ORDER BY tab.created_date DESC
 12                 )
 13             )
 14             -- Delete everything but the 100 nesest rows
 15             WHERE r > 100
 16     )
 17     -- Return newest date that was removed
 18     RETURNING max(tab_outer.created_date) INTO latestDate;
 19     dbms_output.put_line(latestDate);
 20  END;
 21  /

06/08/14

And even in SQL*Plus (10.2.0.1.0 client, 11.2.0.3.0 database):

SQL> VARIABLE latestDate VARCHAR2(20);
SQL> DELETE FROM my_table tab_outer
  2  WHERE tab_outer.rowid IN (
  3      -- Fetch rowids of rows to delete
  4      SELECT rid FROM (
  5          SELECT rownum r, rid FROM (
  6              SELECT tab.rowid rid
  7                  FROM my_table tab
  8                  ORDER BY tab.created_date DESC
  9              )
 10          )
 11          -- Delete everything but the 100 nesest rows
 12          WHERE r > 100
 13  )
 14  -- Return newest date that was removed
 15  RETURNING max(tab_outer.created_date) INTO :latestDate;

400 rows deleted.

SQL> select :latestDate from dual;

:LATESTDATE
--------------------------------------------------------------------------------
06/08/14

Can you post a complete example and your database/client version.