Month: July 2010

July 30

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:

Read the Post C based XML tools in your $ORACLE_HOME

July 29

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…

Read the Post External Views (XML based)

July 29