Почему я не могу использовать переменную связывания в операторе немедленного выполнения?

Question

Я хотел бы использовать переменные связывания вместо конкатенации строк при создании динамического оператора SQL для execute immediate,

В приведенном ниже примере я могу использовать переменные связывания для a, b а также ret, но когда я пытаюсь связать для f Я получаю ORA-06502: PL/SQL: numeric or value error: character to number conversion error, Почему и как я могу связать также f?

Я использую 11.2.0.1.0.

create or replace function so4fun (
  a in number, 
  b in number,
  f in varchar2
) return number as
  decl constant varchar2(32767) := 
    'declare a constant number := :a; b constant number := :b;';
  stmt varchar2(32676);
  ret number;
begin
  /* This one works: */
  stmt := decl || ' begin :result := ' || f || '; end;';
  execute immediate stmt using in a, in b, out ret;

  /* But why this one doesn't ?

  stmt := decl || ' begin :result := :f; end;';
  execute immediate stmt using in a, in b, out ret, in f;

  This doesn't work either:

  stmt := decl || ' tmp number; begin tmp := :f; :result := tmp; end;';
  execute immediate stmt using in a, in b, in f, out ret;

  Both are giving me ORA-06502: PL/SQL: numeric or value error: character to
  number conversion error */

  return ret;
end;
/
show errors

/* expected result when a = 1, b = 2 is 1.5 */
select so4fun(1, 2, '(a + b) / b') from dual;

drop function so4fun;

Answer 1

Переменные связывания предназначены для связывания переменных, а не для связывания фрагментов кода. Идея состоит в том, что Oracle может компилировать и кэшировать запрос или блок кода и выполнять его несколько раз с различными параметрами.

Однако вы пытаетесь использовать привязку параметров для замены вычисленной формулы. Это предотвратит компиляцию и кэширование блока кода и поэтому не поддерживается.

Кроме того, это не может быть выражено текущим синтаксисом. Если Oracle видит tmp := :f он думает, что вы просто хотите назначить параметр f к переменной tmp, Не стоит оценивать функцию.

Просто иди с рабочим решением. Это работает в конце концов.

Answer 2

Ошибка возникает потому, что вы объявили f как varchar2 но вы написали в закомментированном разделе следующее:

tmp number; begin tmp := :f ...

в котором вы пытаетесь присвоить "символьное" значение переменной, которая ожидает число. Вы также пытались назначить f к результату функции, которая снова ожидает число.

Вещи работают хорошо с || потому что это конкатенация строк.

Вам как-то нужно конвертировать ваш varchar2 в число (TO_NUMBER(f)), или, возможно, напишите свой процесс, чтобы принять параметр f как число, а не varchar2.