Re: Execute some basic math in a single SQL

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Tue, 27 Mar 2007 20:45:10 +0200
Message-ID: <4ef2fbf50703271145n481e327ev774d5bb123a2c283@mail.gmail.com>

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_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-l

Received on Tue Mar 27 2007 - 13:45:10 CDT