ORA-01422: точная выборка возвращает больше, чем запрошенное количество строк в RETURNING INTO

Question

У меня есть следующий sql (оракул), который удаляет все строки из таблицы, кроме 100 самых новых.

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

Этот код иногда дает ORA-01422: exact fetch returns more than requested number of rows утверждая, что в последней дате было добавлено более одной строки. Как это возможно? Агрегирующая функция (max) в предложении RETURNING INTO должна гарантировать, что возвращается только одна строка, нет? Может ли это иметь какое-либо отношение к явному использованию или rowid (я не понимаю, как)?

Answer 1

Я думал, что было невозможно использовать агрегаты в предложении возврата, так как я никогда не пробовал это, и это не упоминается в документации, но на самом деле это работает (11gr2)!!

Смотрите ниже в 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

И даже в SQL*Plus (клиент 10.2.0.1.0, база данных 11.2.0.3.0):

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

Можете ли вы опубликовать полный пример и вашу версию базы данных / клиента.