8 Replies Latest reply: Jun 9, 2009 2:27 PM by Etbin RSS

    Evaluate Expression from PL/SQL without passing it SQL engine

    705443
      I have an expression similar to '1+2+(3*6)/12*ROUND(10*3, 3)' which I generate programatically in VARCHAR2 variable. One way to calculate the expression is execute immediate 'select '||exp||' from dual' into lv_x; which would return the value. But my shared pool is filling up as I do this thousands of times may more in some cases. I unsuccessfully tried to use bind variables.
      1. Is there a way to temporarily turn shared pool off before the query an enable it after executing the query?
      2. Is there a simple function in PL/SQL which evaluvates the expression? If not I may have to painfully write one.
      Please advice
        • 1. Re: Evaluate Expression from PL/SQL without passing it SQL engine
          damorgan
          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.
          • 2. Re: Evaluate Expression from PL/SQL without passing it SQL engine
            705443
            Our version is 10.1.0.4.2
            I could not use a bind varaible there, can I? Could you please hint me there?
            • 3. Re: Evaluate Expression from PL/SQL without passing it SQL engine
              MichaelS
              I could not use a bind varaible there, can I?
              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.
              • 4. Re: Evaluate Expression from PL/SQL without passing it SQL engine
                John Spencer
                "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
                • 5. Re: Evaluate Expression from PL/SQL without passing it SQL engine
                  BrianCamire
                  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.
                  • 6. Re: Evaluate Expression from PL/SQL without passing it SQL engine
                    Etbin
                    could this work for you as the first step (no functions)?
                    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;
                      w_expression varchar2(4000) := replace(p_expression,' ','');
                      i pls_integer := 1;
                      next_char varchar2(1);
                      num_in boolean := false;
                      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||'''');
                        return ret_val;
                      end;
                    
                      procedure push_stack(p_stack in out varchar2,p_item in varchar2) is
                      begin
                        p_stack := p_stack || p_item || 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;
                        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(p_left,p_right);
                        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);
                        dbms_output.put_line('evaluate operation: ('||left_operand||')'||top_operator||'('||right_operand||') = '||to_char(result));
                                push_stack(operand_stack,to_char(result));
                              end if;
                            end if;
                          end if;
                        end loop;
                      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(w_expression,'(-','(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);
                        end if;
                        i := i + 1;
                      end loop;
                      if num_in then
                        push_stack(operand_stack,'');
                      end if;
                      while not is_empty(operator_stack) 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);
                        dbms_output.put_line('evaluate operation: ('||left_operand||')'||top_operator||'('||right_operand||') = '||to_char(ret_val));
                        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(w_expression||' = '||to_char(ret_val));
                      if is_empty(operand_stack) and is_empty(operator_stack) then
                        return ret_val;
                      else 
                        return to_number(null);
                      end if;
                    end;
                    Regards

                    Etbin

                    In memory of "old days" when there was only FORTRAN at the university (no Pascal yet).

                    Edited by: Etbin on 8.6.2009 16:48
                    • 7. Re: Evaluate Expression from PL/SQL without passing it SQL engine
                      705443
                      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
                      • 8. Re: Evaluate Expression from PL/SQL without passing it SQL engine
                        Etbin
                        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