Oracle 11G: XMLQuery = eval

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.

Share.

About Author

6 Comments

  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. Marco Gralike on

    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. Marco Gralike on

    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