Evaluating an expression, like a calculator...

Today, I learned a new 11g Release 1 and above 'trick' that I wasn't aware of. This is pretty cool.

A frequently asked question in the past has been:

I have a string, with some calculation in it - like "1+2/3". I would like to evaluate that string and get the result. How do I do that.

Historically - the answer has been "dynamic SQL, but please be careful to not flood the shared pool with tons of literal SQL and be really careful about SQL Injection!"

Now, I learned a new way. It came from this post, thanks to the frequent asktom poster "Sokrates"

ops$tkyte%ORA11GR1> select * from v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

ops$tkyte%ORA11GR1> variable x varchar2(40)
ops$tkyte%ORA11GR1> exec :x := '55+42*123/3'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select xmlquery(replace( :x, '/', ' div ' ) 
                            returning content ) .getNumberVal()
2    from dual
3  /

XMLQUERY(REPLACE(:X,'/','DIV')RETURNINGCONTENT).GETNUMBERVAL()
--------------------------------------------------------------
                                                      1777

It works for very very simple expressions - XQuery arithmetic expressions are very simple. They support +, -, *, div, idiv (integer division), and mod

ops$tkyte%ORA11GR1> exec :x := '(55+42-124) idiv 3 div 2 mod 5*2'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> /

XMLQUERY(REPLACE(:X,'/','DIV')RETURNINGCONTENT).GETNUMBERVAL()
--------------------------------------------------------------
                                                         -9

See http://www.xquery.com/tutorials/guided-tour/xquery-operators.html for a guided tour of what you can do.