Oracle SQL*Plus XQUERY Command

In previous blog entries, I wrote about Oracle's support for XQuery with the XQLPlus XQuery command-line editor and Oracle's XQuery API for Java (XQJ) implementation (OJXQI). In this blog entry, I'm going to focus on using the SQL*Plus XQUERY command to simply run XQuery expressions in SQL*Plus. While this is not a standard command like the SQL/XML functions XMLQUERY and XMLTABLE, it is really easy to use and a nice way to do quick XQuery work in SQL*Plus.

The SQL*Plus User's Guide and Reference discusses use of SQL*Plus XQUERY in both the Release 10.2 and Release 11.1 (11.1 PDF) versions of this document. In this blog entry, I'll show some examples that demonstrate the topics discussed in that manual and add some details that I have not seen documented in either version.

The SQL*Plus XQUERY command can be executed without any interaction with database tables or views because XQuery expressions do no necessary rely upon underlying database or even XML sources to run properly. For example, the next screen shot (click on the image to see a larger version of it), shows a simple XQuery expression run with the XQUERY command in SQL*Plus and the XQuery result expressed.



Note that the XQuery expression shown in the example above does not rely on any data from the database. For this blog entry, to keep things simple, I'll use XQuery expressions that do not interact with the database. There are many examples in the Oracle documentation that show how to use SQL*Plus XQUERY to interact with the database. I want to focus in this blog on the configurable settings available for XQUERY instead.

The next screen shot (click on it to see larger version) demonstrates a perfectly valid XQuery expression being supplied via the XQUERY command, but the result does not show the full entered expression.



To see the entire results of the XQuery expression in the example above, the SQL*Plus setting LONG needs to be set appropriately. Note that this setting is not specific to XQuery or XQUERY, but is instead a standard setting in SQL*Plus. The next screen show shows how using SET LONG can overcome the problem. In this case, I am setting LONG to 200, enough for this simple example. The default for this setting is so low that the documentation recommends that this LONG setting usually needs to be set for using XQUERY.



Similarly to the use of SET LONG shown above, other standard SQL*Plus settings can be used in conjunction with the XQUERY command. For example, SET LINESIZE can be used for individual lines that are longer than the default of eighty characters. Likewise, SET PAGESIZE can also be used.

A standard SQL*Plus setting that I particularly like using with my XQUERY commands is the command SET HEADING OFF. As with other non-XQUERY SQL*Plus queries, the setting of HEADING to OFF removes the printed header on top of the returned query results. In the case of the XQUERY command, the normally returned header (as shown in the screen shots above) is "Result Sequence" with a line separator formed by use of consecutive hyphens (---). As the next screen shot demonstrates, this heading is turned off with SET HEADING OFF.



There may be times where we wish to keep the header (not use SET HEADING OFF or explicitly use SET HEADING ON), but wish to change the heading from "Result Sequence" to something of our own choosing. The 10.2 SQL*Plus User's Guide and Reference states that the heading for the returned XQUERY results can be changed using the COLUMN command with the column column_value being the one whose HEADING is changed. However, I cannot find any column_value column in the 11.1 database, nor do I see any mention of changing this header in the 11.1 SQL*Plus User's Guide and Reference.

Fortunately, it is easy to determine which column needs to have its heading changed to print out our desired heading on XQUERY results. The simple way to determine this is to enter COLUMN (lowercase or uppercase) in SQL*Plus without any arguments and look through the list of set columns for a likely candidate. The next screen snapshot shows the relevant portion of this.



We don't have to look long in the results returned from running column in SQL*Plus without arguments to see the appropriate column to set. It is the first one returned! Based on this, we can see result_plus_xquery to display any heading we like. To do this, we can use either

column result_plus_xquery heading "We like XQUERY!"

or

column result_plus_xquery heading "We like XQUERY!" ON

The first shown command will only lead to the designated heading be printed above XQUERY results if the heading happens to be turned on already (the default). However, the second command does the same thing and ensures that the heading will be printed by turning it on.

The next screen snapshot demonstrates use of the second version. Note how the heading is shown again, but is no longer the default "Result Sequence," but is instead our specified custom heading "We like XQUERY!"

Up to this point, I have only used standard SQL*Plus settings to change how the XQUERY command displays its results in SQL*Plus. There are also XQUERY-specific settings as well. Due to the rather sizable length of this entry already, I'll only list these XQUERY-specific SQL*Plus settings here and may discuss them in greater details in the future (though the documentation covers them fairly thoroughly already).

The XQUERY-specific SQL*Plus settings are BASEURI, ORDERING, NODE, and CONTEXT. These are each set by starting the set command with SET XQUERY and then supplying one of the four specific setting names and its value to be set. The default settings for these four XQUERY-specific SQL*Plus settings can be seen with the SHOW XQUERY command as demonstrated in the next screen snapshot.



As the above screen snapshot indicates, BASEURI and CONTEXT have empty strings as their defaults. You must go to the documentation to find out what DEFAULT means for the other two settings. The default setting for NODE is BYVALUE, meaning that newly created nodes will not maintain identity and will be treated as completely new nodes. The default setting for ORDERING is UNORDERED, which means that XQuery results are in the sorted order they existed in the database rather than the sort order specified in the XQuery expression.

The SQL*Plus XQUERY command provides a non-standard but simple method for evaluating XQuery expressions and thus is a complementary tool to XQLPlus. I tend to prefer SQL*Plus XQUERY for XQuery expressions against the Oracle database and prefer XQLPlus for XQuery expressions against non-database resources such as external XML files on the file system.

2007: A Great Year for Software Developers

The year 2007 was generally a good year to be a software developer. There were many advances this year that made our lives as software developers easier and made us more productive. At the same time, many 2007 developments made our work more enjoyable and brought back excitement into what we do. The following list is my top ten events, announcements, or occurrences of 2007 in the software development world. It would be impossible to say what the ten biggest events in general for the software development world were in 2007, so this entry instead focuses on the biggest events for me personally. I welcome any suggestions for other major 2007 events in the feedback section.

10. Oracle Releases TopLink as Open Source (6 March 2007)

Oracle announced the release of its commercial object-relational mapping (ORM) TopLink product into the open source community as part of the Eclipse Persistence Platform. Oracle had already contributed TopLink Essentials to the open source community as the reference implementation of the Java Persistence API (JPA), but this announcement made an even more powerful JPA-compliant ORM solution available to open source users.

9. Bruce Eckel's Hybridizing Java (30 January 2007)

There were several key factors in the rapid adoption of Flex and OpenLaszlo as rich web client development platforms. This Bruce Eckel article was certainly one of those factors. In this entry, Bruce articulately outlines many of the frustrations and limitations many of us have run into trying to develop Ajax-based web applications on Java EE platforms. While others had been talking and writing about the beauty of Flex and Java EE together before this, it was this article that was the first to break my previous perceptions about Flash and caused me to really think about Flex as a potential development environment. When the entry was reprinted as How and Why Ajax, and Not Java, Became the Favored Technology for Rich Internet Applications, the article got even more coverage and I think many more developers got their first glimpse of the power of using Flex on the web client side with Java EE on the back-end. It helps when the man who thinks in Java (and C++ and Python) puts his name behind an idea.

8. Adobe Releases BlazeDS (12 December 2007)

Flex provided a compelling client-size solution for communicating with Java EE back-ends even before the BlazeDS announcement via its HTTPService and WebService support. However, there are times when communication mechanisms other than HTTP servlets or web services are desired. Previously, Adobe's Flex Data Services 2 was a commercial product available for alternative forms of communication. The BlazeDS announcement was welcome to many of us because it brought many of these server-side communication mechanisms out of the commercial only world and into the freely available and open source world. As part of this announcement, it was also announced that Action Message Format (AMF3) was being released. Additional information on these releases is available here.

7. Flash Player 9 Released for Linux (17 January 2007) [PDF]

There were several occurrences in 2007 that led to greater interest in Flash as a web platform. One of the most important characteristics of any web runtime platform is ubiquity on web browsers. Flash is a widely recognized and often downloaded platform, but Flex requires Flash 9. The announcement that the Flash 9 Player is available for Linux is an important piece of making Flash a compelling runtime environment to develop against.

6. XQuery 1.0, XPath 2.0, and XSLT 2.0 become W3C Recommendations (23 January 2007)

XQuery 1.0, XPath 2.0, and XSLT 2.0 progressed through the standardization process together and all three became W3C Recommendations early in 2007. I am particularly excited about the XQuery 1.0 progress because this means that vendors can start solidifying their XQuery implementations to a single standard. There is much to like about XQuery, but a specification is only valuable when it is a truly standardly implemented specification. Now, I am hoping that we will see Java Specification Request (JSR) 225 ("XQuery API for Java") (XQJ) finalized in 2008.

5. Oracle Releases JDeveloper 11g Technology Previews (May, September, and December 2007)

I have found that many of my colleagues do not realize that JDeveloper is a free IDE for Java, database, and many more types of development. I think part of this confusion is a result of Oracle formerly charging for JDeveloper and part of the confusion involves the whole "free beer versus free speech" and open source issue. While JDeveloper is NOT open source (not "free speech"), it is free of charge ("free beer"). I think that many developers think the only "free" (as in "free beer") Java IDEs are the two that happen to be open source ("free speech"): NetBeans and Eclipse.

I am not the only one who has observed that most Java developers are not aware that JDeveloper is freely available. Justin Kestelyn, Oracle Technology Network Editor-In-Chief, made this same observation at JavaOne 2007. This is a little surprising because it has been over two years since Oracle announced at JavaOne 2005 that JDeveloper would be free for all.

While JDeveloper supports non-Oracle products, it is especially handy when working with Java (SE and/or EE) applications that work with an Oracle database and OC4J/Oracle Application Server.

Oracle has released three "Technology Previews" of JDeveloper 11g in 2007 (Technology Preview 1 in May 2007, Technology Preview 2 in September 2007, and Technology Preview 3 on 21 December 2007). The new features associated with JDeveloper 11g Technology Preview 3 are listed here.

An interesting history of JDeveloper is available here.

Another interesting side note is that JDeveloper was the IDE of choice of the top three teams at JavaPolis 2005 Rapid Application Development (RAD) Race. Then, in JavaPolis RadRace 2006, one of the two winning teams used JDeveloper. One of the two teams to win the 2007 competition didn't use JDeveloper (but they did use Application Express).

4. OpenLaszlo 4.0 Released (19 March 2007)

Previously known as OpenLaszlo Legals, OpenLaszlo 4.0 introduced the ability to deploy OpenLaszlo applications against Flash or DHTML/Ajax environments with relatively minor code changes or even no code changes in simpler examples. OpenLaszlo features a declarative XML-based language (LZX) for coding the view/presentation in XML and uses a JavaScript subset (parts of JavaScript that do not have browser idiosyncrasies) to script behaviors and logic.

While OpenLaszlo's DHTML support has still not caught up with what it can do when deployed to Flash, it is nevertheless an amazing feat. For organizations cannot rely solely on Flash being on users' web browsers, OpenLaszlo's DHTML support may become what they need. OpenLaszlo provides a compelling alternative to using other Ajax/JavaScript frameworks and libraries. Also, OpenLaszlo has an advantage of not requiring Flash 9 (Flex does require that) and so may be more fitting for organizations whose customers may not be as likely to migrate to Flash 9 from Flash 7 or Flash 8 (the versions OpenLaszlo can deploy to). Finally, OpenLaszlo developers have already contributed to the betterment of the Ajax development community with articles and blog entries like the blog entry on dealing with web browser idiosyncrasies.

3. GlassFish v2 FCS Released (17 September 2007)

After upgrading several times to new beta releases of GlassFish v2, it was nice to have a final release to upgrade to. Since then, GlassFish v2 UR1 has been released (19 December 2007).

As the Java EE application server reference implementation, GlassFish is on the leading edge of Java EE application servers in terms of implementing Java EE 5 new features. It provides an easy-to-use web-based administrative console while still supporting command-line asdmin commands. It also provides Java EE developers with an easy way to test their Java EE applications for specification compliance. For example, I have used GlassFish to implement my first JAX-WS applications. In short, this Java EE reference implementation helps the entire Java EE development community.

2. Java SE 6 Released (11 December 2006)

Okay, I admit it, I am cheating on this one; Java SE 6 was actually released in late 2006. However, I figured that it was close enough to 2007 and its most significant impact for me was in 2007 to warrant including on this 2007 list. Also, several updates to Java SE 6 have become available during 2007.

There is much to like about Java SE 6 and I seem to run into new things I like about it at least on a monthly basis. Some of my favorite features of Java SE 6 include its built-in support for JAXB and annotations processing, its simple but satisfying String.isEmpty() method, its System.console() method and the ability to easily read user input from the console, its JAX-WS web services support, new JMX MXBeans, and its better performance. Many of the new and useful features that are now available with Java SE 6 are enumerated in What's New in Java SE 6?. Scripting for the JVM seems to be a very popular feature of this release of Java.

1. Adobe Open Sources Flex (26 April 2007) [PDF]

For me, the most significant software development event of 2007 was Adobe's open sourcing of its Flex 2 product. This, combined with Bruce Eckel's "Hybridizing Java" article (see #9 above) and the release of Flash Player 9 on major platforms (see #7 above) made Flex and Flash an obvious choice for developing Rich Internet Applications (RIAs) [or is it Rich Interactive Applications? :)] with Java EE on the back-end. Adobe's recent announcement regarding release of BlazeDS for other means of communication between Flex front-ends and Java EE back-ends solidifies this approach even more. I also really like OpenLaszlo (see #4 above) for many of the same reasons I like Flex. For me, the decision of what is the best web browser-based presentation solution for Java EE applications comes down to these two alternatives.

Honorable Mention

Listing only ten announcements of personal significance in the software development world necessarily meant bumping a few meaningful announcement or events. This section outlines some of them that didn't "make the cut," but will have the potential or even high probability of impacting many of us significantly in the future. Some of these may not have been as significant to me personally because I have not yet had the chance to use them thoroughly or because they have not yet proven themselves significant even if the potential is there currently.

The runners-up listed here are not listed in any particular order.

⇒ JavaFX (8 May 2007)

Based on F3, JavaFX was the biggest announcement of JavaOne 2007. This is saying something because JavaOne is known as a conference where major announcements are made related to the Java development community. Almost immediately, there was strong and heated discussion regarding this. Two interesting resources on this discussion are Java Champions Discuss JavaFX with Sun Microsystems and JavaFX Does Not Impress Anyone. It seems obvious that JavaFX is a response to Flash (and hence to Flex and OpenLaszlo) and Microsoft's Silverlight. Whether it will ever be able to seriously challenge them remains to be seen.

⇒ The Rise of the JVM Scripting Languages (throughout 2007)

I cheated a little bit and named the release of Java SE 6 as the second most significant software development event of 2007 to me personally. For many, the most interesting characteristic of Java SE 6 is its expanded support for languages other than Java, particularly scripting languages, to be run on the Java Virtual Machine (JVM). Martin Fowler compares two of the most popular, Groovy and JRuby, in his efficiently named entry GroovyOrJRuby. I also like the article Why Scripting Languages Matter. I really like Ruby, but I still haven't had a lot of use for a scripting language in my JVM ... yet.

⇒ Spring 2.5 Released (19 November 2007)

The Spring Framework has become so widely populated that any new significant version is likely to have a large impact on the Java SE/EE development community. Even those who don't use Spring directly, benefit from the obvious impact its had on the evolution of J2EE to Java EE. Spring has helped developers recognize the advantages of dependency injection and programming to simple Java classes rather than to complex classes implementing specific interfaces and/or inheriting from specific framework classes. Java EE has adopted many of these and other Spring-isms. I, of course, still like Spring even in my most simple Java SE applications if for no other reason than its JDBC handling.

⇒ Ruby on Rails 2.0 (7 December 2007)

⇒ New Versions of JFreeChart and Related Products (throughout 2007)

Several new versions of JFreeChart were released in 2007, including 1.0.5 (23 March 2007), 1.0.6 (15 June 2007) and 1.0.8 (23 November 2007). The Eastwood Chart Servlet project, based on JFreeChart and emulating Google Chart API, was also released in 2007 (13 December 2007). I like to use JFreeChart with OpenLaszlo and Flex, so these new versions of this free and open source (LGPL) Java charting API were welcome.

⇒ Google Collections

This extension of Java's standard collections support is only in version 0.5 (alpha) at this point, but it holds much promise.

⇒ Rejection of Microsoft Office Open XML as a Standard

⇒ Stalling of ECMAScript Edition 4 Progress

XQJ: JDBC for XQuery

In recent blog entries, I have posted about using Oracle's XQLPlus command-line XQuery tool. In one of those entries, I pointed out that if XQuery is to XML what SQL is to relational data, then XQLPlus is to XQuery what SQL*Plus is to SQL.

In this blog entry, I intend to demonstrate that we can take these relationships even further and describe the XQuery API for Java (XQJ) as XQuery's JDBC. In other words, XQJ is to XQuery as JDBC is to SQL. Both XQJ and JDBC are Java APIs for accessing data in a standard way. While XQJ is focused on XQuery access of XML data, JDBC is focused on SQL access of relational data. XQJ is currently a work-in-progress as part of Java Specification Request (JSR) 225 ("XQuery API for Java").

As work on JSR 225 precedes, several vendors have made XQJ implementations available. These include Oracle's OJXQI implementation and DataDirect's DataDirect XQuery implementation. DataDirect provides a highly useful XQJ Tutorial (PDF) and DevX provides a brief but useful example of using Oracle's XQJ implementation.

In this blog entry, I use the Oracle XQJ implementation to read an XQuery script file (with an .xql extension in my example) and process it. That processed script file references the source XML document it queries via the fn:doc function. This example does not obtain a connection, which is sometimes a necessary step (such as when querying over XML stored in the database).

The source XML that will be queried for this example is stored in a file called C:\xquery\xmlSource\planets.xml. This sample XML file is shown next.

<?xml version = '1.0'?>
<!-- Note that all data here is not meant to be factual, but is instead intended
     to illustrate XQuery principles.  Also, some of the planets have far too
     many moons to list all of them here, so only select moons are listed in
     those cases. For example, while Jupiter has over 60 moons, only its four
     so-called Galilean moons (the four largest that Galileo could see with the
     available equipment at the time) are listed here. -->
<Planets>
  <Planet name="Mercury"
          minDistanceFromSunMK="46"
   maxDistanceFromSunMK="70"
   class="planet"/>
  <Planet name="Venus"
          minDistanceFromSunMK="108"
   maxDistanceFromSunMK="109"
   class="planet" />
  <Planet name="Earth"
          minDistanceFromSunMK="146"
   maxDistanceFromSunMK="152"
   class="planet">
    <Moons>
      <Moon>The Moon</Moon>
    </Moons>
  </Planet>
  <Planet name="Mars"
          minDistanceFromSunMK="205"
   maxDistanceFromSunMK="249"
   class="planet">
    <Moons>
      <Moon>Phobos</Moon>
      <Moon>Deimos</Moon>
    </Moons>
  </Planet>
  <Planet name="Jupiter"
          minDistanceFromSunMK="741"
   maxDistanceFromSunMK="817"
   class="planet">
    <Moons>
      <Moon>Callisto</Moon>
      <Moon>Europa</Moon>
      <Moon>Ganymede</Moon>
      <Moon>IO</Moon>
    </Moons>
  </Planet>
  <Planet name="Saturn"
          minDistanceFromSunMK="1350"
          maxDistanceFromSunMK="1500"
         class="planet">
    <Moons>
      <Moon>Atlas</Moon>
      <Moon>Calypso</Moon>
      <Moon>Dione</Moon>
      <Moon>Prometheus</Moon>
      <Moon>Pan</Moon>
      <Moon>Pandora</Moon>
      <Moon>Titan</Moon>
    </Moons>
  </Planet>
  <Planet name="Uranus"
          minDistanceFromSunMK="2700"
          maxDistanceFromSunMK="3000"
         class="planet">
    <Moons>
      <Moon>Ariel</Moon>
      <Moon>Cordelia</Moon>
      <Moon>Desdemona</Moon>
      <Moon>Miranda</Moon>
      <Moon>Oberon</Moon>
      <Moon>Ophelia</Moon>
      <Moon>Puck</Moon>
      <Moon>Titania</Moon>
      <Moon>Umbriel</Moon>
    </Moons>
  </Planet>
  <Planet name="Neptune"
          minDistanceFromSunMK="4460"
          maxDistanceFromSunMK="4540"
          class="planet">
    <Moons>
      <Moon>Galatea</Moon>
      <Moon>Larissa</Moon>
      <Moon>Nereid</Moon>
      <Moon>Proteus</Moon>
      <Moon>Triton</Moon>
    </Moons>
  </Planet>
  <Planet name="Pluto"
          minDistanceFromSunMK="7376"
          maxDistanceFromSunMK="4437"
          class="dwarf">
    <Moons>
      <Moon>Charon</Moon>
      <Moon>Hydra</Moon>
      <Moon>Nix</Moon>
    </Moons>
  </Planet>
</Planets>

The XQuery script file to be used to query over the XML source shown above is stored in a different directory and its full path and name are C:\xquery\xqlScripts\extractIAUPlanets.xql. It is important to note that the XQL script and the XML source file are located in different directories because these differences will be reflected in the Java code that uses the XQL script to query the XML source. Here is the short contents of the extractIAUPlanets.xql file:

<IAUnionPlanets>
{for $i in doc("planets.xml")/Planets/Planet
where $i/@class = "planet"
return <Planet>{data($i/@name)}</Planet>}
</IAUnionPlanets>

This short XQuery script file will query over the XML source of planets (which includes Pluto as a planet) and will return only the planets that are still officially considered planets by the International Astronomical Union (IAU). In other words, poor Pluto gets filtered out because it is now a "dwarf planet" instead of a "planet."

Note in the five lines of XQuery script code in the above script, two lines are simply the new XML opening and closing tag for IAUnionPlanets. The other three lines are the more dynamic portion (hence the curly braces) and these only return the names of planets for planets which are of class "planet."

It is significant to note that the fn:doc function references the source XML file (planets.xml) without any path information. The XQuery script will only know where to look for the planets.xml XML source file if we tell the XQuery implementation what the base URI is. In Oracle's XQJ implementation, this is done with a oracle.xquery.Configuration.setBaseURI(String) call. That Configuration can then be passed to the oracle.xquery.XQueryContext.prepareXQuery() call shown in the Java code below. Note that this configuration base URI does not impact the location where the XQL script file will be found, but instead impacts where the XQL script file will look for the source XML file via the fn:doc function.

Here is the Java class (OracleXqjAccess.java) that uses Oracle's XQJ to invoke the XQuery script (C:\xquery\xqlScripts\extractIAUPlanets.xql) shown above on the XML source (C:\xquery\xmlSource\planets.xml) shown above. Here is the code for OracleXqjAccess.java:

package xqueryexamples;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;

import java.util.ArrayList;
import java.util.List;

import oracle.xml.parser.v2.XMLNode;
import oracle.xml.xqxp.datamodel.XMLItem;
import oracle.xml.xqxp.datamodel.XMLSequence;

import oracle.xquery.Configuration;
import oracle.xquery.PreparedXQuery;
import oracle.xquery.XQueryContext;

import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

/**
 * The main purpose of this class is to exercise Oracle's implementation of
 * XQuery API for Java (XQJ) [Java Specification Request 225].
 */
public class OracleXqjAccess
{
   /** XQueryContext. */
   private static XQueryContext context = new XQueryContext();

   /**
    * Default constructor accepting no arguments.
    */
   public OracleXqjAccess ()
   {
   }

   /**
    * Obtain XQueryContext for use in performing XQuery.
    * 
    * @return XQueryContext
    */
   private XQueryContext getXQueryContext()
   {
      if ( context != null )
      {
         return context;
      }
      else
      {
         context = new XQueryContext();
         return context;
      }
   }

   /**
    * Extract text content of non-root first-level XML elements returned from
    * provided XQuery script.
    * 
    * @param aXQueryScript XQuery script to be executed.
    * @param aBaseUri Base URI to be used for any documents looked up within
    *                 the provided aXQueryScript using the fn:doc function.
    * @return List of String associated with child nodes.
    */
   public List<String> extractSingleElementStringsFromFirstLevelElements
      ( final String aXQueryScript,
        final String aBaseUri )
   {
      final String mName = "runExampleXQueryFromFile(String,String)";
      Reader reader = null;
      List <String> childNodesList = new ArrayList<String>();
      try
      {
         final XQueryContext xqueryContext = getXQueryContext();
         final Configuration config = new Configuration();
         reader = new FileReader(aXQueryScript);
         System.out.println( mName + " - XQuery Script File: " + aXQueryScript );
         config.setXQueryOption(Configuration.XQUERY_NORMAL);
         config.setBaseURI(aBaseUri);
         System.out.println( mName + " - Provided Base URI: " + aBaseUri );
         PreparedXQuery preparedXQuery =
            xqueryContext.prepareXQuery(reader, config);
         XMLSequence xrs = preparedXQuery.executeQuery();
         while ( xrs.next() )
         {
            XMLItem xmlItem = xrs.getCurrentItem();
            final XMLNode xmlNode = xmlItem.getNode();
            NodeList childNodes = xmlNode.getChildNodes();
            final int numberChildNodes = childNodes.getLength();
            for ( int i=0; i < numberChildNodes; ++i )
            {
               final Node childNode = childNodes.item(i);
               childNodesList.add( childNode.getTextContent() );
            }
         }
      }
      catch (FileNotFoundException fnfEx)  // use of FileReader
      {
         System.err.println(  "Could not find file " + aXQueryScript + ": "
                            + fnfEx.getMessage() );
      }
      finally
      {
         if ( reader != null )
         {
            try
            {
               reader.close();
            }
            catch (IOException ioEx)
            {
               System.err.println(
                    mName
                  + " - Exception thrown while trying to close Reader."
                  + ioEx.getMessage() );
            }
         }
      }

      return childNodesList;
   }

   /**
    * Provide the full-fledged planets (not dwarf) endorsed by the IAU.
    * 
    * @return List of names of planets endorsed as planets by IAU.
    */
   public List<String> getIAUPlanets()
   {
      final String iauPlanetsScript =
         "C:\\xquery\\xqlScripts\\extractIAUPlanets.xql";
      final String baseURI = "file:///C:/xquery/xmlSource/";
      return extractSingleElementStringsFromFirstLevelElements(
         iauPlanetsScript, baseURI );
   }

   /**
    * Display provided list of strings.
    * 
    * @param aListTitle Title of the list to be displayed.
    * @param aListOfStrings List of Strings to be displayed.
    */
   public static void displayContentsOfList( final String aListTitle,
                                             final List<String> aListOfStrings )
   {
      System.out.println("----- " + aListTitle + " -----");
      for ( final String string : aListOfStrings )
      {
         System.out.println(string);
      }
   }

   /**
    * Main function.
    * 
    * @param aArgs Command-line arguments; none anticipated currently.
    */
   public static void main( final String[] aArgs )
   {
      OracleXqjAccess xqjAccess = new OracleXqjAccess();
      displayContentsOfList( "IAU Endorsed Planets",
                             xqjAccess.getIAUPlanets() );
   }
}

The XQJ-specific and closely related code is highlighted above. There are two System.out statements to display the path and file name of the XQL script file and to display the base URI used implicitly in the XQL script when running it against a source XML file referenced via the fn:doc function.

There are some subtle nuances associated with the code above. As described above, the base URI only describes where the XQL XQuery Script can expect to find the file whose name is passed into the fn:doc function. The code (final String baseURI = "file:///C:/xquery/xmlSource/";) shows the base URI being expressed with all forward slashes rather than the backslashes normally associated with Windows. The Unix-style forward slashes (even in Windows as is this example) and the "file:///" are necessary in the base URI for the Oracle XQuery engine to properly locate the source XML file. Besides ensuring that "file:///" is specified and that all path separators are Unix/Linux style, the other necessary characteristic of the base URI is that it needs to end with a forward slash if being used in a relative sense as in this example. In other words, a slash is NOT automatically put after this base URI before appending the location passed to fn:doc.

While the three rules (specify protocol, always use forward slashes, and always end with a forward slash) are important in specifying the base URI, the file location and name supplied for the XQJ code to find the XQL script file uses more traditional file location syntax: C:\\xquery\\xqlScripts\\extractIAUPlanets.xql (the double backslashes are to handle escaping in Java Strings). In the simpler case of specifying the location of the XQuery script file, one could use Java's System.getProperty("file.separator") to appropriately place file separators for the applicable operating system. For the base URI to the XML source files, on the other hand, the file separators should always be forward slashes regardless of the underlying operating system.

Here is the output from running this Oracle XQJ-powered code to run the prescribed XQuery script over the designated XML file:

runExampleXQueryFromFile(String,String) - XQuery Script File: C:\xquery\xqlScripts\extractIAUPlanets.xql
runExampleXQueryFromFile(String,String) - Provided Base URI: file:///C:/xquery/xmlSource/
----- IAU Endorsed Planets -----
Mercury
Venus
Earth
Mars
Jupiter
Saturn
Uranus
Neptune

The NASA Solar System Exploration site is a useful site for details and facts on the solar system and planets. Another useful site is the "Journey Through the Galaxy" site. My limited experience made the Regular site plenty informative, but people more experienced with galactic knowledge may find the Advanced version more interesting.

Specifying a Document in XQLPlus

In a previous blog entry, I briefly summarized some information regarding XQLPlus. In this blog entry, I focus on how to specify a file in the local file system to XQLPlus via the XQuery function fn:doc.

The example XML for this example is shown below and is saved as a file called C:\xquery\sample1.xml. Here are the contents of C:\xquery\sample1.xml:

<?xml version="1.0"?>
<People>
  <Person id="1" employee="true">
    <LastName>Arthurs</LastName>
    <FirstName>Austin</FirstName>
    <WeeklySalary>1000</WeeklySalary>
  </Person>
  <Person id="2" employee="false">
    <LastName>Baldwin</LastName>
    <FirstName>Barry</FirstName>
    <WeeklySalary>2000</WeeklySalary>
    <Employer>Fictitious and Sons</Employer>
  </Person>
  <Person id="3" employee="true">
    <LastName>Castleton</LastName>
    <FirstName>Carrie</FirstName>
    <WeeklySalary>1500</WeeklySalary>
  </Person>
</People>

One can use the XQuery function fn:doc to access this file in an XQuery expression within XQLPlus, but the path must be carefully specified when it is passed to the fn:doc function to work properly. It is important to specify the protocol for accessing the XML document. In this case, the file is on my local hard drive (C:\), so I will use the file protocol. The other interesting twist is that the path separator following the C: in the document string needs to be a forward slash ("/" in Unix/Linux style) rather than the backward slash style (\) normally associated with Windows directory structures.

The following screen snapshot (click on image to see larger version) demonstrates a very basic XQuery expression designed to print out the same XML body as the source, but with the element NewPeople replacing the element name People.



The first attempt, passing a document path and name without the file protocol and using the normal Windows back slashes, results in an error message: "FODC0005: invalid argument to fn:doc." In the second attempt, the slashes are switched to forward slashes and there is no error message, but only an empty <NewPeople> element tag is created. In the third attempt, the forward slashes are retained and file:/// is prepended to the document's location. This attempt works as we hoped and the XQuery expression returns the new XML. Although not shown in the snapshot above, the same error "FODC0005: invalid argument to fn:doc" is encountered when Windows-style back slashes are used with the file protocol.

It is helpful to be able to run XQuery expressions in XQLPlus against XML files stored on the file system. This is often more practical than loading the XML source to be queried into XQLPlus. As shown above this is easily accomplished as long as the String which is passed to the fn:doc function is built correctly with forward slashes and the file protocol.

Using the Apache POI POIBrowser

The Javadoc-generated documentation for POIBrowser package demonstrates how to run the POIBrowser class that is distributed with Apache POI. The current version of this package documentation that is available online (http://poi.apache.org/apidocs/org/apache/poi/contrib/poibrowser/package-summary.html#package_description) demonstrates running POIBrowser with version 2.5.1. In this blog entry, I intend to demonstrate running POIBrowser on Windows (Vista) with Apache POI 3.0.1. It is pretty much the same as running the 2.5.1 version, but with the obvious change of specifying the JAR files with the new version numbers in them. I'll also add some other notes related to running the POI Browser.

The first screen shot here (click on it to see zoom in on it) shows how I can run the POIBrowser. I intentionally included a "dir" command and its results in the screen shot to show which directory I was in. This directory was formed by downloading and unzipping the binary distribution of Apache POI and then later downloading and expanding the source distribution into the src and alt-src directories. I like to have the source directories with this open source product because the examples provide good illustrations of how to use Apache POI APIs.



As seen in the above screen shot, I need to supply the Microsoft Office files that I wanted browsed with POIBrowser as command-line arguments to the POIBrowser. I have placed some example Microsoft Office files in my directory C:\test (I wanted to act on copies rather than original files). It is important to note that Apache POI does not work on the new Office Open XML format introduced with the Microsoft Office 2007 products. The files in this test directory were created with Office 2003 products and so POI can access those files' contents.

The files in the test directory are named marx-poi.doc (Microsoft Word 2003), marx-poi.ppt (Microsoft PowerPoint 2003), and EmployeesReport.xls (Microsoft Excel 2003). These files are all in-work files related to my presentation at the upcoming RMOUG Training Days 2008 on "Excel with Apache POI and Oracle Database." The PowerPoint file is the slide presentation, the Word document is the associated white paper, and the Excel spreadsheet was generated from the Oracle database-provided HR schema using Apache POI.

To run POIBrowser on these three files, the following command was used:


java -cp poi-3.0.1-FINAL-20070705.jar;poi-contrib-3.0.1-FINAL-20070705.jar org.apache.poi.contrib.poibrowser.POIBrowser C:\test\marx-poi.doc C:\test\EmployeesReport.xls C:\test\marx-poi.ppt


The POIBrowser is a basic Swing application and running it as shown above leads to a simple Swing HMI with the title "POI Browser 0.09" and an item that can be clicked on to drop down other choices with the label "POI Filesystems."

The next image (click on it to see larger version), shows how the HMI looks after clicking on the icon to the left of "POI Filesystems."



The files that were passed on the command-line to POIBrowser are listed in inverse order on the tool. Each of these can be drilled down into to view more details about that file. For example, the next snapshot (click on image to see it larger) shows some of the summary details available on the PowerPoint document. The Word document shows similar details.



There is lots of summary and document summary information on both the PowerPoint document and on the Word document in my example, but, as the next image shows, there is not much for the Excel spreadsheet. This isn't due to any Excel limitation, but is instead due to the fact that the viewed Excel document happens to have been generated by my POI-based sample application, which did not bother trying to populate this information. The PowerPoint and Word documents in this example were generated using PowerPoint and Word respectively and so had this information populated.



The primary and most helpful use of POIBrowser is to actually look at the source code to see how the Apache POI API (specifically POI-HPSF) can be used to access property settings of Microsoft Office documents.

An Advantage of Software Development Experience

I have had the good fortune to work with many extremely sharp individuals who are new to software development, but learn rapidly and become significant contributors very quickly. As I have worked with these individuals, I have been extremely impressed with their intellectual abilities and ability to learn. However, I have also been able to recognize some of the advantages that only experience can bring to a developer's overall skillset and some of the lessons that only experienced seems to teach. In this blog entry, I have chosen to focus only on a single such example.

I think that most of us learn by relating new things we hear or read or see to things we already know. For example, most of us were taught how to multiply integers in elementary school by showing us how multiplication was really like adding (which we already knew), but that we were now adding same-sized groups of things instead of adding individual things. We were taught multiplication based on our already assumed knowledge of how to perform addition. Likening multiplication to addition not only helped us to learn multiplication quicker, but also served as a memory device. If we ever forgot the mechanics of multiplication (forgot the multiplication tables we memorized), we could return to our understanding of multiplication related to addition to remember what the product of two numbers is.

Most of us learn about software the same way. I started learning and working with Ruby before transitioning from Java 1.4.2 to Java 5 and the experience helped me to be more comfortable with the Java 5 enhanced for-each loop because I had seen a similar looping syntax in Ruby. Likewise, Ruby on Rails helped to introduce the concept of convention over configuration to me, which made it trivial to understand the Java Persistence API's (JPA's) configuration by exception concept. When I first experimented with Java, my C++ experience was significantly helpful in understanding Java syntax and in learning Java more quickly than I would have without C++ experience. Similarly, my first experiments with C# went much quicker thanks to my Java and C++ experience.

The final example I wanted to cover of this concept has to do with the quantifier symbol notation that is common to XML DTDs, regular expressions, and XQuery.

In regular expressions, the symbols ?, +, and * have special meanings as metacharacters. It turns out that the DTD creators and XQuery creators took advantage of this and gave these same symbols similar meanings in their respective specifications. The table below lists what these three symbols mean in each of these categories.

High-level Technology	Specifically Affected	?	+	*
Regular Expressions	Number of Characters or Groups	Optional	One or more	Any number
XML DTD	Occurrence Indicators (XML Elements)	Optional	One or more	Any number
XQuery	Occurrence Indicators (Type Expressions)	Optional	One of more	Any number

As the table above indicates, the symbol ? means optional (one or zero occurrence) for any character or group it follows in a regular expression and means that same thing for any defined element it follows in a DTD and for any XQuery type expression it follows. Similarly, + means one or more (one to many) for all three (regular expressions, DTD, and XQuery) and * means zero or more (zero to many) for all three areas.

The purpose of this blog has been to both illustrate how software development experience can make a software developer more productive and more adaptable to new technologies and to explicitly demonstrate the the common symbolic notation used for regular expressions, DTDs, and XQuery for specifying quantities of "things" in each area.

There is one downside to all of this, however. While an experienced developer can apply previously learned knowledge and skills to learning a new knowledge or skill, he or she must be cautious about allowing current knowledge to block the ability to think creatively and in new ways. The tendency to always do things the way we've done them before has its downsides and has led to the business lingo favorite: "Think outside of the box."

OpenLaszlo/Flex/Flash at Collaborate08

I was pleased to receive an e-mail message last night with the acceptance of the abstract that Bill Jackson, Michael Martin, and I submitted for Collaborate08 in Denver, Colorado, April 13-17, 2008, at the Colorado Convention Center.

Our presentation is titled "Add Some Flash to Your Oracle DB Applications: Flex and OpenLaszlo" and will be included in the IOUG (Independent Oracle Users Group) forum at Collaborate08. This will be Session 402 and the currently scheduled date and time for this presentation is Monday, April 14, 2008, from 9:15 am to 10:15 am.

Collaborate08 is the third annual presentation of this conference with the first two conferences being in Nashville and Las Vegas. Links to information on the three editions of Collaborate are included next:

Collaborate08 - Denver, Colorado - April 13-17, 2008
(Colorado Convention Center)
http://www.ioug.org/collaborate08/
http://www.oaug.org/conferencesandeducation/conferences/2008/collaborate08/

Collaborate07 - Las Vegas, Nevada - April 15-19, 2007
(Mandalay Bay Resort and Convention Center)
http://www.oaug.org/conferencesandeducation/conferences/2007/collaborate07/

Collaborate06 - Nashville, Tennessee - April 23-27, 2006
(Gaylord Opryland Resort and Convention Center)
http://www.oaug.org/conferencesandeducation/conferences/2006/collaborate06/

There is much going on in the Denver area related to Oracle in Spring 2008. In addition to Collaborate08 being held in Denver in April 2008, the Rocky Mountain Oracle User's Group (RMOUG) is holding their Training Days 2008 in February in the same Denver Convention Center. I will be presenting two different presentations (on XQuery and on Apache POI) at RMOUG Training Days 2008 and will be providing some support to Bill Jackson in his presentation at that conference.

Is Open Source Always Better?

The obvious answer, of course, is "No." Anytime that a word like "always", "never," "absolutely," or "necessarily" is used in a question like this, the answer will almost always be no because there are very few absolutes in software development. The more interesting question is whether the fact that a particular product, service, or library is open source is an advantage or disadvantage in selecting that product, service, or library for use. As with many questions of any significance, any reasonable answers usually begin with "It depends ..."

The question of whether open source is inherently a positive or a negative comes up repeatedly. It came up this week in Adopting a Java Persistence Framework: Which, When, and What? The author, Sharad Acharya, states in this article that one of the liabilities of using open source Hibernate is that it is open source. This led to multiple feedback comments expressing surprise that open source was being used as a liability. In my experience, many developers have an extreme position on open source always being better than proprietary products while managers, customers, and other stakeholders often have an extreme position against open source because of support concerns, licensing concerns, etc. As open source products such as Linux, Apache HTTP Server, and Apache Tomcat have become pervasive, the one side has softened on its stance against open source. Many of my colleagues in software development, however, still seem to think of open source as always and absolutely superior to proprietary products.

Before going on, it is important to emphasize that open source does not necessarily mean free. Open source also does not necessarily mean standards compliant or imply any compliance to any specification. It so happens that several open source products happen to be both free and compliant to some standard or specification, but not all open source products are. Therefore, if a person argues that open source is superior to proprietary products because the open source has lower licensing costs or because open source is standards compliant, the argument is flawed.

It is important to understand so-called "Free Beer Versus Free Speech" differentiation to understand how and why not all open source is free of license costs. In fact, for a company that generates proprietary software, some open source licenses (such as the GNU General Public License) may be considered far more costly in the long run than a proprietary product's license. While an open source product is not necessarily free, it is also true that a free product is not necessarily open source. For example, Oracle provides both JDeveloper and Database Express Edition free of charge, but neither of these highly useful products are open source.

I really like the JFreeChart charting library and it is open source and it is free of license cost. That being stated, there is no implication in any of the JFreeChart documentation that I have seen that JFreeChart conforms to any particular standard or specification. In fact, I cannot even think of what standard or specification would be applicable to it.

Another important caveat in any discussion on open source is to point out that there is such a thing as proprietary open source, meaning that the code can be viewed, but the product itself is highly proprietary and in many of these cases requires licensing fees.

What conditions lead to "open source" being a positive or negative attribute of a software library or product? Off the top of my head, the following dependent conditions come to mind:

• Are clients, customers, management, and other stakeholders comfortable with the idea of open source software used in their applications?


• Does use of the open source product mean that we must make our product open source as well?


• Realistically, will we ever want or need the ability to view the source code for this product? Even more importantly, are we willing to make changes to the open source third-party product knowing that we will have to maintain these changes as new versions of the open source product appear that may address this problem or bug in a different way?


• If we are willing to change the open source product's code for our use, does this bind us to releasing the code to others as well and can we live with that?


• Is customer support important for this product and is it readily available at a reasonable cost?


• Is adequate, or even good, documentation available for this open source product or are our developers going to spend more in labor costs learning this product than the licensing fees would be for an easier-to-use and better documented proprietary product? Note that I am not saying that proprietary or closed-source documentation is necessarily better than that of open source. Instead, I am talking about a case in which a particular closed-source proprietary product has significantly better documentation than its free, open source counterpart.


• What is the nature of the community behind a particular open source product? Is it one person or a large, thriving community?


• When was the project last updated? If it is December 2007 and the last time the project was updated is September 2002, you might want to think carefully about using this product. This is especially true when the project's main page has a "Current News" or similar section with the latest entry being several years old. Some open source products die with little or no fanfare.


• Does the open source product offer features not offered by proprietary software (and would you miss them if they are not offered)?


• Does the open source product offer all of the features offered by the proprietary product (and would you miss them if they are not offered)?


• Is the open source product just as proprietary as the closed-source project with the only difference being that one can see its code?


• Are there considerations that my customer, client, or other end user has that encourage or discourage use of open source products in their software? For example, while I really like Firefox, I keep Microsoft Internet Explorer installed on my machine because there are still several sites that seem to only work on MSIE (including, not surprisingly, Windows Update).


• Is there an entity or individual which has legal responsibility and stewardship for this open source product? (Many open source products understandably exclude any legal responsibilities or warranties).


• Perhaps the most important "it depends ..." question is "it depends which open source product you are comparing to which closed-source product." In the end, I don't think "it's open source" should be used as a major benefit or liability in choosing between products unless the planned usage is likely to really take advantage of open source attributes (developers using product are likely to view and/or change code or the open source project being used is so prevalent and commonly used that advantages of thorough end user testing and enhancement requests will be realized).
.

I have had great success and have the utmost respect for several open source products including Apache Tomcat and HTTP Server, Apache Ant, Apache Struts, Apache POI, Apache XML (as well as Apache Xalan and Apache Xerces), Apache Batik SVG Toolkit, Apache FOP, Apache Log4J, Spring Framework, NetBeans IDE, GlassFish application server, Hibernate object-relational persistence, PostgreSQL database, JFreeChart charting library, JDOM, Laszlo Systems'OpenLaszlo, Adobe Flex, Ruby on Rails, Firefox, and more. In addition, consensus seems to be building around the advantages and usefulness of open source products such as Eclipse IDE and Apache Maven.

Unfortunately, for each one of the highly useful open source products identified above, there are tens or hundreds or even thousands of open source projects out there that are dead or dying or never really got off the ground. For these short-lived projects, being open source does not make up for their many other deficiencies.

Some otherwise useful open source applications lose their advantage or usefulness when the language or technology provides a viable or even better alternative. For example, when Java 5 introduced annotations, the previously highly useful XDoclet became much less useful for many of us. Similarly, while some still prefer Log4j, others have started using the java.util.logging classes introduced by Java 1.4 for logging needs. Although annotations, Java logging, and JAXP and other standardized XML parsing were part of Java even before it was partially open sourced, most of us migrated to using annotations and a large number of us migrated to using the logging utility and JAXP even though that meant leaving open source alternatives behind.

So what makes a relatively small number of open source products truly superior or at least compelling alternatives to their commercial (and typically but now always closed-source) counterparts? These factors are important to understand to separate the truly useful open source from the rest. Here are some of my observed characteristics of the most useful open source.

• Open source product enjoys large, committed community of developers, users, articles and blogs authors, etc. The poster child for this is probably the Apache Software Foundation (ASF). There are some open source project hosting sites that don't prevent anyone from starting any project. While this freedom sounds nice, it makes it more difficult for end users to separate the truly great open source from the mediocre and even poor open source. The ASF's strict policies related to project incubation and committers' involvement tend to lead to more serious projects with real futures. When the community of committers is not as large as one would like, this condition can be somewhat alleviated by committed individuals. JFreeChart and JDOM come to mind for examples of these products. However, they do boast large user communities.


• Open source product is standard or specification compliant. Many of the most useful open source products are actually reference implementations for specifications (Tomcat, GlassFish, and TopLink Essentials come to mind). Even if a particular open source product is not the reference implementation, it is most useful when it implements a specification or standard. Otherwise, it is not all that different from the often castigated "proprietary" software. There are some open source products that are de facto standards because there is no specification that provides the standard. Examples of these include Ant and Struts before JavaServer Faces.


• Open source product is orders of magnitude simpler to use and apply than its commercial and/or standards-compliant counterparts. Some open source products have been wildly successful because they provided a more direct and simpler way to accomplish the same results as the more difficult standards-compliant approaches. The Spring Framework, for example, gained huge popularity when developers realized how much easier it was to apply Spring+Hibernate on a web server than to use EJB 2.x. Likewise, many people still choose Tapestry or other web frameworks over JSF because of the often perceived greater simplicity of the JSF alternatives.


• Open source product offers significant features or benefits not provided by the proprietary, commercial, and closed-source alternatives. Because monetary licensing cost (or lack thereof) is a benefit, cost can be factored into such a determination. For example, JBoss was the first major application server to provide significant enterprise Java support at no license cost. Another example is JFreeChart. It is more likely though that the true benefit might be better specification compliance (Tomcat/GlassFish) or significant added ease of us or significant innovative features at the time (Spring Framework/Struts).

There are many potential advantages to open source products, but not all open source products realize all or even some of these advantages. Potential advantages of open source include (1) ability to view the code, (2) ability to change the code, (3) collaboration from many different users to get "best of breed" ideas, and (4) the ability to have far wider and deeper testing from many real users.

The advantages of open source are not always realized. Many of will rarely actually change an open source product's code for our uses. More of us may look at the code for inspiration (I like to do this with Spring Framework for example), but I think even this benefit is often overrated. Open source products can really only boast of better user testing if they are used by a large community. The one-person open source project with three end users is probably no where near as well tested as most commercial products. Another advantage often associated with open source is quick bug fixes. This really depends on the open source product and its community. The idea that open source can enjoy the best ideas from many different sources is noble and idealistic and sometimes works out as advertised. However, it often leads to disagreements, project fractures, and spun off projects competing for the same space. Again, large well-received open source software projects are the ones most likely to enjoy the advantages of many innovative ideas.

Just as not all open source products can claim to enjoy any or all of the benefits commonly associated with open source, it is also true that the better open source products tend not to be saddled with many of the common complaints about open source projects. For example, poor documentation is often cited (and in my experience is a real issue) as a negative of many open source products. The better open source projects tend to be better documented and often even have strong third-party documentation in the forms of books, articles, blogs, and other real user documentation.

I have been the beneficiary of many fine open source projects, tools, and libraries. I appreciate the long hours that many developers and users have put into making some of these open source products that finest product available in their respective areas. With that stated, I'll also add that a few of us among the development ranks have become to used to thinking that open source is always and necessarily better than closed-source (which we often equate with license costs).

As with most things in software development, there is no solid rule of thumb and we need to use engineering judgment and compare products on a case by case basis and evaluate these products against our own peculiar and particular needs. The only thing that I would feel comfortable saying absolutely about this is the politician-friendly statement: "In general, with all else being equal, I prefer open source products." It is rare that "all else is equal" and the "in general" gives me an escape route for a few exceptions even to this rule of thumb. Some people consider "open source" an automatic advantage, some do consider it an automatic disadvantage (though I think the number of people in this group is shrinking thanks to many successful open source projects), and the rest of us realize that "open source" or "closed source" is only one piece of the overall package that should be considered when evaluating and selecting products that best satisfy our customer needs.

I have never even looked at any source code for some of my favorite open source projects, tools, and libraries, but I have directly benefited from the ability and willingness of others to view and change source code in these products. Even more commonly, I have benefited from the wide use and "real life" testing of these products. So, while I generally look favorably upon open source, I also realize that slapping the "open source" label on a poor product does nothing to make it a better product and that there are situations where certain individuals don't see the advantages to open source and might even see it as a liability.

XQLPlus: SQL*Plus for XQuery

If XQuery is to XML data what SQL (Structured Query Language) is to relational data, then it follows that XQLPlus is to XQuery what SQL*Plus is to SQL.

Chapter 18 of the Oracle XML DB Developer’s Guide 11g Release 1 (“Using Oracle XQuery with XML DB”) states about Oracle’s proprietary convenience SQL function XQUERY: “in effect, this command turns SQL*Plus into an XQuery command-line interpreter.” In other words, the XQUERY function can be used in conjunction with Oracle SQL*Plus to work with XML. However, as documented in Andrej Koelewijn’s IT-eye weblog entry Executing XQuery Statements with XQLPlus, Oracle actually provides a command-line XQuery interpreter tool called XQLPlus. When using XQuery that has little or nothing to do with the database, this XQLPlus tool may be easier to use than using SQL*Plus and the XQUERY function.

This blog entry adds a few more details regarding this handy utility, but I recommend the quick read of the Andrej Koelewijn blog in addition to mine if interested in what XQLPlus has to offer. While there are obviously other references and resources covering XQLPlus, a quick Google search shows that a large percentage of these are simply links and trackbacks to the Andrej Koelewijn blog. Thanks, Andrej, for directing greater focus on this wonderfully useful utility.

The Executing XQuery Statements with XQLPlus blog entry demonstrates running XQLPlus as part of the xquery.jar JAR file included with a JDeveloper installation. This same JAR file can also be found in the Oracle 11g database installation (normal database jlib subdirectory and the sqldeveloper subdirectory) and in the OC4J/Oracle Application Server installation directory. Because the Executing XQuery Statements with XQLPlus blog entry covers using JDeveloper's delivered xquery.jar to access XQLPlus, I'll focus here on using it in the Oracle 11g Database installation instead.

The xquery.jar JAR file is included in the jlib subdirectory of the Oracle Database 11g Release 1 installation. For example, on my machine, it is in the directory C:\app\Dustin\product\11.1.0\db_1\jlib. The XQLPlus class is oracle.xquery.XQLPlus and is available in the xquery.jar JAR file in this directory (you can confirm this with the command jar tvf xquery.jar).

You might be tempted to run the XQLPlus tool directly with the command java -cp xquery.jar oracle.xquery.XQLPlus. Trying this, however, leads to a NoClassDefFoundError for the class oracle/xml/scalable/PageManagerPool. To provide this class's definition, add xmlparserv2.jar to the classpath. This class is not in the same directory as xquery.jar, so its relative directory needs to be specified. When the new command java -cp xquery.jar;..\LIB\xmlparserv2.jar oracle.xquery.XQLPlus is run, you will see the desired XQLPlus prompt as shown in the following image (click on the image to see a larger version of it):



Very simple XQuery expressions can be evaluated with XQLPlus run as shown above. However, for most realistic queries, additional classes will need to be added to the classpath as certain features are encountered by XQLPlus at runtime. For example, the orai18n-collation.jar is required when the class oracle/i18n/text/OraCollator cannot be found.

With the XML Parser Version 2 library and the internationalization library, the command to run XMLPlus is now: java -cp xquery.jar;..\LIB\xmlparserv2.jar;orai18n-collation.jar oracle.xquery.XQLPlus . As you run increasingly complicated XQuery expressions through XQLPlus, it is likely that you'll need to include additional JAR files on the classpath. For instance, xsu12.jar is often needed and that can be found in the same relative database installation directory as xmlparserv2.jar (..\LIB).

The xquery.jar JAR file is also located in the database installation directory in the sqldeveloper area. For example, on my machine, it is located in C:\app\Dustin\product\11.1.0\db_1\sqldeveloper\lib. The xmlparserv2.jar file is in the same directory and the internationalization JAR is in jlib, so XQLPlus can be run from the database's SQL Developer directory with the command java -cp xquery.jar;xmlparserv2.jar;../jlib/orai18n-collation.jar oracle.xquery.XQLPlus.

The Oracle database installation (core and SQL Developer section) and JDeveloper installation are not the only sources of the xquery.jar and its XQLPlus class/utility. This can also be found in OC4J installation directories in the lib subdirectory. For example, it is located in the C:\oc4j_extended_101330\lib directory on my machine. In that installation, the xmlparserv2.jar class is located in the same directory as xquery.jar, so running XQLPlus from this directory boils down to this command: java -cp xquery.jar;xmlparserv2.jar oracle.xquery.XQLPlus.

The Oracle Containers for J2EE Services Guide includes Chapter 8 (Oracle XML Query Service) that talks about OC4J's extensive additional XQuery support above and beyond the XQLPlus Java class.

Because XQLPlus is a Java class run with the normal Java launcher, it is necessary to include dependent JARs on the classpath when running it. This is a little bit of a hassle, so I prefer to include the command as either a CMD script (in DOS) or as a Unix/Linux shell script, alias, or shell function in those environments.

Another useful resource on querying XML with XQLPlus is Querying XML Documents. This resource demonstrates how to run XQLPlus in interactive mode (no file or XQuery expression provided to XQLPlus on the command-line), querying XML from the command-line, and querying XML with XQueryX (XQuery expressions with XML syntax). A resource that is dated but still is useful as an overview of XQLPlus is XQuery Prototype with XQLPlus Readme.

The xquery.jar JAR file with the XQLPlus command-line utility can be found in the installation directories for the Oracle database, for the Oracle database's SQL Developer, for JDeveloper, and for OC4J. The primary difference between these appears to be where dependent JAR files can be found relative to the xquery.jar file. Because these relative locations differ depending on which xquery.jar is used and because the classpath can be several entries long, I recommend capturing the entire call in a script placed in the environment's path that can be executed whenever the XQLPlus tool is required.