Tag: XQuery

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…

March 24

I just saw Daniel Fink’s – OptimalDBA – blog post: When is a sql statement too long? When the following OTN XMLDB Forum post popped up in my mind… “xquery” versus “select xmlquery” and passing clauses. It tells the story about using bind variables and its performance issues, some alternative ways of dealing with things, charactersets and the ORA-19102 error (“XQuery string literal expected”), ORA-19114 (“Error during parsing the XQuery expression: string”) or ORA-01704 (“String literal too long”).

So when is a XQuery string too long…?

August 2