Двойное выполнение функций в операторе вставки при использовании предложения возврата в Oracle DB

Question

Мы обнаружили ошибку в Oracle DB версии 21.3 (то же самое было проверено и в версии 19.3). Если вы используете функции внутри оператора вставки и возвращаете значения с помощью предложения return, результатом будет то, что функция, которую вы вызываете внутри метода вставки, выполняется ДВАЖДЫ !!!

Ниже приведен пример использования функции, которая создает следующее значение для последовательности. Я знаю, что вы можете использовать ".nextval" напрямую (и, как показано, работает нормально), но это всего лишь образец, демонстрирующий двойное выполнение функций. Я мог бы использовать любую другую функцию и был бы выполнен ДВАЖДЫ (например, что-то вычислял).

Вы можете проверить, как это работает, используя этот пример:

      create sequence seq_1;
create sequence seq_2;
create table test1000 (id number, a varchar2(4004));
create table test1001 (id number, a varchar2(1001));
create or replace function testfun return number is
  v_a number;
begin
  select seq_1.nextval into v_a from dual;
  dbms_output.put_line('FUNCTION RETURNS SEQ_1 VALUE: '||v_a);
  return v_a;
end;
/

Вы можете проверить это, если выполните следующее:

      declare
  v_a varchar2(100);
begin
  insert into test1000(id, a) values (seq_2.nextval, 'SEQ_1:'||testfun) returning a into v_a;
  dbms_output.put_line(v_a);
  insert into test1001(id, a) values (seq_2.nextval, 'SEQ_1:'||testfun) returning a into v_a;
  dbms_output.put_line(v_a);
end;
/

Он выведет:

      FUNCTION RETURNS SEQ_1 VALUE: 1
SEQ_1:1
FUNCTION RETURNS SEQ_1 VALUE: 2
FUNCTION RETURNS SEQ_1 VALUE: 3
SEQ_1:3

Вы можете увидеть двойное исполнение во второй вставке. Данные в таблице test1001 имеют значение 2, но значение, возвращаемое в переменной v_a, другое (значение 3). Двойное исполнение произошло не в первой вставке, а во второй. Я считаю, что это связано с тем, что во второй таблице есть поле «a» с varchar2 с максимальным размером выше 4000, и я считаю, что это имеет какое-то отношение к тому, как oracle обрабатывает расширенный тип varchar2 (MAX_STRING_SIZE расширен в нашей БД).

Я сообщил об этой ошибке в Oracle. Я обновлю это, когда получу от них ответ ...

Answer 1

Решение верхней выборки простое. Просто используйте ".nextval" напрямую. Если у вас есть какая-либо другая функция, решение состоит в том, что вы выполняете функцию (и сохраняете результат в переменной) перед использованием ее в операторе вставки следующим образом:

      declare
  v_a varchar2(100);
  v_seq_id number;
begin
  v_seq_id := testfun;
  insert into test1000(id, a) values (seq_2.nextval, 'SEQ_1:'||v_seq_id) returning a into v_a;
  dbms_output.put_line(v_a);
  v_seq_id := testfun;
  insert into test1001(id, a) values (seq_2.nextval, 'SEQ_1:'||v_seq_id) returning a into v_a;
  dbms_output.put_line(v_a);
end;
/

Результат будет правильным:

      FUNCTION RETURNS SEQ_1 VALUE: 1
SEQ_1:1
FUNCTION RETURNS SEQ_1 VALUE: 2
SEQ_1:2