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 LONGcan 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 LONGshown above, other standard SQL*Plus settings can be used in conjunction with the XQUERY command. For example,
SET LINESIZEcan be used for individual lines that are longer than the default of eighty characters. Likewise,
SET PAGESIZEcan 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
OFFremoves 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 OFFor 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
COLUMNcommand with the column
column_valuebeing the one whose
HEADINGis changed. However, I cannot find any
column_valuecolumn 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
columnin 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_xqueryto display any heading we like. To do this, we can use either
column result_plus_xquery heading "We like XQUERY!"
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
CONTEXT. These are each set by starting the set command with
SET XQUERYand 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 XQUERYcommand as demonstrated in the next screen snapshot.
As the above screen snapshot indicates,
CONTEXThave empty strings as their defaults. You must go to the documentation to find out what
DEFAULTmeans for the other two settings. The default setting for
BYVALUE, meaning that newly created nodes will not maintain identity and will be treated as completely new nodes. The default setting for
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.