René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

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 Oracle

This is an on Oracle article. The most current articles of this series can be found here.