if on 11 you could
Sokrates, April 16, 2010 - 1:49 am UTC
sql > select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
sql > create function
2 eval_simple_arithmetic_expr(vexpr in varchar2)
3 return number
4 is
5 evaluated number;
6 begin
7 select
8 xmlquery(
9 replace( vexpr, '/', ' div ')
10 returning content
11 ).getNumberVal()
12 into evaluated
13 from
14 dual;
15 return evaluated;
16 end eval_simple_arithmetic_expr;
17 /
Function created.
sql > select eval_simple_arithmetic_expr('5/100*300'), 5/100*300 from dual;
EVAL_SIMPLE_ARITHMETIC_EXPR('5/100*300') 5/100*300
---------------------------------------- ----------
15 15
sql > select eval_simple_arithmetic_expr('1*2*3+4+5*6+7'), 1*2*3+4+5*6+7 from dual;
EVAL_SIMPLE_ARITHMETIC_EXPR('1*2*3+4+5*6+7') 1*2*3+4+5*6+7
-------------------------------------------- -------------
47 47
sql > select eval_simple_arithmetic_expr('193 mod 47'), mod(193, 47) from dual;
EVAL_SIMPLE_ARITHMETIC_EXPR('193MOD47') MOD(193,47)
--------------------------------------- -----------
5 5
see
http://www.w3.org/TR/xquery/#id-arithmetic what one can do with this
April 16, 2010 - 10:04 am UTC
Thanks
Anoop, April 19, 2010 - 3:24 am UTC
Hi Tom,
Thanks for your inputs.
I tried using both execute immediate and dynamic sql using dbms_sql.parse.
Execute Immediate did not work on forms6i and dbms_sql.parse worked. So am using dbms_sql.parse for now.
Thanks again for your valuable inputs.