Search

Top 60 Oracle Blogs

Recent comments

SQL, XML/SQL, XPath, XQuery

HOWTO: Viewing XML data in V_$CELL_% storage cell SYS views

Got a small question from Frits if I could help him make some XML data readable in one of the SYS.V_$CELL_% / V$CELL_% views. I have been a bit busy, in between jobs, to try to make some of those XML columns more readable anyway (for myself and others) so…lets have a go at it. …

Continue reading »

OOW 2012 – XQuery Update (HOL)

As promised, hereby the Hands-On Lab Oracle OpenWorld XQuery Update example statements. The following XQuery Update code will work from Oracle database 11.2.0.3 and upwards and is fully supported from this version onwards. For more information see some of the blogposts here on this site or the OTN XMLDB forum for more examples. The code …

Continue reading »

OOW 2012 – Oracle XML DB Hands-On Lab (HOL10055)

I posted, as (my) tradition dictates, the Oracle agenda overview of XMLDB sessions during Oracle Open World 2012. I already signed up for the HOL10055 session but yesterday I also got a quick peek of it’s contents this year. And yes, I can promise you, it is a must go, if you are dealing with

Read More...

HOWTO: XDB Repository Events – An Introduction

Oracle XMLDB Repository Events, IMHO, was one of the coolest functionalities introduced in Oracle 11.1. In principal they are a kind of event “triggers” that get fired during actions / methods on objects in the XDB Repository. One of the disadvantages of this functionality is that they are very “sparsely” documented in the Oracle XMLDB

Read More…

HOWTO: Consume Anydata via XMLType (and back)

This was a small mind exercise on the OakTable website (OakTable Challenge)for a person regarding how to go from a relational table to anydata datatype table and back, which I, of course, approached via an “XMLType” of way thinking. Probably the whole thing is not that practical and/or can be optimized in various ways, but …

Continue reading »

HOWTO: Count all Rows in all Tables – The XMLDB Way

Someone beat me to it in this good post: Oracle Tip: Counting ROWS for all tables in a Schema . So here a reminder for me where to find it.

HOWTO: Trace “ORA-19022: Unoptimized XML construct”

So you’re on 11.2.0.2.0 and you encountered in SQL*Plus this new feature “Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)“. What can you do and how to get more info…?

I encountered this new feature in SQL*Plus a month or so ago via the executing the following:

 
SQL#66cc66;">> #993333; font-weight: bold;">SET autotrace #993333; font-weight: bold;">ON
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT xt#66cc66;">.nam#66cc66;">, xt2#66cc66;">.color
  #cc66cc;">2  #993333; font-weight: bold;">FROM XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#993333; font-weight: bold;">DEFAULT #ff0000;">'abc.com/123'#66cc66;">,
  #cc66cc;">3                              #ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
  #cc66cc;">4                #ff0000;">'employees/emp'
  #cc66cc;">5                PASSING XMLTYPE#66cc66;">(#ff0000;">'
  6   
  7    Scott
  8    
  9     red
 10      orange
 11     
 12    
 13    
 14     John
 15     
 16      blue
 17      green
 18     
 19    
 20   '#66cc66;">)
 #cc66cc;">21                 #993333; font-weight: bold;">COLUMNS
 #cc66cc;">22                 nam      VARCHAR2#66cc66;">(#cc66cc;">20#66cc66;">) PATH #ff0000;">'name'#66cc66;">,
 #cc66cc;">23                 color_t  XMLTYPE      PATH #ff0000;">'b:favorites'#66cc66;">) xt#66cc66;">,  
 #cc66cc;">24        XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
 #cc66cc;">25                 #ff0000;">'b:favorites/b:color'
 #cc66cc;">26                 PASSING xt#66cc66;">.color_t  
 #cc66cc;">27                 #993333; font-weight: bold;">COLUMNS
 #cc66cc;">28                 color  VARCHAR2#66cc66;">(#cc66cc;">10#66cc66;">) PATH #ff0000;">'.'#66cc66;">) xt2;
 
NAM                  COLOR
#808080; font-style: italic;">-------------------- ----------
Scott                red
Scott                orange
John                 blue
John                 green
 
 
Execution Plan
#808080; font-style: italic;">----------------------------------------------------------
Plan hash #993333; font-weight: bold;">VALUE: #cc66cc;">1368717035
 
#808080; font-style: italic;">-------------------------------------------------------------------------------------------------------------
#66cc66;">| Id  #66cc66;">| Operation                          #66cc66;">| Name                   #66cc66;">| #993333; font-weight: bold;">ROWS  #66cc66;">| Bytes #66cc66;">| Cost #66cc66;">(%CPU#66cc66;">)#66cc66;">| #993333; font-weight: bold;">TIME     #66cc66;">|
#808080; font-style: italic;">-------------------------------------------------------------------------------------------------------------
#66cc66;">|   #cc66cc;">0 #66cc66;">| #993333; font-weight: bold;">SELECT STATEMENT                   #66cc66;">|                        #66cc66;">|    66M#66cc66;">|   254M#66cc66;">|   221K  #66cc66;">(#cc66cc;">1#66cc66;">)#66cc66;">| 00:#cc66cc;">44:#cc66cc;">21 #66cc66;">|
#66cc66;">|   #cc66cc;">1 #66cc66;">|  NESTED LOOPS                      #66cc66;">|                        #66cc66;">|    66M#66cc66;">|   254M#66cc66;">|   221K  #66cc66;">(#cc66cc;">1#66cc66;">)#66cc66;">| 00:#cc66cc;">44:#cc66cc;">21 #66cc66;">|
#66cc66;">|   #cc66cc;">2 #66cc66;">|   COLLECTION ITERATOR PICKLER FETCH#66cc66;">| XMLSEQUENCEFROMXMLTYPE #66cc66;">|  #cc66cc;">8168 #66cc66;">| #cc66cc;">16336 #66cc66;">|    #cc66cc;">29   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">3 #66cc66;">|   COLLECTION ITERATOR PICKLER FETCH#66cc66;">| XMLSEQUENCEFROMXMLTYPE #66cc66;">|  #cc66cc;">8168 #66cc66;">| #cc66cc;">16336 #66cc66;">|    #cc66cc;">27   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#808080; font-style: italic;">-------------------------------------------------------------------------------------------------------------
 
Note
#808080; font-style: italic;">-----
   #66cc66;">- Unoptimized XML construct detected #66cc66;">(enable XMLOptimizationCheck #993333; font-weight: bold;">FOR more information#66cc66;">)
 
 
Statistics
#808080; font-style: italic;">----------------------------------------------------------
          #cc66cc;">0  recursive calls
          #cc66cc;">0  db block gets
          #cc66cc;">0  consistent gets
          #cc66cc;">0  physical reads
          #cc66cc;">0  redo #993333; font-weight: bold;">SIZE
        #cc66cc;">557  bytes sent via #993333; font-weight: bold;">SQL#66cc66;">*Net #993333; font-weight: bold;">TO client
        #cc66cc;">419  bytes received via #993333; font-weight: bold;">SQL#66cc66;">*Net #993333; font-weight: bold;">FROM client
          #cc66cc;">2  #993333; font-weight: bold;">SQL#66cc66;">*Net roundtrips #993333; font-weight: bold;">TO#66cc66;">/#993333; font-weight: bold;">FROM client
          #cc66cc;">0  sorts #66cc66;">(memory#66cc66;">)
          #cc66cc;">0  sorts #66cc66;">(disk#66cc66;">)
          #cc66cc;">4  #993333; font-weight: bold;">ROWS processed

By noticing the “COLLECTION ITERATOR PICKLER FETCH” this already dawned with me. In principle a “COLLECTION ITERATOR PICKLER FETCH” means that the XML document or (intermediate) fragment is handled in memory and should be avoided because it is “serialized” and dealt via a Pickler Fetch routine, which in most cases is done via a standard XML parser, which can not be optimized by Oracle, for example, because Oracle doesn’t have enough information (provided maybe via an XML Schema) to re-write this query in a more optimal form. See this website for more information on collection iterator pickler fetches.

XMLOptimizationCheck

The “Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)” is new in SQL*Plus / database version 11.2.0.2.0 and is the equivalent of setting a 19021 level 0×1 via for example: ALTER session SET events =’19021 trace name context forever, level 0×1′.

By setting the “XMLOptimizationCheck” setting in SQL*Plus, Oracle/the database will refuse to execute this unoptimized code.

BE AWARE:
Playing with internal Oracle support database events should only be done when advised by Oracle support, or on a test system were it is not a big deal when this gets corrupted! My advice from me to you, but don’t start whining if it break your environment…You can’t say I didn’t warn you.

According to an entry in the XMLDB Developers manual this is only used in a “test” or “debug” situation.

When this mode is on, the plan of execution is automatically checked for XQuery optimization, and if the plan is suboptimal then an error is raised and diagnostic information is written to the trace file indicating which operators are not rewritten.

And in the SQL*Plus manual for the latest release, under new features, 11.2.0.2, it states:

SET XMLOPTIMIZATIONCHECK
SET XMLOPTIMIZATIONCHECK specifies that only fully optimized XML queries and DML operations are executed. It is only to assist during code development and debugging.

 
SQL#66cc66;">> #993333; font-weight: bold;">SET XMLOptimizationCheck #993333; font-weight: bold;">ON
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT xt#66cc66;">.nam#66cc66;">, xt2#66cc66;">.color
  #cc66cc;">2  #993333; font-weight: bold;">FROM XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#993333; font-weight: bold;">DEFAULT #ff0000;">'abc.com/123'#66cc66;">,
  #cc66cc;">3                              #ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
  #cc66cc;">4                #ff0000;">'employees/emp'
  #cc66cc;">5                PASSING XMLTYPE#66cc66;">(#ff0000;">'
  6   
  7    Scott
  8    
  9     red
 10      orange
 11     
 12    
 13    
 14     John
 15     
 16      blue
 17      green
 18     
 19    
 20   '#66cc66;">)
 #cc66cc;">21                 #993333; font-weight: bold;">COLUMNS
 #cc66cc;">22                 nam      VARCHAR2#66cc66;">(#cc66cc;">20#66cc66;">) PATH #ff0000;">'name'#66cc66;">,
 #cc66cc;">23                 color_t  XMLTYPE      PATH #ff0000;">'b:favorites'#66cc66;">) xt#66cc66;">,  #808080; font-style: italic;">-- path to the node that repeats
 #cc66cc;">24        XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
 #cc66cc;">25                 #ff0000;">'b:favorites/b:color'
 #cc66cc;">26                 PASSING xt#66cc66;">.color_t  #808080; font-style: italic;">-- define input XMLType as output of above, aka a join
 #cc66cc;">27                 #993333; font-weight: bold;">COLUMNS
 #cc66cc;">28                 color  VARCHAR2#66cc66;">(#cc66cc;">10#66cc66;">) PATH #ff0000;">'.'#66cc66;">) xt2;
 
#993333; font-weight: bold;">SELECT xt#66cc66;">.nam#66cc66;">, xt2#66cc66;">.color
#66cc66;">*
ERROR at line #cc66cc;">1:
ORA#66cc66;">-#cc66cc;">19022: Unoptimized XML construct detected
#66cc66;">.

In the trace directory, a trace file will be created showing the following, or alike trace file content for your statement. In the example here a trace file was created with the following content

#66cc66;">[oracle@localhost trace#66cc66;">]$ cat orcl_ora_3092#66cc66;">.trc
 
Trace file #66cc66;">/home#66cc66;">/oracle#66cc66;">/app#66cc66;">/oracle#66cc66;">/diag#66cc66;">/rdbms#66cc66;">/orcl#66cc66;">/orcl#66cc66;">/trace#66cc66;">/orcl_ora_3092#66cc66;">.trc
Oracle #993333; font-weight: bold;">DATABASE 11g Enterprise Edition Release 11#66cc66;">.2#66cc66;">.0#66cc66;">.2#66cc66;">.0 #66cc66;">- Production
#993333; font-weight: bold;">WITH the Partitioning#66cc66;">, OLAP#66cc66;">, #993333; font-weight: bold;">DATA Mining #993333; font-weight: bold;">AND #993333; font-weight: bold;">REAL Application Testing options
ORACLE_HOME #66cc66;">= #66cc66;">/home#66cc66;">/oracle#66cc66;">/app#66cc66;">/oracle#66cc66;">/product#66cc66;">/11#66cc66;">.2#66cc66;">.0#66cc66;">/dbhome_2
System name:    Linux
Node name:      localhost#66cc66;">.localdomain
Release:        2#66cc66;">.6#66cc66;">.18#66cc66;">-194#66cc66;">.0#66cc66;">.0#66cc66;">.0#66cc66;">.4#66cc66;">.el5
Version:        ##cc66cc;">1 SMP Thu Apr #cc66cc;">8 #cc66cc;">18:#cc66cc;">20:#cc66cc;">19 EDT #cc66cc;">2010
Machine:        i686
Instance name: orcl
Redo thread mounted #993333; font-weight: bold;">BY this instance: #cc66cc;">1
Oracle process #993333; font-weight: bold;">NUMBER: #cc66cc;">19
Unix process pid: #cc66cc;">3092#66cc66;">, image: oracle@localhost#66cc66;">.localdomain #66cc66;">(TNS V1#66cc66;">-V3#66cc66;">)
 
 
#66cc66;">*** #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">14:#cc66cc;">51:#cc66cc;">46.775
#66cc66;">*** #993333; font-weight: bold;">SESSION ID:#66cc66;">(#cc66cc;">1.7#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">14:#cc66cc;">51:#cc66cc;">46.775
#66cc66;">*** CLIENT ID:#66cc66;">(#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">14:#cc66cc;">51:#cc66cc;">46.775
#66cc66;">*** SERVICE NAME:#66cc66;">(SYS$USERS#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">14:#cc66cc;">51:#cc66cc;">46.775
#66cc66;">*** MODULE NAME:#66cc66;">(#993333; font-weight: bold;">SQL#66cc66;">*Plus#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">14:#cc66cc;">51:#cc66cc;">46.775
#66cc66;">*** ACTION NAME:#66cc66;">(#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">14:#cc66cc;">51:#cc66cc;">46.775
 
#66cc66;">===============================================================================
XML Performance Diagnosis:
Unparsed Query:
#66cc66;">******* UNPARSED QUERY #993333; font-weight: bold;">IS #66cc66;">*******
#993333; font-weight: bold;">SELECT #993333; font-weight: bold;">CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(SYS_XQEXTRACT#66cc66;">(#993333; font-weight: bold;">VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,
#ff0000;">'/emp/name'#66cc66;">,#ff0000;">'xmlns="abc.com/123" xmlns:b="xyz.net/456" '#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">20971520#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) 
#993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">20#66cc66;">) #66cc66;">) #ff0000;">"NAM"#66cc66;">,#993333; font-weight: bold;">CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(#993333; font-weight: bold;">VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,
#cc66cc;">20971520#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">10#66cc66;">) #66cc66;">) #ff0000;">"COLOR" 
#993333; font-weight: bold;">FROM #993333; font-weight: bold;">TABLE#66cc66;">(#ff0000;">"SYS"#66cc66;">.#ff0000;">"XQSEQUENCE"#66cc66;">(#993333; font-weight: bold;">EXTRACT#66cc66;">(#ff0000;">"SYS"#66cc66;">.#ff0000;">"XMLTYPE"#66cc66;">(
#ff0000;">'
 
  Scott
  
   red
    orange
   
  
  
   John
   
    blue
    green
   
  
 '#66cc66;">)#66cc66;">,#ff0000;">'/oraxq_defpfx:employees/oraxq_defpfx:emp'#66cc66;">,
 #ff0000;">' xmlns:oraxq_defpfx="abc.com/123"'#66cc66;">)#66cc66;">)#66cc66;">) #ff0000;">"KOKBF$"#66cc66;">,#993333; font-weight: bold;">TABLE#66cc66;">(#ff0000;">"SYS"#66cc66;">.#ff0000;">"XQSEQUENCE"#66cc66;">(#993333; font-weight: bold;">EXTRACT
 #66cc66;">(#993333; font-weight: bold;">EXTRACT#66cc66;">(#993333; font-weight: bold;">VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#ff0000;">'/oraxq_defpfx:emp/b:favorites'#66cc66;">,#ff0000;">' xmlns:oraxq_defpfx=
 "abc.com/123" xmlns:b="xyz.net/456"'#66cc66;">)#66cc66;">,#ff0000;">'/b:favorites/b:color'
 #66cc66;">,#ff0000;">' xmlns:b="xyz.net/456"'#66cc66;">)#66cc66;">)#66cc66;">) #ff0000;">"KOKBF$" 
 
Reason: upkxml2sql
#66cc66;">===============================================================================
#66cc66;">.

As shown here its depicts the “upkxml2sql” method as the guilty one. You can now try to avoid this issue by using alternative means and/or provide the database with more information, for example, while using XMLType Binary storage, XML Schema based storage (Object Relational / Binary XML) or register an XML Schema in the XDB Repository. Other alternatives could be avoiding the use of the “//” XPath operator.

The moment the query is parsed and executed / shared in cache via the cursor sharing mechanism, then there is a chance you won’t see a trace file. To avoid cursor sharing you could set the cursor sharing to “exact” and/or add white space to your query or alternate upper/lower case and/or other small tricks to force re-parsing.

Before Oracle 11.2.0.2.0 there is an alternative since, AFAIK at least, Oracle version 11.x. by setting the 19027 event, level 0×2000 to get a bit more insight in what happens under the hood, if Oracle can optimize (or not), the request via query re-write.

19027 trace name context forever, level 0×2000

If you would set the 19027 event, level 0×2000, then a trace file is produced in the trace directory of the base DIAG directory structure.

 
SQL#66cc66;">> #993333; font-weight: bold;">ALTER #993333; font-weight: bold;">SESSION #993333; font-weight: bold;">SET events #66cc66;">= #ff0000;">'19027 trace name context forever, level 0x2000';
 
#993333; font-weight: bold;">SESSION altered#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT xt#66cc66;">.nam#66cc66;">, xt2#66cc66;">.color
  #cc66cc;">2  #993333; font-weight: bold;">FROM XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#993333; font-weight: bold;">DEFAULT #ff0000;">'abc.com/123'#66cc66;">,
  #cc66cc;">3                              #ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
  #cc66cc;">4                #ff0000;">'employees/emp'
  #cc66cc;">5                PASSING XMLTYPE#66cc66;">(#ff0000;">'
  6   
  7    Scott
  8    
  9     red
 10      orange
 11     
 12    
 13    
 14     John
 15     
 16      blue
 17      green
 18     
 19    
 20   '#66cc66;">)
 #cc66cc;">21                 #993333; font-weight: bold;">COLUMNS
 #cc66cc;">22                 nam      VARCHAR2#66cc66;">(#cc66cc;">20#66cc66;">) PATH #ff0000;">'name'#66cc66;">,
 #cc66cc;">23                 color_t  XMLTYPE      PATH #ff0000;">'b:favorites'#66cc66;">) xt#66cc66;">,  #808080; font-style: italic;">-- path to the node that repeats
 #cc66cc;">24        XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
 #cc66cc;">25                 #ff0000;">'b:favorites/b:color'
 #cc66cc;">26                 PASSING xt#66cc66;">.color_t  #808080; font-style: italic;">-- define input XMLType as output of above, aka a join
 #cc66cc;">27                 #993333; font-weight: bold;">COLUMNS
 #cc66cc;">28                 color  VARCHAR2#66cc66;">(#cc66cc;">10#66cc66;">) PATH #ff0000;">'.'#66cc66;">) xt2;
 
NAM                  COLOR
#808080; font-style: italic;">-------------------- ----------
Scott                red
Scott                orange
John                 blue
John                 green
 
SQL#66cc66;">> #993333; font-weight: bold;">ALTER #993333; font-weight: bold;">SESSION #993333; font-weight: bold;">SET events #66cc66;">=#ff0000;">'19027 trace name errorstack off';
 
#993333; font-weight: bold;">SESSION altered#66cc66;">.

The trace file can be found via following the DIAG directory structure, which can be found, for example, via entering “show parameter diag” in SQL*Plus (as user SYS or alternative highly privileged database account).

One of the latest trace files in this directory shows now similar content in that trace file as shown below.

#66cc66;">[oracle@localhost trace#66cc66;">]$ pwd
 
#66cc66;">/home#66cc66;">/oracle#66cc66;">/app#66cc66;">/oracle#66cc66;">/diag#66cc66;">/rdbms#66cc66;">/orcl#66cc66;">/orcl#66cc66;">/trace
 
#66cc66;">[oracle@localhost trace#66cc66;">]$ cat orcl_ora_6167#66cc66;">.trc
Trace file #66cc66;">/home#66cc66;">/oracle#66cc66;">/app#66cc66;">/oracle#66cc66;">/diag#66cc66;">/rdbms#66cc66;">/orcl#66cc66;">/orcl#66cc66;">/trace#66cc66;">/orcl_ora_6167#66cc66;">.trc
Oracle #993333; font-weight: bold;">DATABASE 11g Enterprise Edition Release 11#66cc66;">.2#66cc66;">.0#66cc66;">.2#66cc66;">.0 #66cc66;">- Production
#993333; font-weight: bold;">WITH the Partitioning#66cc66;">, OLAP#66cc66;">, #993333; font-weight: bold;">DATA Mining #993333; font-weight: bold;">AND #993333; font-weight: bold;">REAL Application Testing options
ORACLE_HOME #66cc66;">= #66cc66;">/home#66cc66;">/oracle#66cc66;">/app#66cc66;">/oracle#66cc66;">/product#66cc66;">/11#66cc66;">.2#66cc66;">.0#66cc66;">/dbhome_2
System name:    Linux
Node name:      localhost#66cc66;">.localdomain
Release:        2#66cc66;">.6#66cc66;">.18#66cc66;">-194#66cc66;">.0#66cc66;">.0#66cc66;">.0#66cc66;">.4#66cc66;">.el5
Version:        ##cc66cc;">1 SMP Thu Apr #cc66cc;">8 #cc66cc;">18:#cc66cc;">20:#cc66cc;">19 EDT #cc66cc;">2010
Machine:        i686
Instance name: orcl
Redo thread mounted #993333; font-weight: bold;">BY this instance: #cc66cc;">1
Oracle process #993333; font-weight: bold;">NUMBER: #cc66cc;">24
Unix process pid: #cc66cc;">6167#66cc66;">, image: oracle@localhost#66cc66;">.localdomain #66cc66;">(TNS V1#66cc66;">-V3#66cc66;">)
 
 
#66cc66;">*** #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">11:06:#cc66cc;">22.030
#66cc66;">*** #993333; font-weight: bold;">SESSION ID:#66cc66;">(#cc66cc;">1.47#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">11:06:#cc66cc;">22.030
#66cc66;">*** CLIENT ID:#66cc66;">(#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">11:06:#cc66cc;">22.030
#66cc66;">*** SERVICE NAME:#66cc66;">(SYS$USERS#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">11:06:#cc66cc;">22.030
#66cc66;">*** MODULE NAME:#66cc66;">(#993333; font-weight: bold;">SQL#66cc66;">*Plus#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">11:06:#cc66cc;">22.030
#66cc66;">*** ACTION NAME:#66cc66;">(#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">11:06:#cc66cc;">22.030
 
        XMLTABLE RWT QUERY
#993333; font-weight: bold;">SELECT #993333; font-weight: bold;">CAST#66cc66;">( sys_xq_upkxml2sql#66cc66;">(sys_xqexval#66cc66;">(sys_xqextract#66cc66;">(#993333; font-weight: bold;">VALUE#66cc66;">(QMXTABF$#66cc66;">)#66cc66;">, #ff0000;">'/emp/name'#66cc66;">,#ff0000;">'xmlns="abc.com/123" xmlns:b="xyz.net/456" '#66cc66;">)#66cc66;">)#66cc66;">, #cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">, #cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">20#66cc66;">) #66cc66;">)  #993333; font-weight: bold;">AS #ff0000;">"NAM"#66cc66;">,xmlquery#66cc66;">( #ff0000;">'declare default element namespace "abc.com/123";declare namespace b="xyz.net/456";b:favorites' PASSING #993333; font-weight: bold;">BY XMLTABLE #993333; font-weight: bold;">VALUE#66cc66;">(QMXTABF$#66cc66;">) returning content #66cc66;">) #993333; font-weight: bold;">AS #ff0000;">"COLOR_T" #993333; font-weight: bold;">FROM #993333; font-weight: bold;">TABLE#66cc66;">(xqsequence#66cc66;">( xmlquery#66cc66;">(#ff0000;">'declare default element namespace "abc.com/123";declare namespace b="xyz.net/456";employees/emp' passing #993333; font-weight: bold;">BY #993333; font-weight: bold;">VALUE QMXTABCOL$ returning #993333; font-weight: bold;">SEQUENCE#66cc66;">)  #66cc66;">)#66cc66;">) QMXTABF$
        XMLTABLE RWT QUERY #993333; font-weight: bold;">END
        XMLTABLE RWT QUERY
#993333; font-weight: bold;">SELECT #993333; font-weight: bold;">CAST#66cc66;">( sys_xq_upkxml2sql#66cc66;">(sys_xqexval#66cc66;">(xmlquery#66cc66;">( #ff0000;">'declare namespace b="xyz.net/456";.' PASSING #993333; font-weight: bold;">BY XMLTABLE #993333; font-weight: bold;">VALUE#66cc66;">(QMXTABF$#66cc66;">) returning #993333; font-weight: bold;">SEQUENCE #66cc66;">)#66cc66;">)#66cc66;">, #cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">, #cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">10#66cc66;">) #66cc66;">)  #993333; font-weight: bold;">AS #ff0000;">"COLOR" #993333; font-weight: bold;">FROM #993333; font-weight: bold;">TABLE#66cc66;">(xqsequence#66cc66;">( xmlquery#66cc66;">(#ff0000;">'declare namespace b="xyz.net/456";b:favorites/b:color' passing #993333; font-weight: bold;">BY #993333; font-weight: bold;">VALUE QMXTABCOL$ returning #993333; font-weight: bold;">SEQUENCE#66cc66;">)  #66cc66;">)#66cc66;">) QMXTABF$
        XMLTABLE RWT QUERY #993333; font-weight: bold;">END
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef

So both XMLTABLE subsections of the used query are attempted by Oracle to rewritten into internal XQuery (“xq”, XQuery, extract, evaluate, xml2sql, xq sequence routines). Looking at the original query…

#993333; font-weight: bold;">SELECT xt#66cc66;">.nam
#66cc66;">,      xt2#66cc66;">.color
#993333; font-weight: bold;">FROM   XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#993333; font-weight: bold;">DEFAULT #ff0000;">'abc.com/123'#66cc66;">,
                              #ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
                #ff0000;">'employees/emp'
                PASSING XMLTYPE#66cc66;">(#ff0000;">'
                                 
                                  Scott
                                  
                                   red
                                    orange
                                   
                                  
                                  
                                   John
                                   
                                    blue
                                    green
                                   
                                  
                                 '#66cc66;">)
                 #993333; font-weight: bold;">COLUMNS
                 nam      VARCHAR2#66cc66;">(#cc66cc;">20#66cc66;">) PATH #ff0000;">'name'#66cc66;">,
                 color_t  XMLTYPE      PATH #ff0000;">'b:favorites'#66cc66;">) xt   
  #66cc66;">,     XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
                 #ff0000;">'b:favorites/b:color'
                 PASSING xt#66cc66;">.color_t  
                 #993333; font-weight: bold;">COLUMNS
                 color  VARCHAR2#66cc66;">(#cc66cc;">10#66cc66;">) PATH #ff0000;">'.'#66cc66;">)             xt2
;

You can see the two XMLTABLE statements…

#993333; font-weight: bold;">SELECT #993333; font-weight: bold;">CAST#66cc66;">( sys_xq_upkxml2sql#66cc66;">(sys_xqexval#66cc66;">(sys_xqextract#66cc66;">(#993333; font-weight: bold;">VALUE#66cc66;">(QMXTABF$#66cc66;">)#66cc66;">, #ff0000;">'/emp/name'
                                                                        #66cc66;">, #ff0000;">'xmlns="abc.com/123" xmlns:b="xyz.net/456" '#66cc66;">)
                                           #66cc66;">)#66cc66;">, #cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">, #cc66cc;">2
                               #66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">20#66cc66;">) #66cc66;">)  #993333; font-weight: bold;">AS #ff0000;">"NAM"
#66cc66;">,      xmlquery#66cc66;">( #ff0000;">'declare default element namespace "abc.com/123";
                  declare namespace b="xyz.net/456";
                  b:favorites' 
                  PASSING #993333; font-weight: bold;">BY XMLTABLE #993333; font-weight: bold;">VALUE#66cc66;">(QMXTABF$#66cc66;">) 
                  returning content #66cc66;">) #993333; font-weight: bold;">AS #ff0000;">"COLOR_T" 
                  #993333; font-weight: bold;">FROM #993333; font-weight: bold;">TABLE#66cc66;">(xqsequence#66cc66;">( xmlquery#66cc66;">(#ff0000;">'declare default element namespace "abc.com/123";
                                                   declare namespace b="xyz.net/456";employees/emp' 
                                                   passing #993333; font-weight: bold;">BY #993333; font-weight: bold;">VALUE QMXTABCOL$ 
                                                   returning #993333; font-weight: bold;">SEQUENCE#66cc66;">) 
                                        #66cc66;">)
                            #66cc66;">) QMXTABF$

and…
.

#993333; font-weight: bold;">SELECT #993333; font-weight: bold;">CAST#66cc66;">( sys_xq_upkxml2sql#66cc66;">(sys_xqexval#66cc66;">(xmlquery#66cc66;">( #ff0000;">'declare namespace b="xyz.net/456";.' 
                                                      PASSING #993333; font-weight: bold;">BY XMLTABLE #993333; font-weight: bold;">VALUE#66cc66;">(QMXTABF$#66cc66;">) 
                                                      returning #993333; font-weight: bold;">SEQUENCE #66cc66;">)
                                           #66cc66;">)#66cc66;">, #cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">, #cc66cc;">2
                               #66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">10#66cc66;">) #66cc66;">)  #993333; font-weight: bold;">AS #ff0000;">"COLOR" 
#993333; font-weight: bold;">FROM   #993333; font-weight: bold;">TABLE#66cc66;">(xqsequence#66cc66;">( xmlquery#66cc66;">(#ff0000;">'declare namespace b="xyz.net/456";
                                   b:favorites/b:color' 
                                   passing #993333; font-weight: bold;">BY #993333; font-weight: bold;">VALUE QMXTABCOL$ 
                                   returning #993333; font-weight: bold;">SEQUENCE#66cc66;">)  
                        #66cc66;">)
            #66cc66;">) QMXTABF$

Most of this long list in the trace file can be deducted as in a more regular “10053″ trace file / event…

NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
#66cc66;">...
#66cc66;">...

…but I will have to dig a bit more to see how this all fits together…

For now / HTH / To be continued…

Common XQuery mistakes…applied in XML DB

Do you read FAQ…?

Somehow I keep people reminding there is a FAQ URL on the XMLDB forum and even then people refuse to read those good examples… Anyway found two great posts I want to share and remember on this, my, web “notepad”. Besides the treewalker example, I tested the examples of those mentioned in the XQuery post on a Oracle 11.2 database.

As far as I could find the treewalker example is part of DOM V2 and not mandatory to implement but I wonder how I can get around the local() stuff, anyway, I will have to investigate a bit further if its just me being a novice in XQuery or that I am missing out on details/info. The XQuery post only demonstrates to me how powerful this extra query language is in an Oracle database and that it is time for me to learn this properly…

The posts that I was referring to:

…be aware of the use of the (double quote instead single quote), namespaces (indeed apparently always an issue) and using (::) in SQL*Plus… The (::) is needed in SQL*Plus to mark that the “;”  is not seen as direct processing instruction for SQL*Plus, but in this case, is for the XQuery engine.

The headlines follow the ones in the XQuery post…

C based XML tools in your $ORACLE_HOME

Being triggered by Laurent Schneider’s post “extract xml from the command line“; I completely forgot about the C-based XDK tooling you nowadays can find in your $ORACLE_HOME. You, probably just like me, weren’t even aware, there were some (C-based that is). Most of these are executable’s and not “just” Java tools, although xsql is a shell script that still starts Java. More information can be found here in the “Oracle® XML Developer’s Kit Programmer’s Guide 11.2

I mean in principle they are not “new”, they were there since 8.1.x, but now they are compiled executables which you can use on the shell prompt and or in scripting and that is, at least for me, easier than doing the same via their $ORACLE_HOME/xdk Java counterparts.

A shortlist:

External Views (XML based)

Something new? Eh? Should you do this? Eh?

In all, probably not, but for me this was a good exercise towards some more updated demo scripting for my “Boost your environment with XMLDB” presentation or hopefully more clearer relabeled Oracle Open World name for the almost same presentation called “Interfacing with Your Database via Oracle XML DB” (S319105). Just up front, there are some issues with the following:

  • Why should you do it at all. You should have a good reason doing so…
  • It can cause a lot of Physical I/O, at least initially when not cached in the SGA
  • Until current versions, AFAIK, it will do a lot of “Pickler Fetching”, serializing in memory, which is very resource intensive (CPU/PGA)
  • …and its probably not supported…?

…but it is good fun for a small exercise based on the following OTN Thread: “Error with basic XMLTable“…

Let me show you what I mean.

Via “bfilename” you are able, since a long time, I guess Oracle 9.2 and onwards, to read a file as a BLOB and because an “XMLTYPE” can swallow almost any datatype, you could do the following…