Friday, April 16, 2010

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;

Oracle Database 11g Enterprise Edition Release - Production
PL/SQL Release - Production
CORE Production
TNS for Linux: Version - Production
NLSRTL Version - 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> select xmlquery(replace( :x, '/', ' div ' )
returning content ) .getNumberVal()
2 from dual
3 /


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> /


See for a guided tour of what you can do.


Anonymous Sokrates said....

I learnt this from xml-sql-guru Laurent Schneider

Fri Apr 16, 11:49:00 AM EDT  

Anonymous Frank Zhou said....

The xmlquery function can be used to solve a little math puzzle :)



Fri Apr 16, 01:55:00 PM EDT  

Blogger Marco Gralike said....

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 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...

Sun Apr 18, 04:20:00 AM EDT  

Blogger Thomas Kyte said....


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> exec :x := '5*2';

PL/SQL procedure successfully completed.

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.

Sun Apr 18, 09:36:00 AM EDT  

Blogger Marco Gralike said....

Hmmm, Ahh, yep, thats true ;-)
Missed the real issue... :-) but indeed there has been some progression and it has become even better...

Thu Apr 22, 01:47:00 PM EDT  

Blogger Amit said....

Hi Tom,

What about using dbms_aw.eval_number or dbms_aw.eval_text for evaluating expressions in pl/sql.

Mon Mar 21, 04:02:00 AM EDT  

Blogger Thomas Kyte said....


indeed - that would work as well.

Mon Mar 21, 07:24:00 AM EDT  

Blogger Amit said....

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


Mon Mar 21, 07:45:00 AM EDT  

Blogger Thomas Kyte said....

@Amit -

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.

Mon Mar 21, 09:47:00 AM EDT  

Blogger Wryly said....

Hi Tom/Amit (still around?),
I have the same problem, with a shared pool flooded by statements generated by a loop reading operands and operators from a table.
Thousands of expressions like '3243*654/321+876-32*3' or '543+876*2'.

There can be any number of operands and operators in any order, so I can't see a way to avoid exec immed with bind variables.

Like Amit said, a PL/SQL parser turns out to be much slower than dynamic SQL. We're on Oracle 9.2 with no OLAP.

Tue Mar 12, 10:11:00 AM EDT  

Blogger Thomas Kyte said....


either write a tiny parser (in plsql or whatever, not really that hard) for these very simple expressions or use xmlquery.

hard parsing is MUCH slower than xmlquery repeatedly if you are trashing your shared pool. Look at the *big* picture - not just a single user test.

hard parsing is going to kill you.

but writing a simple parser to evaluate such trivial strings would be pretty darn easy.

Tue Mar 12, 10:24:00 AM EDT  

Blogger Joaquin Gonzalez said....

Curious note about "Unoptimized XML construct" when using autotrace:

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production

1 select xmlquery(replace( :x, '/', ' div ' )
2* returning content ) .getNumberVal() from dual


1 row selected.

Execution Plan
Plan hash value: 1546270724

| Id | Operation | Name | Rows | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

- Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)

0 recursive calls
12 db block gets
1 consistent gets
0 physical reads
0 redo size
281 bytes sent via SQL*Net to client
362 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

I guess what could be optimized in such a simple query. Or maybe it's autotrace doing weird things. Using dbms_xplan doesn't show any note.


Joaquín González

Thu Apr 04, 12:29:00 PM EDT  


<< Home