На днях коллеги из соседнего отдела столкнулись с проблемой. Иногда один и тот же вызов процедуры на PL/SQL с одними и теми же параметрами давал различные результаты. Причем, если в проблемном блоке, извлекающем одно значение из выборки в переменную, поставить пробел перед точкой с запятой, ошибки нет. Если убрать пробел, все хорошо. Но чудес не бывает. Выяснилось, что практически во всем пакете запросы, возвращающие одну строку в переменные, были обрамлены в блоки обработки исключений следующим образом:

BEGIN
   SELECT VAL
     INTO v_val
     FROM TBL
    WHERE condition ; -- мистический пробел!
EXCEPTION
   WHEN no_data_found OR too_many_rows
   THEN 
      NULL;
END;

И вот когда случалось, что точная выборка возвращает больше одной строки, ошибка гасилась на извлечении второй строки, а в переменную v_val уже успевало попасть значение из первой строки выборки. Установка или удаление мистического пробела меняла план запроса, а может, сам Oracle что-то предпринимал, одно ему, оракулу, ведомое, и в переменную в разных случаях попадало разное значение с последующей неправильной работой всей процедуры.

Посмотрите, как работает пример ниже:

declare
   v_1 NUMBER := 1;
begin
   dbms_output.put_line(v_1);
   begin
      SELECT 2
        INTO v_1
        FROM DUAL
       WHERE 1 = 0;
   exception
      when no_data_found or too_many_rows then
         null;
   end;
   dbms_output.put_line(v_1);
   dbms_random.seed(TO_CHAR(SYSTIMESTAMP, 'DD.MM.YYYY HH24:MI:SS.FF9'));
   begin
      SELECT Q.L
        INTO v_1
        FROM (SELECT LEVEL + 2 AS L, DBMS_RANDOM.VALUE(1, 100) O
                FROM DUAL
             CONNECT BY LEVEL <= 4) Q
       ORDER BY O;
   exception
      when no_data_found or too_many_rows then
         null;
   end;
   dbms_output.put_line(v_1);
end;

Вместо разных планов запроса здесь выступает сортировка по случайному значению. Прогнав данный скрипт, вы увидите, что при ошибке too_many_rows почти каждый раз меняется значение, попадающее в переменную v_1.

Рекомендация в данном случае – в обработке ошибок исключение каждого типа проверять отдельно и в каждом из вариантов инициализировать переменную нужным для дальнейшей корректной работы значением! Может появиться соблазн агрегировать результат при помощи MAX() или MIN(), но это тоже может впоследствии привести – нет, не к ошибке, – к неверной работе реального кода.