|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
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 Oracle
This is an on Oracle article. The most current articles of this series can be found here.