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:
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"
It works for very very simple expressions - XQuery arithmetic expressions are very simple. They support +, -, *, div, idiv (integer division), and mod
See http://www.xquery.com/tutorials/guided-tour/xquery-operators.html for a guided tour of what you can do.
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.
posted by Thomas Kyte at 10:47 AM
9 Comments:
I learnt this from xml-sql-guru Laurent Schneider
http://laurentschneider.com/wordpress/2007/09/isnumber-in-sql.html
The xmlquery function can be used to solve a little math puzzle :)
http://oraqa.com/2008/05/30/how-to-solve-the-123456789-equation-puzzle-in-sql/
Thanks,
Frank
Hey guys, just so you know... XQUERY and XMLTABLE support the XQuery language since Oracle database version 10.2, although I would use it from version 10.2.0.3.0 when Oracle replaced the java solution with a C Kernel buildin engine.
So yes, you can create cool alternative solutions like a pivot table solution, see Laurent Schneiders old blogpost on this, from database version 10.2 and onwards...
@Marco
I know xquery was there - that was one of my top ten things about 10g.
The new thing is the fact that it doesn't need a literal, in 10g if you tried this you would get:
ops$tkyte%ORA10GR2> variable x varchar2(30)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :x := '5*2';
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select xmlquery(replace( :x, '/', ' div ' )
2 returning content ) .getNumberVal()
3 from dual
4 /
select xmlquery(replace( :x, '/', ' div ' )
*
ERROR at line 1:
ORA-19102: XQuery string literal expected
Which means it would be easier and a ton more functional just to select it from DUAL without xquery at all.
The neat thing, the magic here, is the expression being evaluated will not flood the shared pool with literals in 11g - not the evaluation so much.
Hmmm, Ahh, yep, thats true ;-)
Missed the real issue... :-) but indeed there has been some progression and it has become even better...
Hi Tom,
What about using dbms_aw.eval_number or dbms_aw.eval_text for evaluating expressions in pl/sql.
@Amit
indeed - that would work as well.
Thanks Tom,
dbms_aw is working fine but is it a good idea. This package is part of oracle OLAP - will it be available on any oracle installation?
Also given the following options which one do you think is the best:
1. execute immediate 'Select ' || 'expr' || ' from dual'
2. execute immediate 'Begin :1 := ' || 'expr' || '; END;' USING OUT var;
I don't see much diffrence in 1 and 2 - they both flood the shared pool.
3. select xmlquery(replace( :expr, '/', ' div ' ) returning content ) .getNumberVal() from dual
Though shared pool friendly - much slower than 1 and 2 (Compared with option 1 by evaluating some 100K expressions inside a loop)
4. dbms_aw.eval_text
Looks the best so far - but can it be made part of product
5. Write your own expression parser
Complex pl/sql
Thanks
Amit
@Amit -
http://download.oracle.com/docs/cd/E11882_01/license.112/e10594/options.htm#CIHGDEEF
As I understand it, the OLAP API is part of the OLAP option, an extra cost addition to the enterprise edition.
#1 and #2 I would just rule out, do not consider.
#3 is viable.
#4 would be an 'option' - excuse the pun...
#5 - is there something in java that exists out there - you might be able to code up a tiny java API and use it as a stored procedure.
POST A COMMENT
<< Home