SQL & PL/SQL

And your version number is?

"I tried unsuccessfully to use bind variables" without providing us the code you used is a major issue as it is the correct solution to the problem.

Reconsider what your posted ... posting you non-working code and explaining why it does not work ... it the best next step.

Our version is 10.1.0.4.2
I could not use a bind varaible there, can I? Could you please hint me there?

No you can't, but you can avoid the SQL engine:

SQL> declare
 str varchar2(100) := '1+2+(3*6)/12*ROUND(10*3, 3)';
 res number;
begin 
  execute immediate 'begin :out := ' || str || '; end;' using out res;
  dbms_output.put_line('Result: ' || res);
end;
/
Result: 48
PL/SQL procedure successfully completed.

"No you can't, but you can avoid the SQL engine:"

But not the sql_area :-)

dev2 > declare
  2   str varchar2(100) := '1+2+(3*6)/12*ROUND(10*3, 3)';
  3   res number;
  4  begin
  5    execute immediate 'begin :out := ' || str || '; end;' using out res;
  6    dbms_output.put_line('Result: ' || res);
  7  end;
  8  /
Result: 48
 
PL/SQL procedure successfully completed.
 
dev2 > SELECT sql_id, sql_text from v$sqlarea
  2  where sql_text like '%:out%';
 
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
dfzbx35xv8p27
SELECT sql_id, sql_text from v$sqlarea where sql_text like '%:out%'
 
5j2askcqn4ras
begin :out := 1+2+(3*6)/12*ROUND(10*3, 3); end;
 
5xjn0fr4s33br
declare  str varchar2(100) := '1+2+(3*6)/12*ROUND(10*3, 3)';  res number; begin   execute immediate 'begin :ou
t := ' || str || '; end;' using out res;   dbms_output.put_line('Result: ' || res); end;

John

You can use bind variables for the parameters of your expression, as in something like:

SQL> DECLARE
  2      b1 NUMBER := 1;
  3      b2 NUMBER := 2;
  4      b3 NUMBER := 3;
  5      b4 NUMBER := 6;
  6      b5 NUMBER := 12;
  7      b6 NUMBER := 10;
  8      b7 NUMBER := 3;
  9      b8 NUMBER := 3;
 10      result NUMBER;
 11  BEGIN
 12      EXECUTE IMMEDIATE
 13          'SELECT :b1+:b2+(:b3*:b4)/:b5*ROUND(:b6*:b7,:b8) FROM DUAL'
 14      INTO
 15          result
 16      USING
 17          b1, b2, b3, b4, b5, b6, b7, b8;
 18      DBMS_OUTPUT.PUT_LINE(result);
 19  END;
 20  /
48

PL/SQL procedure successfully completed.

This should help reduce shared pool usage if you repeatedly evaluate the same form of expression using different parameters.

In addition to, "How do I keep from filling up the shared pool," there are some other important questions you should be asking yourself (if you haven't already) before continuing down this path, like:

1. Have you adequately protected against SQL injection vulnerabilities? This is hard to do properly.

2. Have you made the appropriate choice of invoker's rights vs. definer's rights?

3. Do you really, really need to be able to evaulate any arbitrary expression, or, is there in practice only a small, finite set of forms of expressions that you need evaluate? If the latter, you might be better off using an approach that does not use dynamic SQL, and so avoids the performance and security issues that go along with it.

Hope this helps.

You guys are great, thanks for the code/function.

I have learnt about a function called eval in Java/C, so I have created java stored function to return the evaluated value.
I will keep this thread open for a while for more inputs.

Thanks!

Edited by: Xram on Jun 8, 2009 10:04 AM

few additional coffee breaks to put together a functions version (not heavily tested)

create or replace function evaluate_expression(p_expression in varchar2) return number is
  ret_val number;
  sep varchar2(1) := '|';
  operand_stack varchar2(4000) := sep;
  operator_stack varchar2(4000) := sep;
  function_stack varchar2(4000) := sep;
  w_expression varchar2(4000) := replace(p_expression,' ','');
  i pls_integer := 1;
  next_char varchar2(1);
  num_in boolean := false;
  opr_in boolean := false;
  fun_in boolean := false;
  top_function varchar2(100);
  top_operator varchar2(100);
  left_operand varchar2(100);
  right_operand varchar2(100);
  
  function pop_stack(p_stack in out varchar2) return varchar2 is
    ret_val varchar2(4000);
  begin
    ret_val := rtrim(substr(p_stack,instr(p_stack,sep,-1,2) + 1),sep);
    p_stack := substr(p_stack,1,instr(p_stack,ret_val||sep,-1,1) - 1);
dbms_output.put_line('pop stack: '''||ret_val||''' = '||substr(p_stack,1,100));
    return ret_val;
  end;

  function peek_stack(p_stack in varchar2) return varchar2 is
    ret_val varchar2(4000);
  begin
    ret_val := substr(rtrim(p_stack,sep),instr(rtrim(p_stack,sep),sep,-1) + 1);
dbms_output.put_line('peek stack = '''||ret_val||''' '||substr(p_stack,1,100));
    return ret_val;
  end;

  procedure push_stack(p_stack in out varchar2,p_item in varchar2) is
  begin
    p_stack := replace(p_stack || p_item || sep,sep || sep,sep);
dbms_output.put_line('push stack: '''||p_item||''' = '||substr(p_stack,1,100));
  end;

  function is_empty(p_stack in varchar2) return boolean is
    ret_val boolean;
  begin
    if p_stack = sep then
      ret_val := true;
    else
      ret_val := false;
    end if;
    return ret_val;
  end;

  function precedence(p_operator in varchar2) return pls_integer is
    ret_val pls_integer;
  begin
    if p_operator in ('+','-') then 
      ret_val := 1;
    elsif p_operator in ('*','/') then 
      ret_val := 2;
    elsif p_operator in ('^') then 
      ret_val := 3;
    elsif p_operator in ('(') then 
      ret_val := 0;
    elsif p_operator in (')') then 
      ret_val := 0;
    elsif p_operator in ('F') then
      ret_val := -1;
    else
      ret_val := 4;
    end if;
    return ret_val;
  end;

  function evaluate_operation(p_left in varchar2,p_right in varchar2,p_operator in varchar2) return number is
    ret_val number := to_number(null);
  begin
    if p_operator = '+' then
      ret_val := to_number(p_left) + to_number(p_right);
    elsif p_operator = '-' then
      ret_val := to_number(p_left) - to_number(p_right);
    elsif p_operator = '*' then
      ret_val := to_number(p_left) * to_number(p_right);
    elsif p_operator = '/' then
      ret_val := to_number(p_left) / to_number(p_right);
    elsif p_operator = '^' then
      ret_val := power(to_number(p_left),to_number(p_right));
    end if;
dbms_output.put_line('evaluate operation: ('||p_left||')'||p_operator||'('||p_right||') = '||to_char(ret_val));
    return ret_val;
  end;

  function evaluate_function(p_function in varchar2) return number is
    ret_val number := to_number(null);
  begin
    if p_function = 'ROUND' then
      right_operand := pop_stack(operand_stack);
      left_operand := pop_stack(operand_stack);
      ret_val := round(to_number(left_operand),to_number(right_operand));
dbms_output.put_line('evaluate function: round['||left_operand||';'||right_operand||']');
    elsif p_function = 'TRUNC' then
      right_operand := pop_stack(operand_stack);
      left_operand := pop_stack(operand_stack);
      ret_val := trunc(to_number(left_operand),to_number(right_operand));
dbms_output.put_line('evaluate function: trunc['||left_operand||';'||right_operand||']');
    end if;
    return ret_val;
  end;

  procedure treat_operand(p_char in varchar2) is
  begin
    operand_stack := operand_stack || p_char;
  end;

  procedure treat_operator(p_char in varchar2) is
    done boolean := false;
    result number;
  begin
    if num_in then
      push_stack(operand_stack,'');
      num_in := false;
    end if;
    while not done loop
      if is_empty(operator_stack) or p_char = '(' then
        push_stack(operator_stack,p_char);
        done := true;
      else
        top_operator := peek_stack(operator_stack);
        if precedence(p_char) > precedence(top_operator) then
          push_stack(operator_stack,p_char);
          done := true;
        else
          top_operator := pop_stack(operator_stack);
          if top_operator = '(' then
            done := true;
          else 
            right_operand := pop_stack(operand_stack);
            left_operand := pop_stack(operand_stack);
            result := evaluate_operation(left_operand,right_operand,top_operator);
            push_stack(operand_stack,to_char(result));
          end if;
        end if;
      end if;
    end loop;
  end;

  procedure treat_function(p_char in varchar2) is
    result number;
  begin
    if p_char = '[' then  -- function parameters in square brackets
      push_stack(function_stack,'');
      push_stack(operator_stack,'F');
    elsif p_char = ';' then  -- ';' function parameter separator (my decimal point is comma)
      push_stack(operand_stack,'');
      while peek_stack(operator_stack) != 'F' loop
        top_operator := pop_stack(operator_stack);
        right_operand := pop_stack(operand_stack);
        left_operand := pop_stack(operand_stack);
        ret_val := evaluate_operation(left_operand,right_operand,top_operator);
        push_stack(operand_stack,to_char(ret_val));
      end loop;
    elsif p_char = ']' then  -- function parameters in square brackets
      push_stack(operand_stack,'');
      while peek_stack(operator_stack) != 'F' loop
        top_operator := pop_stack(operator_stack);
        right_operand := pop_stack(operand_stack);
        left_operand := pop_stack(operand_stack);
        ret_val := evaluate_operation(left_operand,right_operand,top_operator);
        push_stack(operand_stack,to_char(ret_val));
      end loop;
      top_operator := pop_stack(operator_stack);
      top_function := pop_stack(function_stack);
      ret_val := evaluate_function(upper(top_function));
      push_stack(operand_stack,to_char(ret_val));
      while peek_stack(operator_stack) not in ('F','(') loop
        top_operator := pop_stack(operator_stack);
        right_operand := pop_stack(operand_stack);
        left_operand := pop_stack(operand_stack);
        ret_val := evaluate_operation(left_operand,right_operand,top_operator);
        push_stack(operand_stack,to_char(ret_val));
      end loop;
    else
      function_stack := function_stack || p_char;
    end if;
  end;
  
begin
  if substr(w_expression,1,1) = '-' then
    w_expression := '0' || w_expression;
  elsif substr(w_expression,1,1) = '+' then
    w_expression := substr(w_expression,2);
  end if;
  w_expression := replace(replace(replace(w_expression,'(-','(0-'),'(+','('),'[-','[0-');
  while true loop
    next_char := substr(w_expression,i,1);
dbms_output.put_line('loop next_char('||to_char(i)||') = '''||next_char||'''');
    exit when next_char is null;
    if next_char in ('0','1','2','3','4','5','6','7','8','9',',') then -- ',' = decimal point
      treat_operand(next_char);
      num_in := true;
    elsif next_char in ('+','-','*','/','^','(',')') then
      treat_operator(next_char);
      if not fun_in then
        opr_in := true;
      end if;
    else
      treat_function(next_char);
      fun_in := true;
    end if;
    i := i + 1;
  end loop;
  push_stack(operand_stack,'');
  while not is_empty(operator_stack) loop
    top_operator := pop_stack(operator_stack);
    if top_operator = 'F' then
      top_function := pop_stack(function_stack);
      ret_val := evaluate_function(upper(top_function));
    else
      if top_operator != '(' then
        right_operand := pop_stack(operand_stack);
        left_operand := pop_stack(operand_stack);
        ret_val := evaluate_operation(left_operand,right_operand,top_operator);
      end if;
    end if;
    push_stack(operand_stack,to_char(ret_val));
  end loop;
  left_operand := pop_stack(operand_stack);
  ret_val := to_number(left_operand);
dbms_output.put_line('operand_stack = '||substr(operand_stack,1,100));
dbms_output.put_line('operator_stack = '||substr(operator_stack,1,100));
dbms_output.put_line('function_stack = '||substr(function_stack,1,100));
dbms_output.put_line(w_expression||' = '||to_char(ret_val));
  if is_empty(operand_stack) and is_empty(operator_stack) and is_empty(function_stack) then
    return ret_val;
  else 
    return to_number(null);
  end if;
end;

Regards

Etbin

some comments added
to keep it simple:
- stacks are just strings (elements separated by pipes) on developing everything is revealed at first glance
- function parameters are specified in square brackets separated by semicolons (comma is our decimal point)
- dbms_output.put_line intentionally kept in place as on developing provided sufficient trace info

Edited by: Etbin on 9.6.2009 21:07