Why I can't use a bind variable in an execute immediate statement?

I'd like to use bind variables instead of a string concatenation when I build up a dynamic SQL statement for execut开发者_Go百科e immediate.

In the example below I can use bind variables for a, b and ret, but when I try to bind for f I get a ORA-06502: PL/SQL: numeric or value error: character to number conversion error. Why and how I can bind also f ?

I'm using 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;

---

Bind variables are for binding variables, not for binding pieces of code. The idea is that Oracle can compile and cache a query or block of code and execute it several times with different parameters.

However, you try to use parameter binding for replacing the computed formula. It would prevent compiling and caching the block of code and is thus not supported.

Furthermore, it cannot be expressed with the current syntax. If Oracle sees tmp := :f it thinks that you simple want to assign the parameter f to the variable tmp. It doesn't expect to have to evaluate a function.

Just go with the working solution. It works after all.

---

The error occurs because you declared f as a varchar2 but you have written in your commented-out section, the following:

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

in which you try to assign the "character" value to a variable that expects a number. You also tried to assign f to the function result which again expects a number.

Things work fine with || because that is string concatenation.

You somehow need to convert your varchar2 to a number (TO_NUMBER(f)), or perhaps, write your proc to accept the parameter f as a number rather than varchar2.