1

I'd like to use bind variables instead of a string concatenation when I build up a dynamic SQL statement for execute 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;
user272735
  • 10,473
  • 9
  • 65
  • 96
  • I selected Codo's answer because I see his answer more comprehensive and compact. However I also found Ray Toal's answer as useful and I could conclude the answer to my problem from his answer too. I hope many upvotes to both of them ! – user272735 Aug 02 '11 at 07:08

2 Answers2

2

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.

Codo
  • 75,595
  • 17
  • 168
  • 206
1

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.

Ray Toal
  • 86,166
  • 18
  • 182
  • 232
  • So Oracle is doing me a favor and it checks that a bind variable is a right kind of type ? In the case of `f` my intention is to generate a statement like `begin :result := (a + b) / b; end;` but it fails because `:result` is tied to a number and `(a + b) / b` is a varchar2 when binding takes place ? – user272735 Aug 02 '11 at 05:53
  • 2
    Ohhhhh you want `f` to be the varchar2 `"(a + b) / b"` ? That is, `f` is the text of an string which you want to evaluate? In that case binding is not appropriate and you should go with string concatenation. Or see if anything here is helpful: http://asktom.oracle.com/pls/asktom/f?p=100:11:4121852961219206::::P11_QUESTION_ID:1179235402134 – Ray Toal Aug 02 '11 at 05:56