Category: SQL, XML/SQL, XPath, XQuery

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"?>

  
    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
  
  
...
  
    XDB
    http://xmlns.oracle.com/xdb/dav.xsd
    NO
  


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…