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.