René Nyffenegger's collection of things on the web  
René Nyffenegger on Oracle  Most wanted  Feedback
 Follow @renenyffenegger

May 5, 2005: On EVAL'ing an expression  
Some programming languages (such as perl or javascript) have an eval function which executes a string that contains some code in its
own language. The PL/SQL equivalent would be execute immediate. The execute immediate
statement is an all purpose statement and executes any valid PL/SQL block. If only one statement with a return value should be evaluated, the
following eval function comes in handy. It basically wrappes an execute immediate statement:
create or replace function eval (expr varchar2) return varchar2 as ret varchar2(4000); begin execute immediate 'begin :result := '  expr  '; end;' using out ret; return ret; end; /
In order to demonstrate the eval function, I create two tables, operators and operands.
create table operators ( op_with_format varchar2(20) ); create table operands ( op_1 varchar2(5), op_2 varchar2(5) );
The operators table is filled with some mathematical operators (mod, * and +) that take two operands (or arguments). The
position of the operands is dependant on the operator, so I also need to store the format along with the operator (field
op_with_format.
The operands table is filled with the values that are going to be replaced for %op_1% and %op_2%.
insert into operators values ('%op_1% * %op_2% '); insert into operators values ('%op_1% + %op_2% '); insert into operators values ('mod(%op_1%, %op_2%)'); insert into operands values ('42', '11'); insert into operands values ('7' , '5'); insert into operands values ('123' ,'18');
Then, I cross join both tables, that is each row of one row is joined with each row of the other table. eval is executed on
all possible combinations of operands and operators:
column eval format a20 column expr format a20 select expr, eval(expr) eval from ( select replace( replace(op_with_format, '%op_1%', op_1), '%op_2%', op_2) expr from operands cross join operators ); EXPR EVAL   42 * 11 462 42 + 11 53 mod(42, 11) 9 7 * 5 35 7 + 5 12 mod(7, 5) 2 123 * 18 2214 123 + 18 141 mod(123, 18) 15 More on OracleThis is an on Oracle article. The most current articles of this series can be found here.
