Работаем правильно с транзакциями.

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

Рассмотрим такой вариант.

DECLARE
a INT;
BEGIN
SELECT sum(x) INTO a FROM c;
DBMS_OUTPUT.PUT_LINE(a);
dbms_lock.sleep(10);
SELECT sum(x) INTO a FROM c;
DBMS_OUTPUT.PUT_LINE(a);
END;

То есть мы посчитали сумму по столбцу из тестовой таблички, выждали 10 сек. и снова повторили расчёт. Во время паузы, в другом сеансе, изменяем данные в тестовой таблице.

BEGIN
update c set x=x+1;
COMMIT;
END;

Получаем результат:

4
8

По умолчанию Oracle работает именно так. Oracle обеспечивает непротиворечивость данных по умолчанию только для выполнения одного sql запроса. То есть, если запрос выполняется 10 мин., то изменения в участвующих в запросе таблицах игнорируютя, но следующий запрос их уже увидит.

Для обеспечения непротиворечивости данных в пределах большой транзакции нужно включить режим serializable для этой транзакции.
Команда включения режима должна идти первой в транзакции !

commit;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
{ процедура }

COMMIT;

В этом случае мы получим результат

4
4

То есть мы 'заморозили' состояние базы для этой транзакции на момент её начала.

Теперь о ложке дёгтя...
Если мы в процедуре изменяем какую-то строку, а до нас её уже кто-то изменил в другой транзакции, то получаем ошибку

ORA-08177 can't serialize access for this transaction .

Если процедура оочень длинная, а изменения в базе идут очень часто, то к концу её выполнения в сегменте отката может не оказаться нужных нам блоков - они туда попали в результате изменения другим сеансом, были закоммичены и вытеснены другими транзакциями. В этом случае получаем ошибку

ORA-01555 snapshot too old: rollback segment number string with name "string" too small.

То есть лучше не затягивать до бесконечности тразакции и иметь сегменты отката адекватного размера.