Is it possible to evaluate expression in stored procedure?

Question

I have a two table like

 
tbl1
+-------+---------+ 
| id    | number  | 
+-------+---------+ 
| a001  |  001    | 
| a002  |  002    |
| a003  |  003    |
| a004  |  004    |
| a005  |  002    |
+-------+---------+

tbl2
+----+-----------------------------------+------------+ 
| id | Extression                        | True/False | 
+----+-----------------------------------+------------+
| 01 | `a003=a001+ a002`                 |            |
| 02 | `a004=a001+ a002`                 |            |
| 03 | `a004 + a005 =a001 + a002 + a003` |            |
+----+-----------------------------------+------------+

I want to update True/False column based on result of expression.In this case 01 is true,02 is false and 03 is true. I have done this in c# code. But due to limitation of runnig exe through job I want to do this by procedure in oracle. Is this possible ??

Answer 1

you could put all the lookups into an array and then run a pl/sql block over that.

SQL> create package pkg_eval
  2  as
  3    function eval_expr(p_str in tbl2.expression%type)
  4      return varchar2;
  5  end pkg_eval;
  6  /

Package created.

SQL> create package body pkg_eval
  2  as
  3    --  Type to hold lookup values
  4    type lookup_tab is table of number index  by varchar2(4000);
  5    t_lookup lookup_tab;
  6
  7    function eval_expr(p_str in tbl2.expression%type)
  8      return varchar2
  9    is
 10      v_str varchar2(32767) := p_str;
 11      v_idx tbl1.id%type;
 12      v_res varchar2(5);
 13    begin
 14
 15      -- iterate through each lookup and replace it in the input expression
 16      v_idx := t_lookup.FIRST;
 17      while v_idx is not null
 18      loop
 19        v_str := replace(v_str, v_idx, t_lookup(v_idx));
 20        v_idx := t_lookup.NEXT(v_idx);
 21      end loop;
 22      -- Run the expression.
 23      execute immediate '
 24      declare
 25        v_res varchar2(5);
 26      begin
 27        if ((' || v_str || '))
 28        then
 29          v_res := ''true'';
 30        else
 31          v_res := ''false'';
 32      end if;
 33        :a := v_res;
 34      end;' using out v_res;
 35
 36      return v_res;
 37    end eval_expr;
 38
 39  -- Populate lookup values, once per session.
 40  begin
 41    for r_row in (select id, val
 42                     from tbl1)
 43    loop
 44      t_lookup(r_row.id) := r_row.val;
 45    end loop;
 46  end pkg_eval;
 47  /

Package body created.

SQL> col result  format a10
SQL> select t.*, pkg_eval.eval_expr(t.expression) result from tbl2 t;

        ID EXPRESSION                      RESULT
---------- ------------------------------- ----------
         1 a003=a001+ a002                 true
         2 a004=a001+ a002                 false
         3 a004 + a005 =a001 + a002 + a003 true

p.s. for error handling, you may want to deal with corrupt expressions etc like

    return v_res;
  exception
    when others
    then
      return 'unknown';
  end eval_expr;

Answer 2

Yes, you can do that. It looks like you'll need to write a procedure to parse and evaluate your expression, but assuming you've done so and called the procedure EVALUATOR_PROC your insert would look something like

INSERT INTO TBL2 (ID,   EXTRESSION,        TRUE_FALSE)
           VALUES('01', 'a003=a001+ a002', EVALUATOR_PROC('a003=a001+ a002'))

Share and enjoy.