Oracle savepoints. Динамическое создание

В предыдущей заметке я писал о проблеме, с которой может столкнуться программист при использовании явных точек сохранения (savepoint). Сегодня я приведу конкретный пример, как избежать эту проблему.

Решение очень простое: pl/sql не запрещает создавать точки сохранения динамически. Вот пример:

declare
  spnt varchar2(32);
begin
  spnt := 'savepoint_name';
  execute immediate 'savepoint '||spnt;
  begin
    .. do something ..
  exception
    when others then
      execute immediate 'rollback to '||spnt;
  end;
end;

Вместо execute immediate можно использовать такой вариант:

declare
  spnt varchar2(32);
begin
  spnt := 'savepoint_name';
  dbms_transaction.savepoint(spnt);
  begin
    .. do something ..
  exception
    when others then
      dbms_transaction.rollback_savepoint(spnt);
  end;
end;

Замечу, что dbms_transaction внутри себя модифицирует имя точки сохранения, т.е. нельзя в одном блоке перемешивать оба варианта. Пример:

begin
  dbms_transaction.savepoint('savepoint_name');
  ..
  rollback to savepoint_name;
end;

вызовет ошибку: ORA-01086: savepoint 'SAVEPOINT_NAME' never established

Более того, dbms_transaction позволяет передавать числа, в качестве имени точки (видимо к имени добавляется некий префикс). Следующий пример будет корректен:

begin
  dbms_transaction.savepoint(1);
  ..
  dbms_transaction.rollback_savepoint(1);
end;

Это позволяет использовать, например, последовательности (sequence) для динамического создания точек сохранения. Например так:

--create sequence savepoints_seq;
declare
  spnt number;
begin
  select savepoints_seq.nextval into spnt from dual;
  dbms_transaction.savepoint(spnt);
  ..
  dbms_transaction.rollback_savepoint(spnt);
end;
--drop sequence savepoints_seq;

Однако последовательности существуют вне транзакций, а точки сохранения действуют только в пределах транзакции. Поэтому имеет смысл написать свой пакет, в котором использовать простую целочисленную переменную, и наращивать её при обращении к ней.