На днях коллеги из соседнего отдела столкнулись с проблемой. Иногда один и тот же вызов процедуры на 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(), но это тоже может впоследствии привести — нет, не к ошибке, — к неверной работе реального кода.
Новости