Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Execute some basic math in a single SQL
Hi,
> 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?
you may use execute immediate (e.g. in a function):
begin
declare
num number := 2;
variable_name varchar2(100) := 'X';
formula varchar2(2000) := 'X + X';
res number;
sql_text varchar2(2000);
begin
sql_text :=
'select '||replace( formula , variable_name,num) ||' from dual';
dbms_output.put_line ('sql = '|| sql_text);
execute immediate sql_text INTO res;
dbms_output.put_line ('X = '|| res);
end;
end;
sql = select 2 + 2 from dual
X = 4
Jaromir
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 27 2007 - 07:35:00 CDT