Oracle 11G: XMLQuery = eval

Anton Scheffer 6

A nice little trick on Oracle 11G is using XMLQuery as an eval function:

SQL>l
  1  select substr( sys_connect_by_path( level, '*' ), 2 ) || ' = ' ||
  2         XMLQuery( substr( sys_connect_by_path( level, '*' ), 2 ) RETURNING CONTENT).getnumberval() product
  3       , substr( sys_connect_by_path( level, '+' ), 2 ) || ' = ' ||
  4         XMLQuery( substr( sys_connect_by_path( level, '+' ), 2 ) RETURNING CONTENT).getnumberval() sum
  5  from dual
  6* connect by level < 6
SQL>/
PRODUCT                   SUM
------------------------- -------------------------
1 = 1                     1 = 1
1*2 = 2                   1+2 = 3
1*2*3 = 6                 1+2+3 = 6
1*2*3*4 = 24              1+2+3+4 = 10
1*2*3*4*5 = 120           1+2+3+4+5 = 15

Anton   Marco’s first performance  investigation:

== First Time ==

Execution Plan
———————————————————-
Plan hash value: 1236776825

—————————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
|* 1 | CONNECT BY WITHOUT FILTERING| | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
—————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(LEVEL<6)

Statistics
———————————————————-
2526 recursive calls
910 db block gets
1971 consistent gets
171 physical reads
16816 redo size
834 bytes sent via SQL*Net to client
407 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
191 sorts (memory)
0 sorts (disk)
5 rows processed

== Second Time ==

Execution Plan
———————————————————-
Plan hash value: 1236776825

—————————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
|* 1 | CONNECT BY WITHOUT FILTERING| | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
—————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(LEVEL<6)

Statistics
———————————————————-
71 recursive calls
900 db block gets
169 consistent gets
0 physical reads
0 redo size
834 bytes sent via SQL*Net to client
407 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed

 

Disclaimer

The information demonstrated and shared here is based on Oracle beta software. The following is intended to outline Oracle’s general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

6 thoughts on “Oracle 11G: XMLQuery = eval

  1. I trust that 11g has a lot more attack surface than any other oracle version. EVAL functions are potential new sql injection opportunities, or am I wrong? Which would not make me happy, because I am not a pro hacker but a database customer.
    Andre

  2. Bases on Oracle 11g Beta autotrace output is as follows.

    == First Time ==

    Execution Plan
    ———————————————————-
    Plan hash value: 1236776825

    —————————————————————————–
    | Id | Operation | Name | Rows | Cost (%CPU)| Time |
    —————————————————————————–
    | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
    |* 1 | CONNECT BY WITHOUT FILTERING| | | | |
    | 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
    —————————————————————————–

    Predicate Information (identified by operation id):
    —————————————————

    1 – filter(LEVEL

  3. Nice post! I like it aswel. It gives and good example how you can use XMLDB functions in your day to day relational environment.

  4. You leave up it up to the reader to grasp the meaning of this – what the hack is an EVAL function. It is quite interesting of course: it allows in-place, immediate evaluation of dynamically constructed pieces of SQL – its like calling a PL/SQL function that uses EXECUTE IMMEDIATE or dbms_sql to process the string passed in and returns the result – without having to create the function.

    Have you any comments on performance impact of using this eval wannabe?
    Lucas

Comments are closed.

Next Post

ADF Faces File Uploading - It is really that simple!

Facebook0TwitterLinkedinOne particularly type of interaction in web applications is typically a little bit tricky. Requiring additional analysis of the frameworks used. For me personally it was even the way to get started with Java technology (Jason Hunter’s Servlet Programming – that’s where for me it all began). I am talking […]