After going through this document you should be able to:
Get the content of an OS file which could have XML.
Perform some basic XML DB Foldering operations.
Perform some basic DOM Manipulation operations.
Introduction
The purpose of this document is to highlight the helper
subprograms provided with XDB_Utilities package. This package provides subprograms
for performing certain basic Oracle XML DB operations. These subprograms operate
on Bfiles, CLOBs, DOM and Oracle XML DB Resource APIs.
Prerequisites
Following are the prerequisites for installing the XDB_Utilities
package and for running the sample scripts provided with this document. The
required files are provided in XDB_Utilities.zip
file. The files can be found under 'Xdb' folder on extraction.
After extracting the ZIP file, perform the following
tasks to setup the package in your database and to create the required objects
for running the code samples referred below:
Installing the XDB_Utilities package
Install the XDB_Utilities PL/SQL package by executing the XDB_Utilities.sql
file in your schema as mentioned below. The schema in which this SQL is executed
should have CREATE PUBLIC SYNONYM privilege associated with it.
SQL> @<your_directory>\Xdb\XDB_Utilities.sql
Creation of Directory Object
A directory object specifying the location from which to read files needs
to be defined for the proper functioning of the sample scripts. A directory
object maps a name to a directory name on the file system. In the sample scripts
provided, reference to directory object named 'SOURCE_DIR' is made. Execute
the following statement in SQL*Plus to create a directory object named 'SOURCE_DIR'.
Modify directory location 'D:\Xdb' with the location where you have extracted
the files from the zip file. If your operating system uses case-sensitive
path names, then make sure you specify the directory in the correct format.
CREATE OR REPLACE DIRECTORY source_dir AS 'D:\Xdb';
Note: Directory
objects can be created by DBAs or by any user with the CREATE ANY DIRECTORY
privilege. The directory location specified by the directory object should
be on the server machine where the database is running. After a directory
is created, the user creating the directory object needs to grant READ permission
on the directory to other users. For example, to allow the server to read
files on behalf of user Scott in the directory named by 'SOURCE_DIR', the
user who created the directory object must execute the following command.
Modify reference to Scott with your schema name in the below mentioned SQL
statement.
GRANT READ ON DIRECTORY source_dir TO Scott;
The files 'poOrder.xml' , 'account.xsd', 'acct1.xml',
'acct2.xml', 'acct3.xml' and also this overview file i.e. 'XMLDB_Utilities_Overview.html'
are referred in the sample scripts provided. These files have to be physically
located in the directory specified by the directory object. Otherwise the
sample SQL scripts referred below will throw an error.
Creation of the database tables for the sample
scripts
Execute the following statements to create the required tables in your schema.
Note: There
are some sample scripts provided below as links which demonstrate the use
of the subprograms in the XDB_Utilities package. These samples provided within
the SQL scripts are complete and they can be run as is from SQL*Plus.
Subprograms for getting the
content of an OS file.
The content of an OS file needs to be read into a program
in a lot of application scenarios. The file content might be an XML document
or it may be in any other format. XDB_Utilities package includes helper subprograms
for getting the content of a file as a CLOB. Once the file content is available
as a CLOB, it can be utilised as required by the application. These subprograms
make use of the DBMS_LOB PL/SQL package for handling the necessary LOB operations.
The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, NCLOBs,
BFILEs, and temporary LOBs.
Bfile is nothing but a large, binary object stored outside the database. XDB_Utilities
package provides subprograms for getting content of such objects with necessary
character set conversions. Overloaded subprogram getBFileContent() is provided
for getting the file content from a BFile. This subprogram returns the content
of the Bfile as a CLOB. The getBFileContent() subprogram is provided as overloaded
so that for both the below mentioned cases, the file content can be retrieved.
a. Case 1 - Where the CLOB object associated with the BFile is available
b. Case 2 - Where the CLOB associated with the BFile is not available
Usage example: The usage of getBFileContent() subprogram is demonstrated through
this SQL Script. This script demonstrates
how the content of a file 'XMLDB_Utilities_Overview.html' located in the user
specified 'SOURCE_DIR' directory is retrieved using getBFileContent() subprogram.
After getting the file content as CLOB, it stores the file content into the
database table 'demoTable'. The 'demoTable' table can then be queried to view
the file content. Getting the content from a file
XDB_Utilities also provide subprograms for getting the
content of a file specified by the filename and directoryname information with
necessary character set conversions.. Here the directoryname refers to the directory
object created above. The directory object should be representing a valid, existing
physical directory that has been defined in the Oracle database with relevant
permissions. And the filename refers to the name of the file residing physically
in the directory specified. Overloaded subprogram getFileContent() is provided
for getting the content from a file. This subprogram returns the content of
the file as CLOB. The getFileContent() subprogram is provided as overloaded
so that for both the below mentioned cases, the file content can be retreived.
a. Case 1 - Where the CLOB object associated with the file is available
b. Case 2 - Where the CLOB associated with the file is not available
This function internally calls getBFileContent() subprogram after it gets the
BFile object from the filename and directoryname specified.
Usage example: The usage of getFileContent() subprogram is demonstrated through
this SQL Script. This script demonstrates
how the content of a file 'XMLDB_Utilities_Overview.html' located in user specified
'SOURCE_DIR' directory or in a default directory is retrieved using getFileContent()
subprogram. After getting the file content as CLOB, it stores the content into
the database table 'demoTable'. The'demoTable' table can then be queried to
view the file content.
Getting XML file content
Sometimes the data content available in a BFile or a
file is an XML document. XDB_Utilities provides subprograms for getting the
XML content too. Overloaded subprograms getXMLFromBFile() and getXMLFromFile()
are provided to get the XML content from a Bfile and a File respectively. These
subprograms get the file content as a CLOB. On the CLOB returned, an XMLType
function is applied to get the content as XMLType. These subprograms are provided
as overloaded so that for both the below mentioned cases, the file content can
be retreived.
a. Case 1 - Where the CLOB object associated with the file is available
b. Case 2 - Where the CLOB associated with the file is not available
Usage example:
a. The usage of getXMLFromBFile() subprogram is demonstrated through this SQL
Script. This script demonstrates how the XML content of the file 'poOrder.xml'
located in the user specified 'SOURCE_DIR' directory is retrieved using getXMLFromBFile()
subprogram. After getting the file content as XMLType, it stores the content
into the database table 'xmlDemoTable'. The 'xmlDemoTable' table can then be
queried to view the XML file content.
b. The usage of getXMLFromFile() subprogram is demonstrated through this SQL
Script. This script demonstrates how first we can register an XML schema
using the getXMLFromFile() subprogram and then use the same subprogram to load
multiple instances of XML documents into the database. The XML documents are
stored into the 'xmlDemoTable' table which can be queried to view the XML contents.
Both the scenarios i.e. the case where the CLOB object is available and the
case where the CLOB object is not available are covered in this example script.
Subprograms based on Oracle XML
DB Resource APIs
XDB_Utilities package also provides subprograms for commonly
required Oracle XML DB foldering operations. It provides a subprogram resourceExists()
to find out whether a resource exists in the XML DB repository or not, given
a path name.
One more subprogram createHomeDirectory() is provided to create a Home folder
for the given user(schema) in the XML DB repository. This subprogram also assigns
the required privileges to the new home folder created .
Usage example: The usage of createHomeDirectory() subprogram
is demonstrated through this SQL Script.
This script demonstrates the creation of Home folder for a given user using
createHomeDirectory() procedure in the repository. It also checks for the existence
of the Home folder created using the resourceExists() subprogram. Please
note that this script can be executed by the 'SYS' user or the user who has
the required privileges.
Subprograms for DOM Manipulation based
on PL/SQL DOM APIs
One of the important requirement of the applications working
on DOM manipulation is to get the text value of a given node. XDB_Utilities
provides a subprogram getChildTextNode() to get the text value of a node in
an XML document given the node name. This subprogram uses PL/SQL DOM APIs for
getting the node's text value. Besides this, subprograms for converting the
RAW value to a Boolean value(rawToBoolean() function) and vice versa(booleanToRaw()
function) are also provided.
Usage example: The usage of getChildTextNode() subprogram
is demonstrated through this SQL Script.
This script demonstrates the retrieval of the text value of a given node in
an XML document. This script reads through the XML document provided and gets
the text value of the node named 'TRANSPORT' in the XML.
Bookmark
