Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Execute some basic math in a single SQL
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';
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
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_at_constellation.com> 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" -- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 27 2007 - 13:45:10 CDT