Re: Execute some basic math in a single SQL
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: Mark.Brady@xxxxxxxxxxxxxxxxx
- Date: Tue, 27 Mar 2007 20:45:10 +0200
I'do go with something like
create or replace function eval (expr varchar2)
return number
deterministic
is
l_result number;
begin
execute immediate 'alter session set cursor_sharing=force';
-- execute immediate 'begin :result := '||expr||'; end;' using out l_result;
execute immediate 'select '||expr||' from dual eval_expr' into l_result;
execute immediate 'alter session set cursor_sharing=exact';
return l_result;
end;
/
create table formulae (x number, formula varchar2(30));
insert into formulae (x, formula) values (10, 'X + X');
insert into formulae (x, formula) values (10, 'power (X, 2) - X');
SQL> select x, formula, eval ( replace (formula, 'X', x) ) result from formulae;
X FORMULA RESULT
---------- -------------------- --------
10 X + X 20.00
10 power (X, 2) - X 90.00
The two 'alter session set cursor_sharing=..." are there to make
eval() library cache friendly - you'll have a single (hard) parse
for every formula and you won't pollute the library cache:
SQL> select sql_text from v$sql where sql_text like '%eval_expr';
SQL_TEXT
-----------------------------------------------------------------------
select :"SYS_B_0" + :"SYS_B_1" from dual eval_expr
select power (:"SYS_B_0", :"SYS_B_1") - :"SYS_B_2" from dual eval_expr
The commented-out variation using an anonymous block is a
possible alternative to avoid reading dual, which would give a
negligible improvement and which is useless in 10g anyway, since
of course there you won't read dual at all - FAST DUAL:
select :"SYS_B_0" + :"SYS_B_1" from dual eval_expr
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)| |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
You can't, of course, avoid dynamic SQL, since the SQL
compiler is a static compiler, so it can't "interpret" dynamic
components such as the ones in table FORMULAE.
HTH
Alberto
On 3/26/07, Brady, Mark <Mark.Brady@xxxxxxxxxxxxxxxxx> wrote:
Without a UDF….
I have data that is a number and a formula – one row would look like this:
select 2 num, 'X + X' formula from dual
The formula has 1 variable only but it can be repeated more than once.
with data_looks_like_this as (select 2 num, 'X + X' formula from dual)
select num, formula, replace(formula, 'X', num) f from data_looks_like_this
Column f now has 2 + 2
Can I possibly get a 4?
select 2 + 2 from dual
does result in 4.
It seems you should be able to say treat column f not as a char or a number
but as if you saw only what is there.
>>> This e-mail and any attachments are confidential, may contain legal,
professional or other privileged information, and are intended solely for
the addressee. If you are not the intended recipient, do not use the
information in this e-mail in any way, delete this e-mail and notify the
sender. CEG-IP1
--
Alberto Dell'Era
"dulce bellum inexpertis"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
