Skip Headers

Oracle® XML DB Developer's Guide
10g Release 1 (10.1)

Part Number B10790-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

17 Creating and Accessing Data Through URLs

This chapter describes how to generate and store URLs inside the database and to retrieve the data pointed to by the URLs. It also introduces the concept of DBUris which are URLs to relational data stored inside the database. It explains how to create and store references to data stored in Oracle XML DB Repository hierarchy.

This chapter contains these topics:

How Oracle Database Works with URLs and URIs

In developing Internet applications, and particularly Internet-based XML applications, you often must refer to data somewhere on a network using URLs or URIs.

Oracle Database can represent various kinds of paths within the database. Each corresponds to a different object type, all derived from a general type called UriType:

Accessing and Processing Data Through HTTP

Any resources stored inside Oracle XML DB repository can also be retrieved by using the HTTP Server in Oracle XML DB. Oracle Database also includes a servlet that makes table data available through HTTP URLs. The data can be returned as plain text, HTML, or XML.

Any Web-enabled client or application can use the data without SQL programming or any specialized database API. You can retrieve the data by linking to it in a Web page or by requesting it through the HTTP-aware APIs of Java, PL/SQL, or Perl. You can display or process the data through any kind of application, including a regular Web browser or an XML-aware application such as a spreadsheet. The servlet supports generating XML and non-XML content and also transforming the results using XSLT style sheets.

Creating Columns and Storing Data Using UriType

You can create database columns using UriType or its child types, or you can store just the text of each URI or URL and create the object types when needed. When storing a mixture of subtypes in the database, you can define a UriType column that can store various subtypes within the same column.

Because these capabilities use object-oriented programming features such as object types and methods, you can derive your own types that inherit from the Oracle-supplied ones. Deriving new types lets you use specialized techniques for retrieving the data or transforming or filtering it before returning it to the program.

UriFactory Package

When storing just the URI text in the database, you can use the UriFactory package to turn each URI into an object of the appropriate subtype. UriFactory package creates an instance of the appropriate type by checking what kind of URI is represented by a given string. For example, any URI that begins with http:// is considered an HTTP URL. When the UriFactory package is passed such a URI string, it returns an instance of a HttpUriType object.

Example 17-1 Using UriFactory

CREATE table uri_tab (url URIType);
INSERT INTO uri_tab VALUES (httpuritype.createuri(''));  
SELECT e.url.getclob() FROM uri_tab e;

Other Sources of Information About URIs and URLs

Before you explore the features in this chapter, you should be familiar with the notation for various kinds of URIs.


URI Concepts

This section introduces you to URI concepts.

What Is a URI?

A URI, or Uniform Resource Identifier, is a generalized kind of URL. Like a URL, it can reference any document, and can reference a specific part of a document. It is more general than a URL because it has a powerful mechanism for specifying the relevant part of the document. A URI consists of two parts:

  • URL, that identifies the document using the same notation as a regular URL.

  • Fragment, that identifies a fragment within the document. The notation for the fragment depends on the document type. For HTML documents, it has the form #anchor_name. For XML documents, it uses XPath notation.

The fragment appears after the # in the following examples.


Only XDBUriType and HttpUriType support the URI fragment in this release. DBUriType does not support the URI fragment.

How to Create a URL Path From an XML Document View

Figure 17-1 shows a view of the XML data stored in a relational table, EMP, in the database, and the columns of data mapped to elements in the XML document. This mapping is referred to as an XML visualization. The resulting URL path can be derived from the XML document view.

Typical URIs look like the following:

  • For HTML:

    where Anchor is a named anchor inside the document.

  • For XML:


    • The portion before the # identifies the location of the document.

    • The portion after the # identifies a fragment within the document. This portion is defined by the W3C XPointer recommendation.

UriType Objects Can Use Different Protocols to Retrieve Data

Oracle Database supports datatypes in the database to store and retrieve objects that represent URIs. See " UriType Values Store Uri-References". Each datatype uses a different protocol, such as HTTP, to retrieve data.

Oracle Database also provides new forms of URIs that represent references to rows and columns of database tables.

Advantages of Using DBUri and XDBUri

The following are advantages of using DBUri and XDBUri:

  • Reference style sheets within database-generated Web pages. Oracle-supplied package DBMS_METADATA uses DBUri to reference XSL style sheets. XDBUri can also be used to reference XSLT style sheets stored in Oracle XML DB repository.

  • Reference HTML, images and other data stored in the database. The URLs can be used to point to data stored in tables or in the repository hierarchical folders.

  • Improved Performance by bypassing the Web server. If you already have a URL in your XML document, then you can replace it with a reference to the database by either:

    • Using a servlet

    • Using a DBUri or XDBUri to bring back the results

    Using DBUri or XDBUri has performance benefits because you interact directly with the database rather than through a Web server.

  • Accessing XML Documents in the Database Without SQL. You are not required to know SQL to access an XML document stored in the database. With DBUri you can access an XML document from the database without using SQL.

    Because the files or resources in Oracle XML DB repository are stored in tables, you can access them either through the XDBUri or by using the table metaphor through the DBUri.

    See Also:

    PL/SQL Packages and Types Reference, "DBMS_METADATA package"

UriType Values Store Uri-References

URIs or Universal Resource Identifiers identify resources such as Web pages anywhere on the Web. Oracle Database provides the following UriType subtypes for storing and accessing external and internal Uri-references:

These datatypes are object types with member functions that can be used to access objects or pages pointed to by the objects. By using UriType, you can:

These are related by an inheritance hierarchy. UriType is an abstract type and the DBUriType, HttpUriType, and XDBUriType are subtypes of UriType. You can reference data stored in CLOBs or other columns and expose them as URLs to the external world. Oracle Database provides a standard servlet than can be installed that interprets DBUriType.

Advantages of Using UriType Values

Oracle already provides the PL/SQL package UTL_HTTP and the Java class to fetch URL references. The advantages of defining this new UriType datatype in SQL are:

  • Improved Mapping of XML Documents to Columns. Uri-ref support is needed when exploding XML documents into object-relational columns, so that the Uri-ref specified in documents can map to a URL column in the database.

  • Unified access to data stored inside and outside the server. Because you can use UriType values to store pointers to HTTP/DB urls, you get a unified access to the data wherever it is stored. This lets you create queries and indexes without having to worry about where the data resides.

UriType Functions

The UriType abstract type supports a variety of functions that can be used over any subtype. Table 17-1 lists the UriType member functions.

Table 17-1 UriType Member Functions

UriType Member Functions Description


Returns the value pointed to by the URL as a character LOB value. The character encoding will be that of the database character set.


Returns the URL stored in the UriType. Do not use "url" directly. Use this function instead. This can be overridden by subtypes to give you the correct URL. For example, HttpUriType stores only the URL and not the http:// prefix. Hence getUrl() actually prepends the prefix and returns the value.


Similar to the former (getUrl), except that it calls the escaping mechanism to escape the characters in the URL as to conform to the URL specification. For example spaces are converted to the escaped value %20.

See "How Oracle Database Works with URLs and URIs" .


Returns the MIME information for the URL. For UriType, this is an abstract function.


Returns the XMLType object corresponding to the given URI. This is provided so that an application that must perform operations other than getClob or getBlob can use the XMLType methods to do those operations.

This throws an exception if the URI does not point to a valid XML document.


Returns the Binary Large Object (BLOB) value pointed to by the URL. No character conversions are performed and the character encoding is the same as the one pointed to by the URL. This can also be used to fetch binary data.

createUri(uri IN VARCHAR2)

This constructs the UriType. It is not actually in UriType, rather it is used for creating URI subtypes.

HttpUriType Functions

Use HttpUriType to store references to data that can be accessed through the HTTP protocol. HttpUriType uses the UTL_HTTP package to fetch the data and hence the session settings for the package can also be used to influence the HTTP fetch using this mechanism. Table 17-2 lists the HttpUriType member functions.

Table 17-2 HttpUriType Member Functions

HttpUriType Method Description


Returns the value pointed to by the URL as a character LOB value. The character encoding is the same as the database character set.


Returns stored URL.


Similar to getUrl, except that it calls the escaping mechanism to escape the characters in the URL as to conform to the URL specification. For example, spaces are converted to the escaped value %20.


Gets the binary content as a BLOB. If the target data is non-binary, then the BLOB will contain the XML or text representation of the data in the database character set.


Returns the XMLType object corresponding to this URI. Will throw an error if the target data is not XML. See also "getXML() Function".


Returns the MIME information for the URL. See also "getContentType() Function".


httpUriType constructor. Constructs the httpUriType.


httpUriType constructor. Constructs the httpUriType.

Example 17-2 Using HTTPUriType

The following example creates a URI table to store the HTTP instances:

create table uri_tab ( url httpuritype);

Insert the HTTP instance:

insert into uri_tab values 

Generate the HTML:

select e.url.getclob() from uri_tab e;

getContentType() Function

getContentType() function returns the MIME information for the URL. The HttpUriType de-references the URL and gets the MIME header information. You can use this information to decide whether to retrieve the URL as BLOB or CLOB based on the MIME type. You would treat a Web page with a MIME type of x/jpeg as a BLOB, and one with a MIME type of text/plain or text/html as a CLOB.

Example 17-3 Using getContentType() and HttpUriType to Return HTTP Headers

Getting the content type does not fetch all the data. The only data transferred is the HTTP headers (for HTTPURiType) or the metadata of the column (for DBUriType). For example:

  httpuri HttpUriType;
  x clob;  
  y blob;
  httpuri := HttpUriType('');
  if httpuri.getContentType() = 'application-x/bin' then
     y := httpuri.getblob();
     x := httpuri.getclob();
  end if;

getXML() Function

getXML() function returns XMLType information for the result. If the document is not valid XML (or XHTML), then an error is thrown.

DBUri, Intra-Database References

DBUriType, a database relative to URI, is a special case of the Uri-ref mechanism, where ref is guaranteed to work inside the context of a database and session. This ref is not a global ref like the HTTP URL; instead it is local ref (URL) within the database.

You can also access objects pointed to by this URL globally, by appending this DBUri to an HTTP URL path that identifies the servlet that can handle DBUri. This is discussed in "Turning a URL into a Database Query with DBUri Servlet" .

Formulating the DBUri

The URL syntax is obtained by specifying XPath-like syntax over a virtual XML visualization of the database. See Figure 17-1, "DBUri: Visual or SQL View, XML View, and Associated XPath":

  • The visual model is a hierarchical view of what a current connected user would see in terms of SQL schemas, tables, rows, and columns.

  • The XML view contains a root element that maps to the database. The root XML element contains child elements, which are the schemas on which the user has some privileges on any object. The schema elements contain tables and views that the user can see. A child element is ann element that is wholly contained within another, referred to as its parent element. For example <Parent><Child></Child></Parent> illustrates a child element nested within its parent element.

Example 17-4 The Virtual XML Document that Scott Sees

For example, the user scott can see the following virtual XML document.

<?xml version="1.0"?>
<oradb SID="ORCL">
      <!-- EMp table -->

Figure 17-1 DBUri: Visual or SQL View, XML View, and Associated XPath

Description of adxdb053.gif follows
Description of the illustration adxdb053.gif

This XML document is constructed at the time you do the query and based on the privileges that you have at that moment.

You can make the following observations from Example 17-4:

  • User scott can see the scott database schema and jones database schema. These are schemas on which the user has some table or views that he can read.

  • Table emp shows up as EMP with row element tags. This is the default mapping for all tables. The same for dept and the customer_obj_tab table under the jones schema.

  • In this release, null elements are absent

  • There is also a PUBLIC element under which tables and views are accessible without schema qualification. For example, a SELECT query such as:

    SELECT * FROM emp;

    when queried by user scott, matches the table emp under the scott schema and, if not found, tries to match a public synonym named emp. In the same way, the PUBLIC element contains:

    • All the tables and views visible to users through their database schema

    • All the tables visible through the PUBLIC synonym

Notation for DBUriType Fragments

With the Oracle Database being visualized as an XML tree, you can perform XPath traversals to any part of the virtual document. This translates to any row-column intersection of the database tables or views. By specifying an XPath over the visualization model, you can create references to any piece of data in the database.

DbUri is specified in a simplified XPath format. Currently, Oracle does not support the full XPath or XPointer recommendation for DBURType. The following sections discuss the structure of the DBUri.

You can create DBUri references to any piece of data. You can use the following instances in a column as reference:

  • Scalar

  • Object

  • Collection

  • An attribute of an object type within a column. For example:.../ROW[empno=7263]/COL_OBJ/OBJ_ATTR

These are the smallest addressable units. For example, you can use:





Oracle does not currently support references within a scalar, XMLType or LOB data column. Oracle supports using an XPath to XMLType tables.

DBUri Syntax Guidelines

There are restrictions on the kind of XPath queries that can be used to specify a reference. In general, the fragment part must:

  • Include the user database schema name or specify PUBLIC to resolve the table name without a specific schema.

  • Include a table or view name.

  • Include the ROW tag for identifying the ROW element.

  • Identify the column or object attribute that you wish to extract.

  • Include predicates at any level in the path other than the schema and table elements.

  • Indicate predicates not on the selection path in the ROW element.

Example 17-5 Specifying Predicate pono=100 With the ROW Node

For example, if you wanted to specify the predicate pono = 100, but the selection path is:


then you must include the pono predicate along with the ROW node as:


where purchase_obj_tab is a table in the SCOTT schema.

  • A DBUri must identify exactly a single data value, either an object type or a collection. If the data value is an entire row, then you indicate that by including a ROW node. The DBUri can also point to an entire table. Note that only valid XML can be returned.

Using Predicate (XPath) Expressions in DBUris

The predicate expressions can use the following XPath expressions:

  • Boolean operators AND, OR, and NOT

  • Relational operators <, >, <=, !=, >=, =, mod, div, * (multiply)


    • No XPath axes other than the child axes are supported except within XMLType of XMLType tables. The wild card (*), descendant (//), and other operations are not valid.

    • Only the text() XPath function is supported. text() is valid only on a scalar node, not at the row or table level.

The predicates can be defined at any element other than the schema and table elements. If you have object columns, then you can search on the attribute values as well.

Example 17-6 Searching for Attribute Values on Object Columns Using DBUri

For example, the following DBUri refers to an ADDRESS column containing state, city, street, and zip code attributes:


This DBUri identifies the city attribute that has California or Oregon as state and either Portland as city name or 94404 as zipcode.

See Also: for an explanation of the XML XPath notation

Some Common DBUri Scenarios

The DBUri can identify various objects, such as a table, a particular row, a particular column in a row, or a particular attribute of an object column. The following subsections describe how to identify different object types.

Identifying the Whole Table

This returns an XML document that retrieves the whole table. The enclosing tag is the name of the table. The row values are enclosed inside a ROW element:


Example 17-7 Using DBUri to Identify a Whole Table as an XML Document

For example:


returns an XML document with a format like the following:

<?xml version="1.0"?>
   ... <!-- other columns -->
 <!-- other rows -->

Identifying a Particular Row of the Table

This identifies a particular ROW element in the table. The result is an XML document that contains the ROW element with its columns as child elements. Use the following syntax:


Example 17-8 Using DBUri to Identify a Particular Row in the Table

For example:


returns the XML document with a format like the following:

<?xml version="1.0"?>
  <!-- other columns -->


In this example, the predicate expression must identify a unique row.

Identifying a Target Column

In this case, a target column or an attribute of a column is identified and retrieved as XML.


You cannot traverse into nested table or VARRAY columns.

Use the following syntax:


Example 17-9 Using DBUri to Identify a Specific Column


retrieves the ename column in the emp table, where empno is 7369, and department number is 20, as follows:

<?xml version="1.0"?>

Example 17-10 Using DBUri to Identify an Attribute Inside a Column


retrieves the state attribute inside an address object column for the employee whose empno is 7369, as follows:

<?xml version="1.0"?>

Retrieving the Text Value of a Column

In many cases, it can be useful to retrieve only the text values of a column and not the enclosing tags. For example, if XSLT style sheets are stored in a CLOB column, you can retrieve the document text without having any enclosing column name tags. You can use the text() function for this. It specifies that you only want the text value of the node. Use the following syntax:


Example 17-11 Using DBUri to Retrieve Only the Text Value of the Node

For example:


retrieves the text value of the employee name, without the XML tags, for an employee with empno = 7369. This returns a text document, not an XML document, with value SMITH.


The XPath alone does not constitute a valid URI. Oracle calls it a DBUri because it behaves like a URI within the database, but it can be translated into a globally valid Uri-ref.


The path is case-sensitive. To specify scott.emp, typically you will use SCOTT/EMP, because the actual table and column names are stored capitalized in the Oracle data dictionary.

How DBUris Differ from Object References

A DBUri can access columns and attributes and is loosely typed Object references can only access row objects. DBUri is a superset of this reference mechanism.

DBUri Applies to a Database and Session

A DBUri is scoped to a database and session. You must already be connected to the database in a particular session context. The schema and permissions needed to access the data are resolved in that context.


The same URI string may give different results based on the session context used, particularly if the PUBLIC path is used.

For example, /PUBLIC/FOO_TAB can resolve to SCOTT.FOO_TAB when connected as scott, and resolve as JONES.FOO_TAB when connected as JONES.

Where Can DBUri Be Used?

Uri-ref can be used in a number of scenarios, including those described in the following sections:

Storing URLs to Related Documents

In the case of a travel story Web site where you store travel stories in a table, you might create links to related stories. By representing these links in a DBUriType column, you can create intra-database links that let you retrieve related stories through queries.

Storing Style Sheets in the Database

Applications can use XSLT style sheets to convert XML into other formats. The style sheets are represented as XML documents, stored as CLOBs. The application can use DBUriType objects:

  • To access the XSLT style sheets stored in the database for use during parsing.

  • To make references, such as import or include, to related XSLT style sheets. You can encode these references within the XSLT style sheet itself.


    • A DBUri is not a general purpose XPointer mechanism to XML data.

    • It is not a replacement for database object references. The syntax and semantics of references differ from those of Uri-refs.

    • It does not enforce or create any new security models or restrictions. Instead, it relies on the underlying security architecture to enforce privileges.

DBUriType Functions

Table 17-3 lists the DBUriType methods and functions.

Table 17-3 DBUriType Methods and Functions

Method/Function Description


Returns the value pointed to by the URL as a character LOB value. The character encoding is the same as the database character set.


Returns the URL that is stored in the DBUriType.


Similar to getUrl, except that it calls the escaping mechanism to escape the characters in the URL as to conform to the URL specification. For example, spaces are converted to the escaped value %20.


Gets the binary content as a BLOB. If the target data is non-binary, then the BLOB will contain the XML or text representation of the data in the database character set.


Returns the XMLType object corresponding to this URI.


Returns the MIME information for the URL.


Constructs a DBUriType instance.


Constructs a DBUriType instance.

Some of the functions that have a different or special action in the DBUriType are described in the following subsections.

getContentType() Function

This function returns the MIME information for the URL. The content type for a DBUriType object can be:

For example, consider the table dbtab under SCOTT:

CREATE TABLE DBTAB( a varchar2(20), b blob);

A DBUriType of '/SCOTT/DBTAB/ROW/A' has a content type of text/xml, because it points to the whole column and the result is XML.

A DBUriType of '/SCOTT/DBTAB/ROW/B' also has a content type of text/xml.

A DBUriType of '/SCOTT/DBTAB/ROW/A/text()' has a content type of text/plain.

A DBUriType of '/SCOTT/DBTAB/ROW/B/text()' has a content type of text/plain.

getClob() and getBlob() Functions

In the case of DBUri, scalar binary data is handled specially. In the case of a getClob() call on a DBUri '/SCOTT/DBTAB/ROW/B/text()' where B is a BLOB column, the data is converted to HEX and sent out.

In the case of a getBlob() call, the data is returned in binary form. However, if an XML document is requested, as in '/SCOTT/DBTAB/ROW/B', then the XML document will contain the binary in HEX form.


XDBUriType is a subtype of UriType and was introduced with Oracle9i. It provides a way to expose documents in Oracle XML DB repository as URIs that can be embedded in any UriType column in a table.

The URL part of the URI is the hierarchical name of the XML document it refers to. The optional fragment part uses the XPath syntax, and is separated from the URL part by '#'.

The following are examples of Oracle XML DB URIs:



Table 17-4 lists the XDBUriType methods. These methods do not take any arguments.

Table 17-4 XDBUriType Methods

Method Description


Returns the value pointed to by the URL as a Character Large Object (CLOB) value. The character encoding is the same as the database character set.

get Blob()

Returns the value pointed to by the URL as a Binary Large Object (BLOB) value.


Returns the URL that is stored in the XDBUriType.


Similar to getUrl, except that it calls the escaping mechanism to escape the characters in the URL as to conform to the URL specification. For example, spaces are converted to the escaped value %20.


Returns the XMLType object corresponding to the contents of the resource that this URI points to. This is provided so that an application that must perform operations other than getClob or getBlob can use the XMLType methods to do those operations.


Returns the MIME information for the resource stored in the Oracle XML DB repository.


Constructor. Returns an XDBUriType for the given URI.

How to Create an Instance of XDBUriType

XDBUriType is automatically registered with UriFactory so that an XDBUriType instance can be generated by providing the URI to the getURI method.

Currently, XDBUriType is the default UriType generated by the UriFactory.getUri method, when the URI does not have any of the recognized prefixes, such as http://,/DBURI, or /ORADB.

All DBUriType URIs should have a prefix of either /DBURI or /ORADB, case insensitive.

Example 17-12 Returning XDBUriType Instance

For example, the following statement returns an XDBUriType instance that refers to /home/scott/doc1.xml:

SELECT sys.UriFactory.getUri('/home/scott/doc1.xml') FROM dual;

Example 17-13 Creating XDBUriType, Inserting Values Into a Purchase Order Table and Selecting All the PurchaseOrders

The following is an example of how XDBUriType is used:

CREATE TABLE uri_tab (poUrl SYS.UriType, poName VARCHAR2(1000));
  -- We create an abstract type column so any type of URI can be used
-- Insert an absolute url into poUrl
-- The factory will create an XDBUriType because there's no prefix.
-- Here, po1.xml is an XML file that is stored in /public/orders/
  (UriFactory.getUri('/public/orders/po1.xml'), 'SomePurchaseOrder');
-- Get all the purchase orders
SELECT e.poUrl.getClob(), poName FROM uri_tab e;

-- Using PL/SQL, you can access table uri_tab as follows: 
CREATE FUNCTION returnclob()
  a UriType;
  -- Get absolute URL for purchase order named like 'Some%'
   SELECT poUrl INTO a FROM uri_tab WHERE poName LIKE 'Some%';
   RETURN a.getClob();

Example 17-14 Retrieving Purchase Orders at a URL Using UriType, getXML() and extractValue()

Because getXML() returns an XMLType, it can be used in the EXTRACT family of operators. For example:

SELECT e.poUrl.getClob() FROM uri_tab e
   WHERE extractValue(e.poUrl.getXML(),'/User') = 'SCOTT';

This statement retrieves all Purchase Orders for user SCOTT.

Creating Oracle Text Indexes on UriType Columns

UriType columns can be indexed natively in Oracle Database using Oracle Text. No special datastore is needed.

Using UriType Objects

This section describes how to store pointers to documents and retrieve these documents across the network, either from the database or a Web site.

Storing Pointers to Documents with UriType

As explained earlier, UriType is an abstract type containing a VARCHAR2 attribute that specifies the URI. The object type has functions for traversing the reference and extracting the data.

You can create columns using UriType to store these pointers in the database. Typically, you declare the column using the UriType, and the objects that you store use one or more of the derived types such as HttpUriType.

Table 17-4 lists some useful UriType methods.


You can plug in any new protocol using the inheritance mechanism. Oracle provides HttpUriType and DBUriType types for handling HTTP protocol and for deciphering DBUri references. For example, you can implement a subtype of UriType to handle the gopher protocol.

Example 17-15 Creating URL References to a List of Purchase Orders

You can create a list of all purchase orders with URL references to them as follows:

CREATE TABLE uri_tab (poUrl SYS.UriType, poName VARCHAR2(200));
       -- We have created abstract type columns; if you know what kind of URIs
       -- you are going to store, you can create the appropriate types.

 -- Insert an absolute URL into SYS.UriType.
 -- The Urifactory creates the correct instance (in this case a HttpUriType)

-- Insert a URL by directly calling the SYS.HttpUriType constructor. 
-- Note the absence of the http:// prefix when creating SYS.HttpUriType
-- instance through the default constructor.
INSERT INTO uri_tab VALUES (sys.HttpUriType(''),'RelPo');

-- Extract all the purchase orders
SELECT e.poUrl.getClob(), poName FROM uri_tab e;

-- In PL/SQL
CREATE FUNCTION returnclob()
  a SYS.UriType;
  SELECT poUrl INTO a FROM uri_Tab WHERE poName LIKE 'RelPo%';
  RETURN a.getClob();


"Creating Instances of UriType Objects with the UriFactory Package" for a description of how to use UriFactory

Using the Substitution Mechanism

You can create columns of the UriType directly and insert HttpUriType, XDBUriType, and DBUriType values into that column. You can also query the column without knowing where the referenced document lies. For example, from Example 17-15, you inserted DBUri references into the uri_tab table as follows:


This insert assumes that there is a purchase order table in the SCOTT schema. Now, the URL column in the table contains values that are pointing through HTTP to documents globally as well as pointing to virtual documents inside the database.

A SELECT on the column using the getClob() method would retrieve the results as a CLOB irrespective of where the document resides. This would retrieve values from the global HTTP address stored in the first row as well as the local DBUri reference.:

SELECT e.poURL.getclob() FROM uri_tab e;

Creating Instances of UriType Objects with the UriFactory Package

The functions in the UriFactory package generate instances of the appropriate UriType subtype (HttpUriType, DBUriType, and XDBUriType). This way, you can avoid hardcoding the implementation in the program and handle whatever kinds of URI strings are used as input. See Table 17-5.

The getUri method takes a string representing any of the supported kinds of URI and returns the appropriate subtype instance. For example:

Registering New UriType Subtypes with the UriFactory Package

The UriFactory package lets you register new UriType subtypes:

  • Derive these types using the CREATE TYPE statement in SQL.

  • Override the default methods to perform specialized processing when retrieving data, or to transform the XML data before displaying it.

  • Pick a new prefix to identify URIs that use this specialized processing.

  • Register the prefix using UriFactory.registerURLHandler, so that the UriFactory package can create an instance of your new subtype when it receives a URI starting with the new prefix you defined.

For example, you can invent a new protocol ecom:// and define a subtype of UriType to handle that protocol. Perhaps the subtype implements some special logic for getCLOB, or does some changes to the XML tags or data within getXML. When you register the ecom:// prefix with UriFactory, any calls to UriFactory.getUri generate the new subtype instance for URIs that begin with the ecom:// prefix.

Table 17-5 UriFactory: Functions and Procedures

UriFactory Function Description

MEMBER FUNCTION escapeUri() RETURN varchar2

Escapes the URL string by replacing the non-URL characters as specified in the Uri-ref specification by their equivalent escape sequence.

FUNCTION unescapeUri() RETURN varchar2

Unescapes a given URL.

PROCEDURE registerUrlHandler(prefix IN varchar2, schemaName in varchar2, typename in varchar2, ignoreCase in boolean:= true, stripprefix in boolean := true)

Registers a particular type name for handling a particular URL.

The type also implements the following static member function: STATIC FUNCTION createUri(url IN varchar2) RETURN <typename>;

This function is called by getUrl() to generate an instance of the type. The stripprefix indicates that the prefix must be stripped off before calling the appropriate constructor for the type.


PROCEDURE unregisterUrlHandler(prefix in varchar2)

Unregisters a URL handler.

Example 17-16 UriFactory: Registering the ecom Protocol

Assume that you are storing different kinds of URIs in a single table:

CREATE TABLE url_tab (urlcol varchar2(80));
-- Insert an HTTP URL reference
INSERT INTO url_tab VALUES ('');
-- Insert a DBUri-ref reference
INSERT INTO url_tab VALUES ('/oradb/SCOTT/EMP/ROW[ENAME="Jack"]');
-- Create a new type to handle a new protocol called ecom://
-- This is just an example template. For it to execute, the implementations
-- of these functions need to be specified.
  -- Must have this for registering with the URL handler
-- Register a new handler for the ecom:// prefixes
  -- The handler type name is ECOMUriTYPE; schema is SCOTT
  -- Ignore the prefix case, so that UriFactory creates the same subtype
  -- for URIs beginning with ECOM://, ecom://, eCom://, and so on.
  -- Strip the prefix before calling the createUri function
  -- so that the string 'ecom://' is not stored inside the
  -- ECOMUriTYPE object. (It is added back automatically when
  -- you call ECOMUriTYPE.getURL.)
  urifactory.registerURLHandler (prefix => 'ecom://',
                                 schemaname => 'SCOTT',
                                 typename => 'ECOMURITYPE',
                                 ignoreprefixcase => TRUE,
                                 stripprefix => TRUE);
-- Insert this new type of URI into the table
INSERT INTO url_tab VALUES ('ECOM://company1/company2=22/comp');
-- Use the factory to generate an instance of the appropriate
-- subtype for each URI in the table.
SELECT urifactory.getUri(urlcol) FROM url_tab;
-- would now generate
HttpUriType(''); -- a Http uri type instance
DBUriType('/oradb/SCOTT/EMP/ROW[ENAME="Jack"]', null); -- a DBUriType
EComUriType('company1/company2=22/comp'); -- an EComUriType instance

Why Define New Subtypes of UriType?

Deriving a new class for each protocol has these advantages:


You can create an instance of DBUriType type by specifying the path expression to the constructor or the UriFactory methods. However, you also need methods to generate these objects dynamically, based on strings stored in table columns. You do this with the SQL function SYS_DBURIGEN().

Example 17-17 SYS_DBURIGEN(): Generating a URI of type DBUriType that points to a Column

The following example uses SYS_DBURIGEN() to generate a URI of datatype DBUriType pointing to the email column of the row in the sample table hr.employees where the employee_id = 206:

SELECT SYS_DBURIGEN(employee_id, email)
  FROM employees
  WHERE employee_id = 206;


SYS_DBURIGEN() takes as its argument one or more columns or attributes, and optionally a rowid, and generates a URI of datatype DBUriType to a particular column or row object. You can use the URI to retrieve an XML document from the database. The function takes an additional parameter to indicate if the text value of the node is needed. See Figure 17-2.

Figure 17-2 SYS_DBURIGEN Syntax

Description of sys_dburigen.gif follows
Description of the illustration sys_dburigen.gif

All columns or attributes referenced must reside in the same table. They must reference a unique value. If you specify multiple columns, then the initial columns identify the row in the database, and the last column identifies the column within the row.

By default, the URI points to a formatted XML document. To point only to the text of the document, specify the optional text() keyword.

See Also:

Oracle Database SQL Reference for SYS_DBURIGEN syntax details

If you do not specify an XML schema, then Oracle interprets the table or view name as a public synonym.

Rules for Passing Columns or Object Attributes to SYS_DBURIGEN()

The column or attribute passed to the SYS_DBURIGEN() function must obey the following rules:

  • Unique mapping: The column or object attribute must be uniquely mappable back to the table or view from which it comes. The only virtual columns allowed are the VALUE and REF operators. The column may come from a TABLE() subquery or an inline view, as long as the inline view does not rename the columns.

  • Key columns: Either the rowid or a set of key columns must be specified. The list of key columns is not required to be declared as a unique or primary key, as long as the columns uniquely identify a particular row in the result.

  • Same table: All columns referenced in the SYS_DBURIGEN() function must come from the same table or view.

  • PUBLIC element: If the table or view pointed by the rowid or key columns does not have a database schema specified, then the PUBLIC keyword is used instead of the schema. When the DBUri is accessed, the table name resolves to the same table, synonym, or view that was visible by that name when the DBUri was created.

  • TEXT function: DBUri, by default, retrieves an XML document containing the result. To retrieve only the text value, use the text() keyword as the final argument to the function.

    For example:

    SELECT SYS_DBURIGEN(empno,ename,'text()') FROM scott.emp,
      WHERE empno=7369;

    or you can just generates a URL of the form:

  • Single-column argument: If there is a single-column argument, then the column is used both as the key column to identify the row and as the referenced column.

Example 17-18 Passing Columns With Single Arguments to SYS_DBURIGEN()

For example:

  WHERE empno=7369;

uses the empno both as the key column and the referenced column, generating a URL of the form:


for the row with empno=7369


Example 17-19 Inserting Database References Using SYS_DBURIGEN()

CREATE TABLE doc_list_tab(docno NUMBER PRIMARY KEY, doc_ref SYS.DBUriType);
-- Insert /SCOTT/EMP/ROW[rowid='xxx']/EMPNO
INSERT INTO doc_list_tab 
         (SELECT SYS_DBURIGEN(rowid, empno) FROM emp WHERE empno=100));
-- Insert a Uri-ref to point to the ename column of emp!
INSERT INTO doc_list_tab
         (SELECT SYS_DBURIGEN(empno, ename) FROM emp WHERE empno=7369));
-- Result of the DBURIGEN looks like /SCOTT/EMP/ROW[EMPNO=7369]/ENAME

Returning Partial Results

When selecting the results of a large column, you might want to retrieve only a portion of the result and create a URL to the column instead. For example, consider the case of a travel story Web site. If all the travel stories are stored in a table, and users search for a set of relevant stories, then you do not want to list each entire story in the result page. Instead, you show the first 100 characters or gist of the story and then return a URL to the full story. This can be done as follows:

Example 17-20 Returning a Portion of the Results By Creating a View and Using SYS_DBURIGEN()

Assume that the travel story table is defined as follows:

CREATE TABLE travel_story (story_name VARCHAR2(100), 
                           story CLOB);
-- Insert Some Value
INSERT INTO travel_story
  VALUES ('Egypt', 'This is the story of my time in Egypt....');

Now, you create a function that returns only the first 20 characters from the story:

  res VARCHAR2(20);
  amount NUMBER := 20;
BEGIN, amount, 1, res);
  RETURN res;

Now, you create a view that selects out only the first 100 characters from the story and then returns a DBUri reference to the story column:

CREATE VIEW travel_view AS
  SELECT story_name, charfunc(story) short_story,
         SYS_DBURIGEN(story_name, story, 'text()') story_link
  FROM travel_story;

Now, a SELECT from the view returns the following:

SELECT * FROM travel_view;
Egypt      This is the story of SYS.DBUriType('/PUBLIC/TRAVEL_STORY/ROW[STORY_NAME='Egypt']/STORY/text()')


You can use SYS_DBURIGEN() in the RETURNING clause of DML statements to retrieve the URL of an object as it is inserted.

Example 17-21 Using SYS_DBURIGEN in the RETURNING Clause to Retrieve the URL of an Object

For example, consider the table CLOB_TAB:

CREATE TABLE clob_tab (docid NUMBER, doc CLOB);

When you insert a document, you might want to store the URL of that document in another table, URI_TAB.

CREATE TABLE uri_tab (docs SYS.DBUriType);

You can specify the storage of the URL of that document as part of the insert into CLOB_TAB, using the RETURNING clause and the EXECUTE IMMEDIATE syntax to run the SYS_DBURIGEN function inside PL/SQL as follows:

  ret SYS.dburitype;
  -- execute the insert and get the url
    'INSERT INTO clob_tab VALUES (1,''TEMP CLOB TEST'') 
       RETURNING SYS_DBURIGEN(docid, doc, ''text()'') INTO :1'
  -- Insert the url into uri_tab
  INSERT INTO uri_tab VALUES (ret);

The URL created has the form:



The text() keyword is appended to the end indicating that you want the URL to return just the CLOB value and not an XML document enclosing the CLOB text.

Turning a URL into a Database Query with DBUri Servlet

You can make table data accessible from your browser or any Web client, using the URI notation within a URL to specify the data to retrieve:

DBUri Servlet Mechanism

For the preceding methods, a servlet runs for accessing this information through HTTP. This servlet takes in a path expression following the servlet name as the DBUri reference and produces the document pointed to by the DBUri to the output stream.

The generated document can be a Web page, an XML document, plain text, and so on. You can specify the MIME type so that the browser or other application knows what kind of content to expect:

  • By default, the servlet can produce MIME types of text/xml and text/plain. If the URI ends in a text() function, then the text/plain MIME type is used, else an XML document is generated with the MIME type of text/xml.

  • You can override the MIME type and set it to binary/x-jpeg or some other value using the contenttype argument to the servlet.

Example 17-22 URL for Overriding the MIME Type by Generating the contenttype Argument, to Retrieve the empno Column of Table Employee

For example, to retrieve the empno column of the employee table, you can write a URL such as one of the following:

-- Generates a contenttype of text/plain[EMPNO=7369]/ENAME/text()
-- Generates a contenttype of text/xml[EMPNO=7369/ENAME

where the computer is running Oracle Database, with a Web service at port 8080 listening to requests. oradb is the virtual path that maps to the servlet.

DBUri Servlet: Optional Arguments

Table 17-6 describes the three optional arguments you can pass to DBUri servlet to customize the output.

Table 17-6 DBUri Servlet: Optional Arguments

Argument Description


Changes the default root tag name for the XML document. For example:

This can also be used to put a tag around a URI that points to multiple rows. For example:


Specifies the MIME type of the returned document. For example:


This argument passes a URL to UriFactory, which in turn retrieves the XSL stylehseet at that location. This style sheet is then applied to the XML document being returned by the servlet. For example:


When using XPath notation in the URL for this servlet, you may have to precede certain characters with an escape character such as square brackets. You can use the getExternalUrl() functions in the UriType types to get an escaped version of the URL.

Installing DBUri Servlet

DbUriServlet is built into the database, and the installation is handled by the Oracle XML DB configuration file. To customize the installation of the servlet, you must edit it. You can edit the config file, xdbconfig.xml under the Oracle XML DB user, through WebDAV, FTP, from Oracle Enterprise Manager, or in the database. To update the file using FTP or WebDAV, simply download the document, edit it as necessary, and save it back in the database. There are several things that can be customized using the configuration file.

Notice that the servlet is installed at /oradb/* specified in the servlet-pattern tag. The * is necessary to indicate that any path following oradb is to be mapped to the same servlet. The oradb is published as the virtual path. Here, you can change the path that will be used to access the servlet.

Example 17-23 Installing DBUri Servlet Under /dburi/*

For example, to have the servlet installed under /dburi/*, you can run the following PL/SQL:

  doc XMLType;
  doc2 XMLType;
  doc := dbms_xdb.cfg_get();
    INTO doc2 FROM DUAL;

Security parameters, the servlet display-name, and the description can also be customized in the xdbconfig.xml configuration file. See Appendix A, " Installing and Configuring Oracle XML DB" and Chapter 25, " Writing Oracle XML DB Applications in Java". The servlet can be removed by deleting the servlet-pattern for this servlet. This can also be done using updateXML() to update the servlet-mapping element to null.

DBUri Security

Servlet security is handled by Oracle Database using roles. When users log in to the servlet, they use their database username and password. The servlet will check to make sure the user logging in belongs to one of the roles specified in the configuration file. The roles allowed to access the servlet are specified in the security-role-ref tag. By default, the servlet is available to the special role authenticatedUser. Any user who logs into the servlet with any valid database username and password belongs to this role.

This parameter can be changed to restrict access to any role(s) in the database. To change from the default authenticated-user role to a role that you have created, say servlet-users, run:

  doc XMLType;
  doc2 XMLType;
  doc3 XMLType;
  doc := DBMS_XDB.cfg_get();
  SELECT updateXML(doc,
    INTO doc2 FROM DUAL;
  SELECT updateXML(doc2,
    INTO doc3 FROM DUAL;

Configuring the UriFactory Package to Handle DBUris

The UriFactory, as explained in "Creating Instances of UriType Objects with the UriFactory Package", takes a URL and generates the appropriate subtypes of the UriType to handle the corresponding protocol. For HTTP URLs, UriFactory creates instances of the HttpUriType. But when you have an HTTP URL that represents a URI path, it is more efficient to store and process it as a DBUriType instance in the database. The DBUriType processing involves fewer layers of communication and potentially fewer character conversions.

After you install OraDBUriServlet, so that any URL such as http://machine-name/servlets/oradb/ gets handled by that servlet, you can configure the UriFactory to use that prefix and create instances of the DBUriType instead of HttpUriType:

 -- register a new handler for the dburi prefix..     
        ,'SYS','DBUriTYPE', true,true);

After you execute this block in your session, any UriFactory.getUri() call in that session automatically creates an instance of the DBUriType for those HTTP URLs that have the prefix.

See Also:

Oracle XML API Reference for details of all functions in DBUriFactory