blog.gralike.com http://www.liberidu.com/blog About Oracle, XMLDB and other interests Mon, 30 Aug 2010 19:19:27 +0000 en hourly 1 http://wordpress.org/?v=3.0.1 HOWTO: Merge files under Windows http://feedproxy.google.com/~r/Bloggralikecom/~3/xt6ux6niIRk/ http://www.liberidu.com/blog/?p=2343#comments Mon, 30 Aug 2010 19:13:56 +0000 Marco Gralike http://www.liberidu.com/blog/?p=2343 Small “howto”, because I always forget.

Today I needed to merge to Linux tar files on Windows. I downloaded the two Oracle VM Templates for Oracle Business Intelligence Enterprise Edition (OBIEE). After unzipping them you get two tar files called

  • OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file1of2
  • OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file1of2

To be able to merge them and then “untar” those files, under Linux or Unix you would do something like:

 
cat {file01} {file02}  | tar -xvz
 
cat OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file1of2 OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file2of2| tar -xvz
.

…with other words, you would “cat” the first and second file into a file and then “untar”-ed it on the fly via the “tar -xvz”. In Windows you would do this via the following command, that is the first “cat” part, merging the files, via:

 
copy /B {file01} {file02} {newfile}
 
copy /B OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file1of2 + OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file2of2 OVM_EL4U5_X86_PVM_10gR3BIEE.tgz
.

the Windows equivalent of the Linux/Unix

 
cat {file01} {file02}  > {newfile}
 
cat OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file1of2 + OVM_EL4U5_X86_PVM_10gR3BIEE.tgz.file2of2 > OVM_EL4U5_X86_PVM_10gR3BIEE.tgz
.

So you the Windows statement will “merge & copy” them into the needed file, in my case file: “OVM_EL4U5_X86_PVM_10gR3BIEE.tgz”.

Now you can, with a (un)zip utility like “winrar”, “untar” the ” “OVM_EL4U5_X86_PVM_10gR3BIEE.tgz” file into its original content.

]]> http://www.liberidu.com/blog/?feed=rss2&p=2343 0 http://www.liberidu.com/blog/?p=2343 Common XQuery mistakes…applied in XML DB http://feedproxy.google.com/~r/Bloggralikecom/~3/XLYewzz4xdE/ http://www.liberidu.com/blog/?p=2294#comments Wed, 25 Aug 2010 20:36:32 +0000 Marco Gralike http://www.liberidu.com/blog/?p=2294 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…

You always need to do something else

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $n := 1
  4           return
  5             if ($n = 1) then
  6              "one"
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
from  dual
      *
ERROR at line 9:
ORA-19114: XPST0003 - error during parsing the
XQuery expression:
LPX-00801: XQuery syntax error at 'EOF'
5               "one"
-                    ^
 
SQL> ! oerr ORA 19114
 
19114, "XPST0003 - error during parsing the XQuery expression: %s"
 
// *Cause:  An error occurred during the parsing of the XQuery
            expression.
// *Action: Check the detailed error message for the possible causes.
 
SQL> !  oerr LPX 00801
 
00801, 00000, "XQuery syntax error at"
 
// *Cause:  Invalid XQuery query.
// *Action: Correct the query.
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $n := 1
  4           return
  5             if ($n = 1) then
  6              "one"
  7             else
  8              ()
  9          ' returning content)
 10          as "XMLQuery Output"
 11  from  dual;
 
XMLQuery Output
--------------------------------------------------
one

Dynamic evaluation is desired

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $xml := <foo><bar>text</bar><baz>text</baz></foo>
  4           for $el in ("bar", "baz")
  5           return
  6             $xml/$el/text()
  7          ' returning content)
  8          as "XMLQuery Output"
  9 from  dual;
from  dual
      *
ERROR at line 9:
ORA-19224: XPTY0004 - XQuery static type
mismatch: expected - node()* got - xs:string
 
SQL> ! oerr ORA 19224
 
19224, 00000, "XPTY0004 - XQuery static type mismatch: 
                          expected - %s got - %s "
 
// *Cause:  The expression could not be used because it's static type 
            is not appropriate for the context in which it was used.
// *Action: Fix the expression to be of the required type or add
            appropriate cast functions around the expression.

Curly, curly, curly braces

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "test"
  4           return
  5             <test>{$a}</test>
  6          ' returning content)
  7          as "XMLQuery Output"
  8  from  dual;
 
XMLQuery Output
--------------------------------------------------
<test>test</test>

The desire to return multiple elements is strong

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "test"
  4           return
  5             <el1>{$a}</el1><el2>{$a}</el2>
  6          ' returning content)
  7          as "XMLQuery Output"
  8  from  dual;
from  dual
      *
ERROR at line 8:
ORA-19114: XPST0003 - error during parsing the
XQuery expression:
LPX-00801: XQuery syntax error at '>'
4              <el1>{$a}</el1><el2>{$a}</el2>
-                                 ^
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "test"
  4           return
  5             (<el1>{$a}</el1>,<el2>{$a}</el2>)
  6          ' returning content)
  7          as "XMLQuery Output"
  8  from  dual;
 
XMLQuery Output
--------------------------------------------------
<el1>test</el1><el2>test</el2>

attributes when you mean string

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $node := <meta name="author" content="James Fuller"/>
  4           return
  5             element {$node/@name}
  6                     {$node/@content}
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
<author content="James Fuller"></author>
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $node := <meta name="author" content="James Fuller"/>
  4           return
  5             element {$node/@name}
  6                     {fn:string($node/@content)}
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
<author>James Fuller</author>

Comparing things properly

SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "test"
  4           let $b := "test"
  5           return
  6           fn:compare($a,$b)
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
0
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "abc"
  4           let $b := "ab"
  5           return
  6           fn:compare($a,$b)
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
1
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "ab"
  4           let $b := "abc"
  5           return
  6           fn:compare($a,$b)
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
-1
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := (1,2,3)
  4           let $b := (3)
  5           return
  6           $a = $b
  7          ' returning content)
  8          as "XMLQuery Output"
  9  from  dual;
 
XMLQuery Output
--------------------------------------------------
true
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           let $a := "a"
  4           let $b := "a"
  5           return
  6           if( $a eq $b) then
  7             "string values matched"
  8           else
  9             "string values do not match"
 10          ' returning content)
 11          as "XMLQuery Output"
 12  from  dual;
 
XMLQuery Output
--------------------------------------------------
string values matched

Empty namespaces conundrum

SQL> set lines 100
SQL> set long 10000
SQL> set pages 5000
SQL> set feed on
 
SQL> select xmlquery
  2         ('xquery version "1.0"; (: :)
  3           declare default element namespace "http://www.w3.org/1999/xhtml"; (: :)
  4           declare namespace no-namespace = " "; (: :)
  5           let $xml := <html><body>
  6                       [no-namespace:element]This element has no namespace[/no]
  7                       </body></html>
  8           return
  9           $xml//*[namespace-uri() eq " "]
 10          ' returning content)
 11          as "XMLQuery Output"
 12  from  dual;
 
XMLQuery Output
----------------------------------------------------------------------------------------------------
[no-namespace:element xmlns:no-namespace=" "]This element has no namespace[/no-namespace:element]
 
1 row selected.

Had to use [ ] brackets here because I still don’t have found a plug-in that doesn’t mess with namespaces on WordPress sites and scrambles the output the moment it encounters namespace notations…

Anyway good to see that Oracle produces the same errors and results.
Next one to read for me ‘An Introduction to XQuery FLWOR expression‘…

8-)

]]> http://www.liberidu.com/blog/?feed=rss2&p=2294 0 http://www.liberidu.com/blog/?p=2294 So what’s the “Michigan OakTable Symposium” all about… http://feedproxy.google.com/~r/Bloggralikecom/~3/f6pKg8dXLjM/ http://www.liberidu.com/blog/?p=2283#comments Mon, 23 Aug 2010 19:29:37 +0000 Marco Gralike http://www.liberidu.com/blog/?p=2283 Bit sad that I don’t have the time to post some of the cool internal tracing stuff that I have done lately and that gave me a far better insight in the inner workings of Oracle XMLDB, but there is a reason for it…

:-)

As you have maybe have noticed, there is besides the day-to-day database administration and consulting work, a lot of presenting going on in my “free” time. Sharing is fun, but also needs a lot of preparing. So besides attending and presenting on Oracle Open World and OPP2000 in Brussels, I also sneaky arranged – LOL – some slots in that great Michigan OakTable Symposium line-up, in which I feel myself very humbled…so if you can’t read it maybe you should get hold of me somewhere on one of those conferences and just ask…

;-)

Anyway, need to know why you should attend…? Have a sneak peek…

MOTS from Joel Schneider on Vimeo.

]]> http://www.liberidu.com/blog/?feed=rss2&p=2283 0 http://www.liberidu.com/blog/?p=2283 Oracle 5 Installation Steps http://feedproxy.google.com/~r/Bloggralikecom/~3/vhVbVcg48Yc/ http://www.liberidu.com/blog/?p=2215#comments Tue, 03 Aug 2010 19:22:44 +0000 Marco Gralike http://www.liberidu.com/blog/?p=2215 While browsing my old pictures, I discovered two small directories with some installation snapshots I once made, because there is almost no info left about this topic (besides in people’s heads) I left it here for “past” reference…

Double click on it to go to the bigger “version” on Picassa.

]]> http://www.liberidu.com/blog/?feed=rss2&p=2215 0 http://www.liberidu.com/blog/?p=2215 C based XML tools in your $ORACLE_HOME http://feedproxy.google.com/~r/Bloggralikecom/~3/KNdi7AAOTV0/ http://www.liberidu.com/blog/?p=2205#comments Fri, 30 Jul 2010 00:44:40 +0000 Marco Gralike http://www.liberidu.com/blog/?p=2205 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:

[oracle@localhost bin]$ pwd
/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin
 
[oracle@localhost bin]$ ls -l x* schema
-rwxr-xr-x 1 oracle oracle 3433339 Jun 23 19:56 schema
-rwxr-xr-x 1 oracle oracle 3582629 Jun 23 19:55 xml
-rwxr-xr-x 1 oracle oracle 4006197 Jun 23 19:56 xmlcg
-rwx------ 1 oracle oracle   49812 Mar 11  2009 xmlwf
-rwxr-xr-x 1 oracle oracle 3485095 Jun 23 19:56 xsl
-rwxr-xr-x 1 oracle oracle     748 Nov 12  2006 xsql
-rwxr-xr-x 1 oracle oracle 3496134 Jun 23 19:56 xvm
 
[oracle@localhost bin]$ file x* schema
xml:    ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), FOR GNU/Linux 2.2.5, dynamically linked (uses shared libs), FOR GNU/Linux 2.2.5, NOT stripped
xmlcg:  ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), FOR GNU/Linux 2.2.5, dynamically linked (uses shared libs), FOR GNU/Linux 2.2.5, NOT stripped
xmlwf:  ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), FOR GNU/Linux 2.2.5, dynamically linked (uses shared libs), FOR GNU/Linux 2.2.5, NOT stripped
xsl:    ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), FOR GNU/Linux 2.2.5, dynamically linked (uses shared libs), FOR GNU/Linux 2.2.5, NOT stripped
xsql:   Bourne shell script text executable
xvm:    ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), FOR GNU/Linux 2.2.5, dynamically linked (uses shared libs), FOR GNU/Linux 2.2.5, NOT stripped
schema: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), FOR GNU/Linux 2.2.5, dynamically linked (uses shared libs), FOR GNU/Linux 2.2.5, NOT stripped

So here a small overview on what they can do…

schema

The “schema” binary is probably the XML Schema Processor and can validate schemas, allowing use of simple and complex XML datatypes.

[oracle@localhost bin]$ schema -h
Unknown switch -h
Usage: schema [flags] <instance> [schema] [working dir]
 
WHERE:
    </instance><instance>    IS the XML instance document TO validate (required)
    [schema]      IS the DEFAULT schema (optional)
    [working dir] IS the working directory FOR processing (optional)
 
Flags:
    -0                Always exit WITH code 0 (success)
    -c                Extra tests TO improve code coverage
    -e <encoding>     Specify DEFAULT input file encoding
    -E </encoding><encoding>     Specify output/DATA/presentation encoding
    -i                IGNORE provided schema file
    -o <num>          Validation options
    -p                Print instance document TO stdout ON success
    -v                SHOW version number
    -u                forced TO Unicode path
 
[oracle@localhost bin]$ schema -v
Oracle XML Developers Kit 11.2.0.1.0
</num></encoding></instance>

xml

Among others, “xml”, can be used for checks on XML well formedness and creates and parses XML with industry standard DOM and SAX interfaces.

[oracle@localhost bin]$ xml -h
Usage: xml    [switches] [document URI]
  OR   xml -f [switches] [document filespec]
Switches:
    -B <baseuri>      SET the Base uri FOR XSLT processor.
 BaseUri of http://pqr/xsl.txt resolves pqr.txt TO http://pqr/pqr.txt
    -c                Conformance CHECK only, no validation
    -e <encoding>     Specify DEFAULT input file encoding (-ee TO force)
    -E </encoding><encoding>     Specify output/DATA/presentation encoding
    -f                File - Interpret <document> AS filespec, NOT URI
    -G <xptr exprs>   evaluates XPointer scheme examples give IN a file
    -h                Help - SHOW this usage help (-hh FOR more options)
    -i <n>            Number of times TO iterate the XSLT processing
    -l <language>     LANGUAGE FOR error reporting
    -o <xsloutfile>   Specify output file of XSLT processor
    -p                Print document/DTD structures after parse
    -P                Pretty print FROM root element
    -PP               Pretty print FROM root node (DOC); includes XMLDecl
    -PE <encoding>    Specify encoding FOR -P OR -PP output
    -PX               Include XMLDecl IN output always
    -s <style sheet>  Style sheet - specifies the XSL style sheet
    -v                Version - SHOW parser version AND exit
    -V <var> <value>  TO test top level VARIABLES IN CXSLT
    -w                Whitespace - preserve ALL whitespace
    -W                Warning - stop parsing after a warning
    -x                Exercise SAX interface FOR parser (prints document)
    -Y                control characters are valid
[oracle@localhost bin]$ xml -cf /tmp/otn_dev_xsd_schema.xml
[oracle@localhost bin]$ xml -cf /tmp/otn_dev_xsd_schema.invalid.xml
IN line 14 of /tmp/otn_dev_xsd_schema.invalid.xml:
LPX-00225: end-element tag "OWNER" does NOT match start-element tag "ROW"
 
[oracle@localhost bin]$ xml -v
Oracle XML Developers Kit 11.2.0.1.0
</value></var></style></encoding></xsloutfile></language></n></xptr></document></encoding></baseuri>

xmlcf

“xmlcg” is a C++ tool to generate C++ classes based on XML input.

[oracle@localhost bin]$ xmlcg -h
Error: Unknown switch
Usage: xmlcg [switches] <document>
    -d <name>          DTD - input IS external DTD (specify output name)
    -o <directory>     Output - specify output directory
    -e <encoding>      Encoding - specify input file encoding
    -h                 Help - SHOW this usage help
    -v                 Version - SHOW Class Generator version#
    -s <name>          Schema - input IS an XML Schema (spec. output name)
 
[oracle@localhost bin]$ xmlcg /tmp/otn_dev_xsd_schema.xml
Generating classes FROM DTD...
[oracle@localhost bin]$ xmlcg  /tmp/otn_dev_xsd_schema.invalid.xml
IN line 14 of /tmp/otn_dev_xsd_schema.invalid.xml:
LPX-00225: end-element tag "OWNER" does NOT match start-element tag "ROW"
Parse failed, code 225
 
[oracle@localhost bin]$ xmlcg -v
Oracle XML Developers Kit 11.2.0.1.0
</name></encoding></directory></name></document>

xmlwf

The “xmlwf” tool is not described in the XDK manual and just / “only” checks on XML well formedness which can be useful in itself.

[oracle@localhost bin]$ xmlwf -h
usage: xmlwf [-n] [-p] [-r] [-s] [-w] [-x] [-d output-dir] [-e encoding] file ...
 
[oracle@localhost bin]$ xmlwf /tmp/otn_dev_xsd_schema.xml
[oracle@localhost bin]$ xmlwf /tmp/otn_dev_xsd_schema.invalid.xml
/tmp/otn_dev_xsd_schema.invalid.xml:14:17: mismatched tag
 
[oracle@localhost bin]$ xmlwf -v
xmlwf USING expat_2.0.1
sizeof(XML_Char)=1, sizeof(XML_LChar)=1, XML_DTD, XML_CONTEXT_BYTES=1024, XML_NS

xsl

Tool “xsl” is a C XSLT generator and can transform XML into other text-based formats such as HTML.

[oracle@localhost bin]$ xsl -h
Usage: xsl [switches] <stylesheet> <instance>
  OR   xsl -f [switches] [document filespec]
Switches:
    -B <baseuri>      SET the Base uri FOR XSLT processor.
 BaseUri of http://pqr/xsl.txt resolves pqr.txt TO http://pqr/pqr.txt
    -e <encoding>     Specify DEFAULT input file encoding (-ee TO force)
    -E </encoding><encoding>     Specify output/DATA/presentation encoding
    -f                File - Interpret <document> AS filespec, NOT URI
    -G <xptr exprs>   evaluates XPointer scheme examples give IN a file
    -h                Help - SHOW this usage help (-hh FOR more options)
    -i <n>            Number of times TO iterate the XSLT processing
    -l <language>     LANGUAGE FOR error reporting
    -o <xsloutfile>   Specify output file of XSLT processor
    -v                Version - SHOW parser version AND exit
    -V <var> <value>  TO test top level VARIABLES IN CXSLT
    -w                Whitespace - preserve ALL whitespace
    -W                Warning - stop parsing after a warning
</value></var></xsloutfile></language></n></xptr></document></encoding></baseuri></instance></stylesheet>

xsql

Combines XML, SQL, and XSLT in the server to deliver dynamic Web content.

[oracle@localhost bin]$ xsql
Oracle XML Developers Kit 11.2.0.2.0 - Production
XML-25009: Missing arguments ON command line
 
Usage: xsql xsqlFileURI [outFileName] [param1=value1 ... paramN=valueN]

xvm

“xvm” is the C alternative of the XSLT Virtual Machine (XVM) which provides a high-performance XSLT transformation engine that supports compiled stylesheets.

[oracle@localhost bin]$ xvm -h
Usage:
  xvm  switches <xslfile> <xmlfile>
  xvm  switches <xpath> <xmlfile>
 
Switches:
    -c        Compile <xslfile>. The bytecode IS IN '<xmlfile>.xvm'.
    -ct       Compile <xslfile> AND transform <xmlfile>.
    -t        Transform </xmlfile><xmlfile> USING bytecode FROM <xslfile>.
    -xc       Compile <xpath>. The bytecode IS IN 'code.xvm'.
    -xct      Compile AND evaluate </xpath><xpath> WITH <xmlfile>.
    -xt       Evaluate XPath bytecode FROM <xpath> WITH <xmlfile>.
 
Examples:
  xvm  -ct  db.xsl db.xml
  xvm  -t   db.xvm db.xml
  xvm  -xct "doc/emloyee[15]/family"  db.xml
</xmlfile></xpath></xmlfile></xpath></xslfile></xmlfile></xslfile></xmlfile></xslfile></xmlfile></xpath></xmlfile></xslfile>
]]> http://www.liberidu.com/blog/?feed=rss2&p=2205 0 http://www.liberidu.com/blog/?p=2205 External Views (XML based) http://feedproxy.google.com/~r/Bloggralikecom/~3/4lgnz7F-w48/ http://www.liberidu.com/blog/?p=2194#comments Thu, 29 Jul 2010 19:23:37 +0000 Marco Gralike http://www.liberidu.com/blog/?p=2194 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…

[oracle@localhost ~]$ sqlplus / AS sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production ON Thu Jul 29 09:20:24 2010
 
Copyright (c) 1982, 2009, Oracle.  ALL rights reserved.
 
 
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND Real Application Testing options
 
SQL> CREATE user otn IDENTIFIED BY otn account UNLOCK;
 
User created.
 
SQL> GRANT dba TO otn;
 
GRANT succeeded.
 
SQL> conn otn/otn
Connected.
 
SQL> sho user
USER IS "OTN"
 
SQL> SET pages 5000
SQL> SET LINES 1000
SQL> SET long 10000
 
SQL> SELECT xmltype(cursor(SELECT owner, schema_url, LOCAL FROM all_xml_schemas ORDER BY owner)) FROM dual;
 
XMLTYPE(CURSOR(SELECTOWNER,SCHEMA_URL,LOCALFROMALL_XML_SCHEMASORDERBYOWNER))
-----------------------------------------------------------------------------------------------------------
< ?xml version="1.0"?>
<rowset>
  <row>
    <owner>EXFSYS</owner>
    <schema_url>http://xmlns.oracle.com/rlmgr/rclsprop.xsd</schema_url>
    <local>NO</local>
  </row>
  <row>
    <owner>EXFSYS</owner>
    <schema_url>http://xmlns.oracle.com/rlmgr/rulecond.xsd</schema_url>
    <local>NO</local>
  </row>
  <row>
    <owner>MDSYS</owner>
    <schema_url>http://www.opengis.net/gml/feature.xsd</schema_url>
    <local>NO</local>
  </row>
  <row>
...
  </row><row>
    <owner>XDB</owner>
    <schema_url>http://xmlns.oracle.com/xdb/dav.xsd</schema_url>
    <local>NO</local>
  </row>
</rowset>
 
51 rows selected.

Lets write the output to disk in the /tmp directory or my Oracle Enterprise Linux environment…

 
SQL> sho user
USER IS "OTN"
 
SQL> CREATE OR REPLACE directory XMLDIR AS '/tmp/';
 
Directory created.
 
SQL> declare
  2     rc sys_refcursor;
  3  begin
  4     open rc FOR SELECT * FROM (SELECT owner, schema_url, LOCAL FROM all_xml_schemas ORDER BY owner);
  5  dbms_xslprocessor.clob2file(xmltype(rc).getClobVal(),'TMPDIR','otn_dev_xsd_schema.xml');
  6  end;
  7 ;
 
PL/SQL procedure successfully completed.
 
SQL> commit;
 
commit complete
 
SQL> ! ls -ltra /tmp/*.xml
 
-rw-rw-r-- 1 oracle oracle 6563 Jul 29 09:36 /tmp/otn_dev_xsd_schema.xml

Due to the fact the content of this XML file (be aware: 6K) is wellformed XML, for example the alert log is not wellformed, and I know its format, I could read it directly from my database session via the following…

SQL> SELECT xmltype(bfilename('TMPDIR','otn_dev_xsd_schema.xml'), nls_charset_id('AL32UTF8')) FROM dual;
 
XMLTYPE(BFILENAME('TMPDIR','OTN_DEV_XSD_SCHEMA.XML'),NLS_CHARSET_ID('AL32UTF8'))
---------------------------------------------------------------------------------------
< ?xml version="1.0"?>
<rowset>
  <row>
    <owner>EXFSYS</owner>
    <schema_url>http://xmlns.oracle.com/rlmgr/rclsprop.xsd</schema_url>
    <local>NO</local>
  </row>
  <row>
    <owner>EXFSYS</owner>
    <schema_url>http://xmlns.oracle.com/rlmgr/rulecond.xsd</schema_url>
    <local>NO</local>
  </row>
  <row>
    <owner>MDSYS</owner>
    <schema_url>http://www.opengis.net/gml/feature.xsd</schema_url>
    <local>NO</local>
  </row>
  <row>
    <owner>MDSYS</owner>
    <schema_url>http://xmlns.oracle.com/spatial/georaster/georaster.xsd</schema_url>
    <local>NO</local>
  </row>
  <row>
    <owner>MDSYS</owner>
    <schema_url>http://www.w3.org/1999/xlink/xlinks.xsd</schema_url>
    <local>NO</local>
  </row>
  <row>
    <owner>MDSYS</owner>
    <schema_url>http://www.opengis.net/gml/geometry.xsd</schema_url>
    <local>NO</local>
  </row>
  ...
  <row>
    <owner>XDB</owner>
    <schema_url>http://xmlns.oracle.com/xdb/dav.xsd</schema_url>
    <local>NO</local>
  </row>
</rowset>
 
51 rows selected.

That easy, although as said it has some issues, performance wise. From Oracle database version 10.2.0.1 and onwards, although I would use as a minimum 10.2.0.3.0 due to that the XQuery engine is C, Oracle kernel build-in based, you could now use the XMLTABLE function, to handle it further. XMLTABLE is a great function to make convert XML in a more relational in look-and-feel and / or because it supports XQuery version 1.0 (and as you (should) know XQuery is a very powerful query language). Anyway, via XMLTABLE, we can now show data in a relational form is demonstrated via the following:

SQL> SELECT xtab.owner
  2         , xtab.schema_url
  3         , xtab.LOCAL
  4  FROM (SELECT xmltype(bfilename('TMPDIR','otn_dev_xsd_schema.xml'), nls_charset_id('AL32UTF8')) OBJECT_VALUE FROM dual) xfile
  5        , XMLTABLE ('/ROWSET/ROW'
  6        PASSING xfile.object_value
  7        COLUMNS OWNER      VARCHAR2(30) path 'OWNER'
  8              , SCHEMA_URL VARCHAR2(80) path 'SCHEMA_URL'
  9              , LOCAL      VARCHAR2(3)  path 'LOCAL'
 10        ) xtab
 11  ;
 
OWNER                          SCHEMA_URL                                                                       LOC
------------------------------ -------------------------------------------------------------------------------- ---
EXFSYS                         http://xmlns.oracle.com/rlmgr/rclsprop.xsd                                       NO
EXFSYS                         http://xmlns.oracle.com/rlmgr/rulecond.xsd                                       NO
MDSYS                          http://www.opengis.net/gml/feature.xsd                                           NO
MDSYS                          http://xmlns.oracle.com/spatial/georaster/georaster.xsd                          NO
MDSYS                          http://www.w3.org/1999/xlink/xlinks.xsd                                          NO
MDSYS                          http://www.opengis.net/gml/geometry.xsd                                          NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/preference_1_0                                 NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/mapping_1_0                                    NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/metadata_1_0                                   NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/constraint_1_0                                 NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/anonymity_1_0                                  NO
ORDSYS                         http://xmlns.oracle.com/ord/meta/xmp                                             NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0                         NO
ORDSYS                         http://xmlns.oracle.com/ord/meta/iptc                                            NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/mddatatype_1_0                                 NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/orddicom_1_0                                   NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/datatype_1_0                                   NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0                          NO
ORDSYS                         http://xmlns.oracle.com/ord/meta/exif                                            NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0                                 NO
ORDSYS                         http://xmlns.oracle.com/ord/meta/ordimage                                        NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0                              NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/manifest_1_0                                   NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/attributeTag_1_0                               NO
...
XDB                            http://xmlns.oracle.com/xdb/XDBStandard.xsd                                      NO
XDB                            http://xmlns.oracle.com/xdb/XDBResConfig.xsd                                     NO
XDB                            http://xmlns.oracle.com/xdb/acl.xsd                                              NO
XDB                            http://xmlns.oracle.com/xdb/dav.xsd                                              NO
 
51 rows selected.

…and due to the fact you can query it, you can create a view out of it…

SQL> CREATE OR REPLACE VIEW MY_EXTERNAL_XFILE
  2  ( OWNER
  3  , SCHEMA
  4  , MINE
  5  )
  6  AS
  7  SELECT xtab.owner
  8       , xtab.schema_url
  9       , xtab.LOCAL
 10  FROM (SELECT xmltype(bfilename('TMPDIR','otn_dev_xsd_schema.xml'), nls_charset_id('AL32UTF8')) OBJECT_VALUE FROM dual) xfile
 11     , XMLTABLE ('/ROWSET/ROW'
 12                 PASSING xfile.object_value
 13                 COLUMNS OWNER      VARCHAR2(30) path 'OWNER'
 14                       , SCHEMA_URL VARCHAR2(80) path 'SCHEMA_URL'
 15                       , LOCAL      VARCHAR2(3)  path 'LOCAL'
 16                 ) xtab
 17  ;
 
VIEW created.
 
SQL> DESC my_external_xfile
 Name                                      NULL?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 SCHEMA                                             VARCHAR2(80)
 MINE                                               VARCHAR2(3)
 
SQL> SELECT * FROM MY_EXTERNAL_XFILE
  2  ;
 
OWNER                          SCHEMA                                                                           MIN
------------------------------ -------------------------------------------------------------------------------- ---
EXFSYS                         http://xmlns.oracle.com/rlmgr/rclsprop.xsd                                       NO
EXFSYS                         http://xmlns.oracle.com/rlmgr/rulecond.xsd                                       NO
MDSYS                          http://www.opengis.net/gml/feature.xsd                                           NO
MDSYS                          http://xmlns.oracle.com/spatial/georaster/georaster.xsd                          NO
MDSYS                          http://www.w3.org/1999/xlink/xlinks.xsd                                          NO
MDSYS                          http://www.opengis.net/gml/geometry.xsd                                          NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/preference_1_0                                 NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/mapping_1_0                                    NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/metadata_1_0                                   NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/constraint_1_0                                 NO
ORDSYS                         http://xmlns.oracle.com/ord/dicom/anonymity_1_0                                  NO
...
XDB                            http://xmlns.oracle.com/xdb/XDBStandard.xsd                                      NO
XDB                            http://xmlns.oracle.com/xdb/XDBResConfig.xsd                                     NO
XDB                            http://xmlns.oracle.com/xdb/acl.xsd                                              NO
XDB                            http://xmlns.oracle.com/xdb/dav.xsd                                              NO
 
51 rows selected.

So now we have a “standard” relational view that we can query, for example, show me all “w3″ based in the XDB Repository registered XML Schemas which are not mine, but accessible for all in the database, via…

SQL> SELECT * FROM MY_EXTERNAL_XFILE WHERE owner='XDB'
  2  AND instr(SCHEMA,'w3') > 0
  3  ;
 
OWNER                          SCHEMA                                                                           MIN
------------------------------ -------------------------------------------------------------------------------- ---
XDB                            http://www.w3.org/2001/csx.xml.xsd                                               NO
XDB                            http://www.w3.org/2001/csx.XInclude.xsd                                          NO
XDB                            http://www.w3.org/2001/XInclude.xsd                                              NO
XDB                            http://www.w3.org/1999/csx.xlink.xsd                                             NO
XDB                            http://www.w3.org/1999/xlink.xsd                                                 NO
XDB                            http://www.w3.org/2001/xml.xsd                                                   NO
 
6 rows selected.

Price to pay…?

As said, it comes with a price to pay, regarding I/O and other performance issues. For example check out the following stats, initially via “explain plan”…

SQL> conn / AS sysdba
Connected.
 
SQL> startup 
ORACLE instance started.
 
Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             251661400 bytes
DATABASE Buffers           54525952 bytes
Redo Buffers                6336512 bytes
DATABASE mounted.
DATABASE opened.
 
SQL> SELECT * FROM v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS FOR Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
 
SQL> SET autotrace ON
 
SQL> SELECT * FROM MY_EXTERNAL_XFILE WHERE owner='MDSYS'
  2  AND instr(SCHEMA,'w3') > 0;
 
OWNER                          SCHEMA                                                                           MIN
------------------------------ -------------------------------------------------------------------------------- ---
MDSYS                          http://www.w3.org/1999/xlink/xlinks.xsd                                          NO
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2888251253
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                        |     4 |     8 |    44  (30)| 00:00:01 |
|   1 |  NESTED LOOPS                      |                        |     4 |     8 |    44  (30)| 00:00:01 |
|   2 |   FAST DUAL                        |                        |     1 |       |     2   (0)| 00:00:01 |
|*  3 |   COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |     4 |     8 |    42  (31)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   3 - filter(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,209715
              20,0),50,1,2) AS VARCHAR2(30) )='MDSYS' AND INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VA
              LUE(KOKBF$),'/ROW/SCHEMA_URL'),0,0,20971520,0),50,1,2) AS VARCHAR2(80) ),'w3')>0)
 
 
Statistics
----------------------------------------------------------
       3624  recursive calls
          0  db block gets
       2888  consistent gets
        105  physical reads
          0  redo size
        583  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
        143  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL> SELECT * FROM MY_EXTERNAL_XFILE WHERE owner='MDSYS'
  2  AND instr(SCHEMA,'w3') > 0;
 
OWNER                          SCHEMA                                                                           MIN
------------------------------ -------------------------------------------------------------------------------- ---
MDSYS                          http://www.w3.org/1999/xlink/xlinks.xsd                                          NO
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2888251253
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                        |     4 |     8 |    44  (30)| 00:00:01 |
|   1 |  NESTED LOOPS                      |                        |     4 |     8 |    44  (30)| 00:00:01 |
|   2 |   FAST DUAL                        |                        |     1 |       |     2   (0)| 00:00:01 |
|*  3 |   COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |     4 |     8 |    42  (31)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   3 - filter(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,209715
              20,0),50,1,2) AS VARCHAR2(30) )='MDSYS' AND INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VA
              LUE(KOKBF$),'/ROW/SCHEMA_URL'),0,0,20971520,0),50,1,2) AS VARCHAR2(80) ),'w3')>0)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        583  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

…and a second time via the same “procedure” checking via the stats via DBMS_XPLAN…

SQL> startup force
ORACLE instance started.
 
Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             251661400 bytes
DATABASE Buffers           54525952 bytes
Redo Buffers                6336512 bytes
DATABASE mounted.
DATABASE opened.
SQL> SELECT * FROM v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS FOR Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
 
SQL> conn otn/otn
Connected.
 
SQL> ALTER session SET statistics_level=ALL;
 
Session altered.
 
SQL> CREATE OR REPLACE VIEW xplan AS SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
 
VIEW created.
 
SQL> SELECT * FROM MY_EXTERNAL_XFILE WHERE owner='MDSYS'
  2  AND instr(SCHEMA,'w3') > 0;
 
SQL> SELECT * FROM MY_EXTERNAL_XFILE WHERE owner='MDSYS'
  2  AND instr(SCHEMA,'w3') > 0;
 
OWNER                          SCHEMA                                                                           MIN
------------------------------ -------------------------------------------------------------------------------- ---
MDSYS                          http://www.w3.org/1999/xlink/xlinks.xsd                                          NO
 
 
SQL> SELECT * FROM xplan;
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9kc32mbtas765, child number 0
-------------------------------------
SELECT * FROM MY_EXTERNAL_XFILE WHERE owner='MDSYS' AND
instr(SCHEMA,'w3') > 0
 
Plan hash value: 2888251253
 
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                        |      1 |        |      1 |00:00:00.09 |     959 |     52 |
|   1 |  NESTED LOOPS                      |                        |      1 |      4 |      1 |00:00:00.09 |     959 |     52 |
|   2 |   FAST DUAL                        |                        |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |
|*  3 |   COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |      1 |      4 |      1 |00:00:00.08 |     959 |     52 |
--------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   3 - filter((CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,20971520,0),50,1,2) AS
              VARCHAR2(30) )='MDSYS' AND INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/SCHEMA_URL'),0,0,2
              0971520,0),50,1,2) AS VARCHAR2(80) ),'w3')>0))
 
 
23 rows selected.
 
SQL> SELECT * FROM MY_EXTERNAL_XFILE WHERE owner='MDSYS'
  2  AND instr(SCHEMA,'w3') > 0;
 
OWNER                          SCHEMA                                                                           MIN
------------------------------ -------------------------------------------------------------------------------- ---
MDSYS                          http://www.w3.org/1999/xlink/xlinks.xsd                                          NO
 
SQL> SELECT * FROM xplan;
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID  9kc32mbtas765, child number 0
-------------------------------------
SELECT * FROM MY_EXTERNAL_XFILE WHERE owner='MDSYS' AND
instr(SCHEMA,'w3') > 0
 
Plan hash value: 2888251253
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                        |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  NESTED LOOPS                      |                        |      1 |      4 |      1 |00:00:00.01 |       3 |
|   2 |   FAST DUAL                        |                        |      1 |      1 |      1 |00:00:00.01 |       0 |
|*  3 |   COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |      1 |      4 |      1 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   3 - filter((CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,20971520,0),50,
              1,2) AS VARCHAR2(30) )='MDSYS' AND INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/S
              CHEMA_URL'),0,0,20971520,0),50,1,2) AS VARCHAR2(80) ),'w3')>0))
 
 
23 rows selected.

As said before, initially Oracle has to do a lot of disk reads and all has to be done via “COLLECTION ITERATOR PICKLER FETCH”, which means in memory, but although in memory, Oracle is able to rewrite it via the XQuery engine (mark the “XQ” in the filter statements) to their relational counter parts due to the fact that Oracle got this information via the XMLTABLE function definition.

The second time around everything has been cached. See the following output via all statistics / DBMS_XPLAN…

SQL> SELECT * FROM MY_EXTERNAL_XFILE WHERE owner='XDB'
  2  AND instr(SCHEMA,'w3') = 0
  3  ;
 
OWNER                          SCHEMA                                                                           MIN
------------------------------ -------------------------------------------------------------------------------- ---
XDB                            http://xmlns.oracle.com/xdb/csx.xmltr.xsd                                        NO
XDB                            http://xmlns.oracle.com/xdb/XDBResource.xsd                                      NO
XDB                            http://xmlns.oracle.com/xdb/XDBSchema.xsd                                        NO
XDB                            http://xmlns.oracle.com/xs/principal.xsd                                         NO
XDB                            http://xmlns.oracle.com/xs/aclids.xsd                                            NO
XDB                            http://xmlns.oracle.com/xs/dataSecurity.xsd                                      NO
XDB                            http://xmlns.oracle.com/xdb/xdbconfig.xsd                                        NO
XDB                            http://xmlns.oracle.com/xs/securityclass.xsd                                     NO
XDB                            http://xmlns.oracle.com/xs/roleset.xsd                                           NO
XDB                            http://xmlns.oracle.com/xdb/stats.xsd                                            NO
XDB                            http://xmlns.oracle.com/xdb/XDBFolderListing.xsd                                 NO
XDB                            http://xmlns.oracle.com/xdb/xmltr.xsd                                            NO
XDB                            http://xmlns.oracle.com/xdb/log/httplog.xsd                                      NO
XDB                            http://xmlns.oracle.com/xdb/log/ftplog.xsd                                       NO
XDB                            http://xmlns.oracle.com/xdb/log/xdblog.xsd                                       NO
XDB                            http://xmlns.oracle.com/xdb/XDBStandard.xsd                                      NO
XDB                            http://xmlns.oracle.com/xdb/XDBResConfig.xsd                                     NO
XDB                            http://xmlns.oracle.com/xdb/acl.xsd                                              NO
XDB                            http://xmlns.oracle.com/xdb/dav.xsd                                              NO
 
19 rows selected.
 
SQL> SELECT * FROM xplan;
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bjx9wfv2hp6u7, child number 0
-------------------------------------
SELECT * FROM MY_EXTERNAL_XFILE WHERE owner='XDB' AND
instr(SCHEMA,'w3') = 0
 
Plan hash value: 2888251253
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                        |      1 |        |     19 |00:00:00.03 |       3 |
|   1 |  NESTED LOOPS                      |                        |      1 |      1 |     19 |00:00:00.03 |       3 |
|   2 |   FAST DUAL                        |                        |      1 |      1 |      1 |00:00:00.01 |       0 |
|*  3 |   COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |      1 |      1 |     19 |00:00:00.03 |       3 |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   3 - filter((CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,20971520,0),50,
              1,2) AS VARCHAR2(30) )='XDB' AND INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/SCH
              EMA_URL'),0,0,20971520,0),50,1,2) AS VARCHAR2(80) ),'w3')=0))
 
 
23 rows selected.
 
SQL> SELECT owner, count(*)
  2  FROM    MY_EXTERNAL_XFILE
  3   WHERE  instr(SCHEMA,'xmlns.oracle.com') > 0
  4  GROUP BY owner;
 
OWNER                            COUNT(*)
------------------------------ ----------
MDSYS                                   1
EXFSYS                                  2
ORDSYS                                 19
XDB                                    19
SYS                                     1
 
SQL> SELECT * FROM xplan;
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5sqvj1k6pmmxj, child number 0
-------------------------------------
SELECT owner, count(*) FROM    MY_EXTERNAL_XFILE  WHERE
instr(SCHEMA,'xmlns.oracle.com') > 0 GROUP BY owner
 
Plan hash value: 2770558904
 
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |      1 |        |      5 |00:00:00.03 |       3 |       |       |          |
|   1 |  HASH GROUP BY                      |                        |      1 |    408 |      5 |00:00:00.03 |       3 |   855K|   855K|  917K (0)|
|   2 |   NESTED LOOPS                      |                        |      1 |    408 |     42 |00:00:00.02 |       3 |       |       |          |
|   3 |    FAST DUAL                        |                        |      1 |      1 |      1 |00:00:00.01 |       0 |       |       |          |
|*  4 |    COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |      1 |    408 |     42 |00:00:00.02 |       3 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   4 - filter(INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/SCHEMA_URL'),0,0,20971520,0),50,1,2) AS
              VARCHAR2(80) ),'xmlns.oracle.com')>0)
 
 
23 rows selected.

Who knows with a little bit of fantasy, me or you, could find a case this could be applied and makes sense. This example was based on the following OTN Thread: “Error with basic XMLTable” and its probably easier to read up it over there. Hope you liked it.

]]> http://www.liberidu.com/blog/?feed=rss2&p=2194 0 http://www.liberidu.com/blog/?p=2194 Advert: The Michigan OakTable Symposium http://feedproxy.google.com/~r/Bloggralikecom/~3/_x6i_4eaXhg/ http://www.liberidu.com/blog/?p=2177#comments Thu, 29 Jul 2010 12:41:58 +0000 Marco Gralike http://www.liberidu.com/blog/?p=2177 Lets say you can’t make it to those presentations of Oracle Open World this year and you also hadn’t the budget to come to Europe to see really (technically) in depth, practice driven, great (probably new) views on your performance and architecture work at work during Miracle Open World, then there is a great alternative: The Michigan OakTable Symposium.

Its “only” a 2 day symposium but you have a chance there to come up personal, discuss issues during presentations and on site, for instance in the lobby, with some of the top people in their field like, among others, Jonathan Lewis, Tanel Poder or Cary Millsap.

I really like those mini-conferences, because they always bring me new ideas how to solve problems at work or stuff that I am dealing with in my mind, seeing them from a new perspective or get new info and techniques, involving Oracle software, I didn’t know about yet…

During this years The Michigan OakTable Symposium you have to chance to enjoy Cary’s extended version (2 slots, one on Tuesday and one on Friday) of his “Thinking Clearly About Performance” presentation that won this year’s ODTUG Editor’s Choice Award. Anyway, almost all presenters during this 2 day event are Oracle ACE’s, ACE Directors or the top in their field and really have something to say…

  • Christian Antognini
  • Mark Farnham
  • Randolph Geist
  • Alex Gorbachev
  • Tim Gorman
  • Marco Gralike
  • Eric Grancher
  • Jonathan Lewis
  • Cary Millsap
  • Doug Burns
  • Jeff Needham
  • Mogens Norgaard
  • Tanel Poder
  • Tuomas Pystynen
  • Robyn Sands
  • Joze Senegacnik
  • Riyaj Shamsudeen
  • Chen Shapira
  • Jeremiah Wilton
  • Andrew Zitelli

Their biographies of, I guess probably almost combined 200+ years of practical IT experience, can be found here:

As Mogens Norgaard said it during his remarks (see the video on Miracle Channel) on the new Second OakTable book called “Expert Oracle Practices: Oracle Database Administration from the Oak Table” that those “new” people really made a better book than the first one, they know their stuff…

Anyway, in short, I suggest to have a look at the MOTS agenda yourself:

See you there? If your not convinced look them up on the internet or via the OakTable aggregated www.oaktable.net blog site.

8-)

]]> http://www.liberidu.com/blog/?feed=rss2&p=2177 0 http://www.liberidu.com/blog/?p=2177 XMLDB Oracle Open World Agenda… http://feedproxy.google.com/~r/Bloggralikecom/~3/qx7H4Jzfoy0/ http://www.liberidu.com/blog/?p=2164#comments Thu, 29 Jul 2010 10:48:19 +0000 Marco Gralike http://www.liberidu.com/blog/?p=2164 Becoming a bit of a tradition actually…

Trying for others to avoid the same, at least it was in 2008/2009, ordeal going thru the O.O.W. Schedule / Content builder trying to find XMLDB topics, I listed those I could find on the XMLDB OTN Forum. As said, trying to follow up on a tradition and to get myself (and hopefully you) an overview on things to come during Oracle Open World 2010 (/Oracle Develop /JavaOne). This year I think its becoming BIG regarding amounts /attendance of people if not only due to the combination of O.O.W./Develop/JavaOne on the same spot in San Francisco…

So, for me and those who are interested, just like the year before, hereby an attempt to find all XMLDB related presentations, workshops and other events during Oracle Open World 2010. I will try to add info, time and days later on (and/or you might) if I find them and/or if they become known. See for XMLDB presentations and Hands-on Lab sessions here:

I hope you enjoy your presentations during O.O.W. and who knows we meet this year.

Not really into XMLDB presentations? Have a look at the 50+ Oracle ACE(D) presentations Oracle Open World and JavaOne, Oracle Develop listing here…

]]> http://www.liberidu.com/blog/?feed=rss2&p=2164 0 http://www.liberidu.com/blog/?p=2164 XMLDB Whitepapers and Tooling about Design, Performance and Selectivity http://feedproxy.google.com/~r/Bloggralikecom/~3/g1wtB20WhFE/ http://www.liberidu.com/blog/?p=2140#comments Thu, 15 Jul 2010 20:06:33 +0000 Marco Gralike http://www.liberidu.com/blog/?p=2140 From time to time the main Oracle XML DB page gets updated with new whitepapers, tooling or Oracle By Example/ Hands-on Lab examples. “Lately” some cool and interesting new whitepapers and updated tooling content were created on this main Oracle XML DB page. The following items and content are really worth reading. Small issue, though, is that you need a bit more than basic understanding to put all this “lessons learned from the last one, two years” into context, but its worth it and otherwise a small reprise on the Oracle XML DB Developers Guide is always useful. A bit like re-reading the Oracle Concepts Manual.

The “Ease of Use Tools” (xdbutilities.zip tool set) for handling XMLType Object Relational storage has been updated and is now applicable on Oracle 10.x and 11.x. No specific to be installed versioned tool set needed anymore. This prepacked tool set on PL/SQL packages is installable on both versions. The zip file also contains a whitepaper that describes some of the (performance) lessons learned while using XMLType Object Relational storage.

“Choosing the Best XMLType Storage Option for Your Use Case” gives you a good overview on what to choose regarding design / supporting structure principles and performance indications on what works best for your XML document.

“Best Practices to Get Optimal Performance out of XML Queries” has been written with the other two in mind. The principles and design decisions are the basis on which the XPath and XQuery solutions are explained. You will have to have a good understanding of the other two to effective implement the advice given in this whitepaper. In all a good whitepaper with lots of lessons learned and a bit of inside information if you can read between the lines on how things tick in XML DB land.

Very useful information if you seek a bit more detailed information after the Oracle XML DB Developers Guide doesn’t supply all the answers anymore.

M.

]]> http://www.liberidu.com/blog/?feed=rss2&p=2140 0 http://www.liberidu.com/blog/?p=2140 Starting a Holiday (/Presentation) at #ODTUG http://feedproxy.google.com/~r/Bloggralikecom/~3/HZPdQLeWJnQ/ http://www.liberidu.com/blog/?p=2095#comments Sat, 26 Jun 2010 15:00:42 +0000 Marco Gralike http://www.liberidu.com/blog/?p=2095 Time flies. Had a great weekend organizing an AMIS Query, an internal course with Doug Burns, looking at soccer games with Doug and Jacco and now already posting from a Marriott hotel room in Washington DC for some activities during #ODTUG

room / meanings

Its still a bit “quiet” (tons of elderly citizen though) in the Marriott hotel regarding conference meet-ups and/or other things, but met last night with Stanley, Debra, Alex, David (Mr. aPex) and for the first time with Sheeri (Kritzer Cabral) and her husband. Forgot his name (after 2 Guinness and pushing 36 hours “to get into the timezone”) but, man, can he draw cool “caricatures“. He had his own caricature book with him reflecting pictures of Magicians during a “Magicians Conference”, all signed by them and/or commented on. Great book/sketches and had a good laugh on how modern “Magicians” are nowadays that they have their annual conference as well…

Its Saturday now, its hot outside and humid (cloudy) and approximately 35C (95F) and I’m almost off with my nephew Rob, who I dragged along this time (if not only for those soccer matches), to see Washington DC. At least I don’t have to prove to my nephews Chris and Rob that I have been there by jumping with my trousers down on the White House lawn and doing so making a Dutch TV premier (getting to old for that stuff anyway).

Oh yeah, almost forgot, its the start of my Holiday, on Wednesday my ODTUG presentation and for the most just enjoying Washington DC before I leave with my daughter towards Switzerland in 2 weeks time.

;-)

]]> http://www.liberidu.com/blog/?feed=rss2&p=2095 0 http://www.liberidu.com/blog/?p=2095