19 Accessing Data Through URIs

This chapter describes how to generate and store URLs in the database and how to retrieve data pointed to by those URLs. Three kinds of URIs are discussed:

This chapter contains these topics:

Overview of Oracle XML DB URL Features

The two main features described in this chapter are these:

  • Using paths as an indirection mechanism – You can store a path in the database and then access its target indirectly by referring to the path. The paths in question are various kinds of Uniform Resource Identifier (URI).

  • Using paths that target database data to produce XML documents – One kind of URI that you can use for indirection in particular, a DBUri, provides a convenient XPath notation for addressing database data. You can use a DBUri to construct an XML document that contains database data and whose structure reflects the database structure.

URIs and URLs

In developing Web-based XML applications, you often refer to data located on a network using Uniform Resource Identifiers, or URIs. A URL, or Uniform Resource Locator, is a URI that accesses an object using an Internet protocol.

A URI has two parts, separated by a number sign (#):

  • A URL part, that identifies a document.

  • A fragment part, that identifies a fragment within the document. The notation for the fragment depends on the document type. For HTML documents, it is an anchor name. For XML documents, it is an XPath expression.

These are typical URIs:

  • For HTML – http://www.url.com/document1#some_anchor, where some_anchor is a named anchor in the HTML document.

  • For XML – http://www.xml.com/xml_doc#/po/cust/custname, where:

    • http://www.xml.com/xml_doc identifies the location of the XML document.

    • /po/cust/custname identifies a fragment within the document. This portion is defined by the W3C XPointer recommendation.

See Also:

URIType and its Subtypes

Oracle XML DB can represent paths of various kinds as database objects. These are the available path object types:

  • HTTPURIType – An object of this type is called an HTTPUri and represents a URL that begins with http://. With HTTPURIType, you can create objects that represent links to remote Web pages (or files) and retrieve those Web pages by calling object methods. This type implements the Hyper Text Transfer Protocol (HTTP(S)) for accessing remote Web pages. HTTPURIType uses package UTL_HTTP to fetch data, so session settings for this package can also be used to influence HTTP fetches.

  • DBURIType – An object of this type is called a DBUri and represents a URI that targets database data – a table, one or more rows, or a single column. With DBURIType, you can create objects that represent links to database data, and retrieve such data as XML by calling object methods. A DBUri uses a simple form of XPath expression as its URI syntax – for example, the following XPath expression is a DBUri reference to the row of database table hr, column employees where column first_name has value Jack:

    /HR/EMPLOYEES/ROW[FIRST_NAME="Jack"] 
    
  • XDBURIType – An object of this type is called an XDBUri and represents a URI that targets a resource in Oracle XML DB Repository. With XDBURIType, you can create objects that represent links to repository resources, and retrieve all or part of any resource by calling object methods. The URI syntax for an XDBUri is a repository resource address optionally followed by an XPath expression. For example, /public/hr/doc1.xml#/purchaseOrder/lineItem is an XDBUri reference to the lineItem child element of the root element purchaseOrder in repository file doc1.xml in folder /public/hr.

Each of these object types is derived from an abstract object type, URIType. As an abstract type, it has no instances (objects); only its subtypes have instances.

Type URIType provides the following features:

  • Unified access to data stored inside and outside the server. Because you can use URIType values to store pointers to HTTP(S) and DBUris, you can create queries and indexes without worrying about where the data resides.

  • Mapping of URIs in XML Documents to Database Columns. When an XML document is shredded to database tables and columns, any URIs contained in the document are mapped to database columns of the appropriate URIType subtype.

You can reference data stored in relational columns and expose it to the external world using URIs. Oracle Database provides a standard servlet, DBUriServlet, that interprets DBUris. It also provides PL/SQL package UTL_HTTP and Java class java.net.URL, which you can use to fetch URL references.

URIType columns can be indexed natively in Oracle Database using Oracle Text – no special datastore is needed.

See Also:

DBUris and XDBUris – What For?

The following are typical uses of DBUris and XDBUris:

  • You can reference XSLT style sheets from within database-generated Web pages. Package DBMS_METADATA uses DBUris to reference XSL style sheets. An XDBUri can be used to reference XSLT style sheets stored in Oracle XML DB Repository.

  • You can reference HTML text, images and other data stored in the database. URLs can be used to point to data stored in database tables or in repository folders.

  • You can improve performance by bypassing the Web server. Replace a global URL in your XML document with a reference to the database, and use a servlet, a DBUri, or a XDBUri to retrieve the targeted content. Using a DBUri or an XDBUri generally provides better performance than using a servlet, because you interact directly with the database rather than through a Web server.

  • With a DBUri, you can access an XML document in the database without using SQL.

  • Whenever a repository resource is stored in a database table to which you have access, you can use either an XDBUri or a DBUri to access its content.

See Also:

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

URIType Methods

Abstract object type URIType includes methods that can be used with each of its subtypes. Each of these methods can be overridden by any of the subtypes. Table 19-1 lists the URIType methods. In addition, each of the subtypes has a constructor with the same name as the subtype.

Table 19-1 URIType Methods

URIType Method Description
getURL()

Returns the URL of the URIType instance.

Use this method instead of referencing a URL directly. URIType subtypes override this method to provide the correct URL. For example, HTTPURIType stores a URL without prefix http://. Method getURL() then prepends the prefix and returns the entire URL.

getExternalURL()

Similar to getURL(), but getExternalURL() escapes characters in the URL, to conform with the URL specification. For example, spaces are converted to the escaped value %20.

getContentType()

Returns the MIME content type for the URI.

HTTPUri: The URL is followed and the MIME header examined, in order to return the actual content type.

DBUri: The returned content type is either text/plain (for a scalar value) or text/xml (otherwise).

XDBUri: The value of the ContentType metadata property of the repository resource is returned.

getClob()

Returns the target of the URI as a CLOB value. The database character set is used for encoding the data.

DBUri: XML data is returned (unless node-test text() is used, in which case the targeted data is returned as is). When a BLOB column is targeted, the binary data in the column is translated as hexadecimal character data.

getBlob()

Returns the target of the URI as a BLOB value. No character conversion is performed, and the character encoding is that of the URI target. This method can also be used to fetch binary data.

DBUri: When applied to a DBUri that targets a BLOB column, getBlob() returns the binary data translated as hexadecimal character data. When applied to a DBUri that targets non-binary data, the data is returned in the database character set.

getXML()

Returns the target of the URI as an XMLType instance. Using this, an application that performs operations other than getClob() and getBlob() can use XMLType methods to do those operations. This throws an exception if the URI does not target a well-formed XML document.

createURI()

Constructs an instance of one of the URIType subtypes.


HTTPURIType Method getContentType()

HTTPURIType method getContentType() returns the actual MIME information for its targeted document. You can use this information to decide whether to retrieve the document as a BLOB value or a CLOB value. For example, you might treat a Web page with a MIME type of x/jpeg as a BLOB value, and one with a MIME type of text/plain or text/html as a CLOB value.

Example 19-1 Using HTTPURIType Method getContentType()

In this example, the HTTP content type is tested to determine whether to retrieve data as a CLOB or BLOB value. The content-type data is the HTTP header, for HTTPURIType, or the metadata of the database column, for DBURIType.

DECLARE
  httpuri HTTPURIType;
  y CLOB;
  x BLOB;
BEGIN
  httpuri := HTTPURIType('http://www.oracle.com/object1');
  DBMS_OUTPUT.put_line(httpuri.getContentType());
  IF httpuri.getContentType() = 'text/html'
  THEN
     y := httpuri.getClob();
  END IF;
  IF httpuri.getContentType() = 'application-x/bin'
  THEN
     x := httpuri.getBlob();
  END IF;
END;
/
text/html

DBURIType Method getContentType()

Method getContentType() returns the MIME information for a URL. If a DBUri targets a scalar value, then the MIME content type returned is text/plain; otherwise, it is text/xml. For example, consider table dbtab:

CREATE TABLE DBTAB(a VARCHAR2(20), b BLOB);

DBUris corresponding to the following XPath expressions have content type text/xml, because each targets a complete column of XML data.

  • /HR/DBTAB/ROW/A

  • /HR/DBTAB/ROW/B

DBUris corresponding to the following XPath expressions have content type text/plain, because each targets a scalar value.

  • /HR/DBTAB/ROW/A/text()

  • /HR/DBTAB/ROW/B/text()

DBURIType Method getClob()

When method getClob() is applied to a DBUri, the targeted data is returned as XML data, using the targeted column or table name as an XML element name. If the target XPath uses node-test text(), then the data is returned as text without an enclosing XML tag. In both cases, the returned data is in the database character set.

For example: If appliedto a DBUri with XPath /HR/DBTAB/ROW/A/text(), where A is a non-binary column, the data in column A is returned as is. Without XPath node-test text(), the result is the data wrapped in XML:

<HR><DBTAB><ROW><A>...data_in_column_A...</A></ROW></DBTAB></HR>

When applied toa DBUri that targets a binary (BLOB) column, the binary data in the column is translated as hexadecimal character data.

For example: If applied to a DBUri with XPath /HR/DBTAB/ROW/B/text(), where B is a BLOB column, the targeted binary data is translated to hexadecimal character data and returned. Without XPath node-test text(), the result is the translated data wrapped in XML:

<HR><DBTAB><ROW><B>...data_translated_to_hex...</B></ROW></DBTAB></HR>

DBURIType Method getBlob()

When applied to a DBUri that targets a BLOB column, getBlob() returns the binary data translated as hexadecimal character data. When applied to a DBUri that targets non-binary data, getBlob() returns the data (as a BLOB value) in the database character set.

For example, consider table dbtab:

CREATE TABLE DBTAB(a VARCHAR2(20), b BLOB);

When getBlob() is applied to a DBUri corresponding to XPath expression /HR/DBTAB/ROW/B, it returns a BLOB value containing an XML document with root element B whose content is the hexadecimal-character translation of the binary data of column B.

When getBlob() is applied to a DBUri corresponding to XPath expression /HR/DBTAB/ROW/B/text(), it returns a BLOB value containing only the hexadecimal-character translation of the binary data of column B.

When getBlob() is applied to a DBUri corresponding to XPath expression /HR/DBTAB/ROW/A/text(), which targets non-binary data, it returns a BLOB value containing the data of column A, in the database character set.

Accessing Data Using URIType Instances

To use instances of URIType subtypes for indirection, you generally store such instances in the database and then use them in queries with a method such as getClob() to retrieve the targeted data. This section illustrates how to do this.

You can create database columns using URIType or any of its subtypes, or you can store just the text of each URI as a string and then create the needed URIType instances on demand, when the URIs are accessed. You can store objects of different URIType subtypes in the same URIType database column.

You can also define your own object types that inherit from the URIType subtypes. Deriving new types lets you use custom techniques to retrieve, transform, or filter data.

See Also:

Example 19-2 Creating and Querying a URI Column

This example stores an HTTPUri and a DBUri (instances of URIType subtypes HTTPURIType and DBURIType) in the same database column of type URIType. A query retrieves the data addressed by each of the URIs. The first URI is a Web-page URL; the second references data in the employees table of standard schema hr. (For brevity, only the beginning of the Web page is shown.)

CREATE TABLE uri_tab (url URIType);
Table created.
 
INSERT INTO uri_tab VALUES (HTTPURIType.createURI('http://www.oracle.com'));
1 row created.
 
INSERT INTO uri_tab VALUES (DBURIType.createURI(
                              '/HR/EMPLOYEES/ROW[FIRST_NAME="Jack"]'));
1 row created.
 
SELECT e.url.getClob() FROM uri_tab e;
 
E.URL.GETCLOB()
-------------------------------------------------------------------
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD>
<TITLE>Oracle Corporation</TITLE>
. . .
 
<?xml version="1.0"?>
 <ROW>
  <EMPLOYEE_ID>177</EMPLOYEE_ID>
  <FIRST_NAME>Jack</FIRST_NAME>
  <LAST_NAME>Livingston</LAST_NAME>
  <EMAIL>JLIVINGS</EMAIL>
  <PHONE_NUMBER>011.44.1644.429264</PHONE_NUMBER>
  <HIRE_DATE>23-APR-98</HIRE_DATE>
  <JOB_ID>SA_REP</JOB_ID>
  <SALARY>8400</SALARY>
  <COMMISSION_PCT>.2</COMMISSION_PCT>
  <MANAGER_ID>149</MANAGER_ID>
  <DEPARTMENT_ID>80</DEPARTMENT_ID>
 </ROW>
 
2 rows selected.

In order to use URIType method createURI(), you must know the particular URIType subtype to use. Method getURI() of package URIFACTORY lets you instead use the flexibility of late binding, determining the particular type information at runtime.

URIFACTORY.getURI() takes as argument a URI string; it returns a URIType instance of the appropriate subtype (HTTPURIType, DBURIType, or XDBURIType), based on the form of the URI string:

  • If the URI starts with http://, then getURI() creates and returns an HTTPUri.

  • If the URI starts with either /oradb/ or /dburi/, then getURI() creates and returns a DBUri.

  • Otherwise, getURI() creates and returns an XDBUri.

Example 19-3 Using Different Kinds of URI, Created in Different Ways

This example is similar to Example 19-2. However, it uses two different ways to obtain documents targeted by URIs:

  • Method SYS.URIFACTORY.getURI() with absolute URIs:

    • an HTTPUri that targets HTTP address http://www.oracle.com

    • a DBUri that targets database address /oradb/HR/EMPLOYEES/ROW[EMPLOYEE_ID=200]

  • Constructor SYS.HTTPURIType() with a relative URL (no http://). The same HTTPUri is used as for the absolute URI: the Oracle home page.

In this example, the URI strings passed to getURI() are hard-coded, but they could just as easily be string values that are obtained by an application at runtime.

CREATE TABLE uri_tab (docUrl SYS.URIType, docName VARCHAR2(200));
Table created.
 
-- Insert an HTTPUri with absolute URL into SYS.URIType using URIFACTORY.
-- The target is Oracle home page.
INSERT INTO uri_tab VALUES
  (SYS.URIFACTORY.getURI('http://www.oracle.com'), 'AbsURL');
1 row created.
 
-- Insert an HTTPUri with relative URL using constructor SYS.HTTPURIType.
-- Note the absence of prefix http://. The target is the same.
INSERT INTO uri_tab VALUES (SYS.HTTPURIType('www.oracle.com'), 'RelURL');
1 row created.

-- Insert a DBUri that targets employee data from database table hr.employees.
INSERT INTO uri_tab VALUES
  (SYS.URIFACTORY.getURI('/oradb/HR/EMPLOYEES/ROW[EMPLOYEE_ID=200]'), 'Emp200');
1 row created.

-- Extract all of the documents.
SELECT e.docUrl.getClob(), docName FROM uri_tab e;
 
E.DOCURL.GETCLOB()
-----------------
DOCNAME
------------------------------------
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD>
<TITLE>Oracle Corporation</TITLE>
. . .
AbsURL
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD>
<TITLE>Oracle Corporation</TITLE>
. . .
RelURL

<?xml version="1.0"?>
 <ROW>
  <EMPLOYEE_ID>200</EMPLOYEE_ID>
  <FIRST_NAME>Jennifer</FIRST_NAME>
  <LAST_NAME>Whalen</LAST_NAME>
  <EMAIL>JWHALEN</EMAIL>
  <PHONE_NUMBER>515.123.4444</PHONE_NUMBER>
  <HIRE_DATE>17-SEP-87</HIRE_DATE>
  <JOB_ID>AD_ASST</JOB_ID>
  <SALARY>4400</SALARY>
  <MANAGER_ID>101</MANAGER_ID>
  <DEPARTMENT_ID>10</DEPARTMENT_ID>
 </ROW>
Emp200

3 rows selected.
 
-- In PL/SQL
CREATE OR REPLACE FUNCTION returnclob
RETURN CLOB
IS
  a SYS.URIType;
BEGIN
  SELECT docUrl INTO a FROM uri_Tab WHERE docName LIKE 'Emp200%';
  RETURN a.getClob;
END;
/
Function created.
 
SELECT returnclob() FROM DUAL;
 
RETURNCLOB()
---------------------------------------------------------------
<?xml version="1.0"?>
 <ROW>
  <EMPLOYEE_ID>200</EMPLOYEE_ID>
  <FIRST_NAME>Jennifer</FIRST_NAME>
  <LAST_NAME>Whalen</LAST_NAME>
  <EMAIL>JWHALEN</EMAIL>
  <PHONE_NUMBER>515.123.4444</PHONE_NUMBER>
  <HIRE_DATE>17-SEP-87</HIRE_DATE>
  <JOB_ID>AD_ASST</JOB_ID>
  <SALARY>4400</SALARY>
  <MANAGER_ID>101</MANAGER_ID>
  <DEPARTMENT_ID>10</DEPARTMENT_ID>
 </ROW>
 
1 row selected.

XDBUris: Pointers to Repository Resources

XDBURIType is a subtype of URIType that provides a way to expose resources in Oracle XML DB Repository using URIs. Instances of type XDBURIType are called XDBUris.

XDBUri URI Syntax

The URL portion of an XDBUri URI is the hierarchical address of the targeted repository resource – it is a repository path (not an XPath expression).

The optional fragment portion of the URI uses the XPath syntax, and is separated from the URL part by a number-sign (#). It is appropriate only if the targeted resource is an XML document, in which case the fragment portion targets one or more parts of the XML document. If the targeted resource is not an XML document, then omit the fragment and number-sign.

The following are examples of XDBUri URIs:

  • /public/hr/image27.jpg

  • /public/hr/doc1.xml#/PurchaseOrder/LineItem

Based on the form of these URIs, we can determine the following:

  • /public/hr is a folder resource in Oracle XML DB Repository.

  • image27.jpg and doc1.xml are resources in folder /public/hr.

  • Resource doc1.xml is a file resource, and it contains an XML document.

  • The XPath expression /PurchaseOrder/LineItem refers to the LineItem child element in element PurchaseOrder of XML document doc1.xml.

You can create an XDBUri using method getURI() of package URIFACTORY.

XDBURIType is the default URIType used when generating instances using URIFACTORY method getURI(), unless the URI has one of the recognized prefixes http://, /dburi, or /oradb.

For example, if resource doc1.xml is present in repository folder /public/hr, then the following query will return an XDBUri that targets that resource.

SELECT SYS.URIFACTORY.getURI('/public/hr/doc1.xml') FROM DUAL;

It is the lack of a special prefix that determines that the type is XDBURIType, not any particular resource file extension or the presence of # followed by an XPath expression; if the resource were named foo.bar instead of doc1.xml, the returned URIType instance would still be an XDBUri.

XDBUri Examples

Example 19-4 Using an XDBUri to Access a Repository Resource by URI

This example creates an XDBUri, inserts values into a purchase-order table, and then selects all of the purchase orders. Because there is no special prefix used in the URI passed to URIFACTORY.getURI(), the created URIType instance is an XDBUri.

DECLARE
res BOOLEAN;
postring VARCHAR2(100):= '<?xml version="1.0"?>
<ROW>
<PO>999</PO>
</ROW>';
BEGIN
res:=DBMS_XDB.createFolder('/public/orders/');
res:=DBMS_XDB.createResource('/public/orders/po1.xml', postring);
END;
/
PL/SQL procedure successfully completed.
 
CREATE TABLE uri_tab (poUrl SYS.URIType, poName VARCHAR2(1000));
Table created.
 
-- 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 is no prefix.
-- Here, po1.xml is an XML file that is stored in /public/orders/
-- of the XML repository.
INSERT INTO uri_tab VALUES
  (URIFACTORY.getURI('/public/orders/po1.xml'), 'SomePurchaseOrder');
1 row created.
 
-- Get all the purchase orders
SELECT e.poUrl.getClob(), poName FROM uri_tab e;
 
E.POURL.GETCLOB()
-----------------
PONAME
------
<?xml version="1.0"?>
<ROW>
<PO>999</PO>
</ROW>
SomePurchaseOrder
 
1 row selected.
 
-- Using PL/SQL, you can access table uri_tab as follows:
CREATE OR REPLACE FUNCTION returnclob
RETURN CLOB
IS
  a URIType;
BEGIN
  -- Get absolute URL for purchase order named like 'Some%'
   SELECT poUrl INTO a FROM uri_tab WHERE poName LIKE 'Some%';
   RETURN a.getClob();
END;
/ 
Function created.
 
SELECT returnclob() FROM DUAL;
 
RETURNCLOB()
---------------------
<?xml version="1.0"?>
<ROW>
<PO>999</PO>
</ROW>
 
1 row selected.

Example 19-5 Using getXML() with EXTRACTVALUE

Because method getXML() returns an XMLType instance, you can use it with SQL functions like extractValue. This query retrieves all purchase orders numbered 999:

SELECT e.poUrl.getClob() FROM uri_tab e
   WHERE extractValue(e.poUrl.getXML(), '/ROW/PO') = '999';

E.POURL.GETCLOB()
---------------------
<?xml version="1.0"?>
<ROW>
<PO>999</PO>
</ROW>
 
1 row selected.

DBUris: Pointers to Database Data

A DBUri is a URI that targets database data. As for all instances of URIType subtypes, a DBUri provides an indirection mechanism for accessing data. In addition, DBURIType lets you do the following:

  • Address database data using XPath notation. This, in effect, lets you visualize and access the database as if it were XML data.

    For example, a DBUri can use an expression such as /HR/EMPLOYEES/ROW[FIRST_NAME="Jack"] to target the row of database table hr, column employees where column first_name has value Jack.

  • Construct an XML document that contains database data targeted by a DBUri and whose structure reflects the database structure.

    For example: A DBUri with XPath /HR/DBTAB/ROW/A can be used to construct an XML document that wraps the data of column A in XML elements that reflect the database structure and are named accordingly:

    <HR><DBTAB><ROW><A>...data_in_column_A...</A></ROW></DBTAB></HR>
    
    

A DBUri does not reference a global location as does an HTTPUri. You can, however, also access objects addressed by a DBUri in a global manner, by appending the DBUri to an HTTPUri that identifies a servlet that handles DBUris – see "DBUriServlet" .

Viewing the Database as XML Data

You can only access those database schemas to which you have been granted access privileges. This portion of the database is, in effect, your own view of the database.

Using DBURIType, you can have corresponding XML views of the database, which are portions of the database to which you have access, presented in the form of XML data. This means all kinds database data, not just data that is stored as XML. When visualized this way, the database data is effectively wrapped in XML elements, resulting in one or more XML documents.

Such "XML views" are not database views, in the technical sense of the term; "view" here means only an abstract perspective that can be useful for understanding DBURIType. You can think of DBURIType as providing a way to visualize and access the database as if it were XML data.

However, DBURIType does not just provide an exercise in visualization and an additional means to access database data. Each "XML view" can be realized as an XML document – that is, you can use DBURIType to generate XML documents using database data.

All of this is another way of saying that DBURIType lets you use XPath notation to 1) address and access any database data to which you have access and 2) construct XML representations of that data.

Figure 19-1 illustrates the relation between a relational table, hr.employees, a corresponding "XML view" of a portion of that table, and the corresponding DBUri URI (a simple XPath expression). In this case, the portion of the data exposed as XML is the row where employee_id is 200. The URI can be used to access the data and construct an XML document that reflects the "XML view".

Figure 19-1 A DBUri Corresponds to an XML Vizualization of Relational Data

Description of Figure 19-1 follows
Description of "Figure 19-1 A DBUri Corresponds to an XML Vizualization of Relational Data"

The XML elements in the "XML view" and the steps in the URI XPath expression both reflect the database table and column names. Note the use of ROW to indicate a row in the database table – both in the "XML view" and in the URI XPath expression.

Note also that the XPath expression contains a root-element step, oradb. This is used to indicate that the URI corresponds to a DBUri, not an HTTPUri or an XDBUri. Whenever this correspondence is understood from context, this XPath step can be skipped. For example, if it is known that the path in question is a path to database data, the following URIs are equivalent:

  • /oradb/HR/EMPLOYEES/ROW[EMPLOYEE_ID=200]/LAST_NAME

  • /HR/EMPLOYEES/ROW[EMPLOYEE_ID=200]/LAST_NAME

Whenever the URI context is not clear, however, you must use the prefix /oradb to distinguish a URI as corresponding to a DBUri. In particular, you must supply the prefix to URIFACTORY methods and to DBUriServlet.

DBUri URI Syntax

An XPath expression is a path into XML data that addresses one or more XML nodes. A DBUri exploits the notion of a virtual XML user visualization of the database to use a simple form of XPath expression as a URI to address database data. This is so, regardless of the type of data, in particular, whether or not the data is XML.

Thus, for DBURIType, Oracle Database does not support the full XPath or XPointer syntax; only a subset is allowed. There are no syntax restrictions for XDBUri XPath expressions. There is also an exception in the DBUri case: data in XMLType tables. For an XMLType table, the simple XPath form is used to address the table itself within the database. Then, to address particular XML data in the table, the remainder of the XPath expression can use the full XPath syntax. This exception applies only to XMLType tables, not to XMLType columns.

In any case, unlike an XDBUri, a DBUri URI does not use a number-sign (#) to separate the URL portion of a URI from a fragment (XPath) portion. DBURIType does not use URI fragments; the entire URI is treated as a (simple) XPath expression.

You can create DBUris to any database data to which you have access. XPaths such as the following are allowed:

  • /database_schema/table

  • /database_schema/table/ROW[predicate_expression]/column

  • /database_schema/table/ROW[predicate_expression]/object_column/attribute

  • /database_schema/XMLType_table/ROW/XPath_expression

In the last case, XMLType_table is an XMLType table, and XPath_expression is any XPath expression. For tables that are not XMLType, a DBUri XPath expression must end at a column; it cannot address specific data inside a column. This restriction includes XMLType columns, LOB columns, and VARCHAR2 columns that contain XML data.

A DBUri XPath expression can do any of the following:

  • Target an entire table.

    For example, /HR/EMPLOYEES targets table employees of database schema hr.

  • Include XPath predicates at any step in the path, except the database schema and table steps.

    For example, /HR/EMPLOYEES/ROW[EMPLOYEE_ID=200]/EMAIL targets the email column of table hr.employees, where employee_id is 200.

  • Use the text() XPath node test on data with scalar content. This is the only node test that can be used, and it cannot be used with the table or row step.

The following can be used in DBUri (XPath) predicate expressions:

  • Boolean operators and, or, and not

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

A DBUri XPath expression must do all of the following:

  • Use only the child XPath axis – other axes, such as parent, are not allowed.

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

  • Specify a database view or table name.

  • Include a ROW step, if a database column is targeted.

  • Identify a single data value, which can be an object-type instance or a collection.

  • Result in well-formed XML when it is used to generate XML data using database data.

    An example of a DBUri that does not result in well-formed XML is /HR/EMPLOYEES/ROW/LAST_NAME. It returns more than one <LAST_NAME> element fragment, with no single root element.

  • Use none of the following:

    • * (wildcard)

    • . (self)

    • .. (parent)

    • // (descendent or self)

    • XPath functions, such as count

A DBUri XPath expression can optionally be prefixed by /oradb or /dburi (the two are equivalent) to distinguish it. This prefix is case-insensitive. However, the rest of the DBUri XPath expression is case-sensitive, as are XPaths generally. Thus, for example, to specify database column hr.employees as a DBUri XPath expression, you must use HR/EMPLOYEES, not hr/employees (or a mixed-case combination), because table and column names are uppercase, by default.

See Also:

http://www.w3.org/TR/xpath on XPath notation

DBUris are Scoped to a Database and Session

The content of the "XML views" you have of the database, and hence of the XML documents that you can construct, reflects the permissions you have to access particular database data at a given time. That is, a DBUri is scoped to a given database session, so the same DBUri can give different results in the same query, depending on the session context (which user is connected and what privileges the user has).

To complicate things a bit, there is also an XML element PUBLIC, under which database data is accessible without any database-schema qualification. This is a convenience feature, but it can also lead to some confusion if you forget that the XML views of the database for a given user depend on the specific access the user has to the database at a given time.

XML element PUBLIC corresponds to the use of a public synonym. For example, when queried by user quine, the following query tries to match table foo under database schema quine, but if no such table exists, it tries to match a public synonym named foo.

SELECT * FROM foo;

In the same way, XML element PUBLIC contains all of the database data visible to a given user, as well as all of the data visible to that user through public synonyms. So, the same DBUri URI /PUBLIC/FOO can resolve to quine.foo when user quine is connected, and resolve to curry.foo when user curry is connected.

DBUri Examples

A DBUri can identify a table, a row, a column in a row, or an attribute of an object column. The following sections describe how to target different object types.

Targeting a Table

You can target a complete database table, using this syntax:

/database_schema/table

Example 19-6 Using a DBUri to Target a Complete Table

In this example, a DBUri targets a complete table. An XML document is returned that corresponds to the table contents. The top-level XML element is named for the table. The values of each row are enclosed in a ROW element.

CREATE TABLE uri_tab (url URIType);
Table created.
 
INSERT INTO uri_tab VALUES
       (DBURIType.createURI('/HR/EMPLOYEES'));
1 row created.
 
SELECT e.url.getClob() FROM uri_tab e;

E.URL.GETCLOB()
---------------
<?xml version="1.0"?>
<EMPLOYEES>
 <ROW>
  <EMPLOYEE_ID>100</EMPLOYEE_ID>
  <FIRST_NAME>Steven</FIRST_NAME>
  <LAST_NAME>King</LAST_NAME>
  <EMAIL>SKING</EMAIL>
  <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
  <HIRE_DATE>17-JUN-87</HIRE_DATE>
  <JOB_ID>AD_PRES</JOB_ID>
  <SALARY>24000</SALARY>
  <DEPARTMENT_ID>90</DEPARTMENT_ID>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>101</EMPLOYEE_ID>
  <FIRST_NAME>Neena</FIRST_NAME>
  <LAST_NAME>Kochhar</LAST_NAME>
  <EMAIL>NKOCHHAR</EMAIL>
  <PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
  <HIRE_DATE>21-SEP-89</HIRE_DATE>
  <JOB_ID>AD_VP</JOB_ID>
  <SALARY>17000</SALARY>
  <MANAGER_ID>100</MANAGER_ID>
  <DEPARTMENT_ID>90</DEPARTMENT_ID>
 </ROW>
 . . .
 
1 row selected.

Targeting a Row in a Table

You can target one or more specific rows of a table, using this syntax:

/database_schema/table/ROW[predicate_expression]

Example 19-7 Using a DBUri to Target a Particular Row in a Table

In this example, a DBUri targets a single table row. The XPath predicate expression identifies the single table row that corresponds to employee number 200. The result is an XML document with ROW as the top-level element.

CREATE TABLE uri_tab (url URIType);
Table created.
 
INSERT INTO uri_tab VALUES
       (DBURIType.createURI('/HR/EMPLOYEES/ROW[EMPLOYEE_ID=200]'));
1 row created.
 
SELECT e.url.getClob() FROM uri_tab e;
 
E.URL.GETCLOB()
-------------------------------------------------------
<?xml version="1.0"?>
 <ROW>
  <EMPLOYEE_ID>200</EMPLOYEE_ID>
  <FIRST_NAME>Jennifer</FIRST_NAME>
  <LAST_NAME>Whalen</LAST_NAME>
  <EMAIL>JWHALEN</EMAIL>
  <PHONE_NUMBER>515.123.4444</PHONE_NUMBER>
  <HIRE_DATE>17-SEP-87</HIRE_DATE>
  <JOB_ID>AD_ASST</JOB_ID>
  <SALARY>4400</SALARY>
  <MANAGER_ID>101</MANAGER_ID>
  <DEPARTMENT_ID>10</DEPARTMENT_ID>
 </ROW>
 
1 row selected.

Targeting a Column

You can target a specific column, using this syntax:

/database_schema/table/ROW[predicate_expression]/column

You can target a specific attribute of an object column, using this syntax:

/database_schema/table/ROW[predicate_expression]/object_column/attribute

You can target a specific object column whose attributes have specific values, using this syntax:

/database_schema/table/ROW[predicate_expression_with_attributes]/object_column

Example 19-8 Using a DBUri to Target a Specific Column

In this example, a DBUri targets column last_name for the same employee as in Example 19-7. The top-level XML element is named for the targeted column.

CREATE TABLE uri_tab (url URIType);
Table created.
 
INSERT INTO uri_tab VALUES
       (DBURIType.createURI('/HR/EMPLOYEES/ROW[EMPLOYEE_ID=200]/LAST_NAME'));
1 row created.
 
SELECT e.url.getClob() FROM uri_tab e;
 
E.URL.GETCLOB()
------------------------------
<?xml version="1.0"?>
 <LAST_NAME>Whalen</LAST_NAME>
 
1 row selected.

Example 19-9 Using a DBUri to Target an Object Column with Specific Attribute Values

In this example, a DBUri targets a CUST_ADDRESS object column containing city and postal code attributes with certain values. The top-level XML element is named for the column, and it contains child elements for each of the object attributes.

CREATE TABLE uri_tab (url URIType);
Table created.
 
INSERT INTO uri_tab VALUES
  (DBURIType.createURI(
     '/OE/CUSTOMERS/ROW[CUST_ADDRESS/CITY="Poughkeepsie" and
                        CUST_ADDRESS/POSTAL_CODE=12601]/CUST_ADDRESS'));
1 row created.
 
SELECT e.url.getClob() FROM uri_tab e;
 
E.URL.GETCLOB()
---------------
<?xml version="1.0"?>
 <CUST_ADDRESS>
  <STREET_ADDRESS>33 Fulton St</STREET_ADDRESS>
  <POSTAL_CODE>12601</POSTAL_CODE>
  <CITY>Poughkeepsie</CITY>
  <STATE_PROVINCE>NY</STATE_PROVINCE>
  <COUNTRY_ID>US</COUNTRY_ID>
 </CUST_ADDRESS>
 
1 row selected.

The DBUri identifies the object that has a CITY attribute with Poughkeepsie as value and a POSTAL_CODE attribute with 12601 as value.

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() XPath node test for this. It specifies that you want only the text value of the node. Use the following syntax:

/oradb/database_schema/table/ROW[predicate_expression]/column/text()

Example 19-10 Using a DBUri to Retrieve Only the Text Value of a Node

This example retrieves the text value of the employee last_name column for employee number 200, , without the XML tags.

CREATE TABLE uri_tab (url URIType);
Table created.

INSERT INTO uri_tab VALUES
       (DBURIType.createURI(
          '/HR/EMPLOYEES/ROW[EMPLOYEE_ID=200]/LAST_NAME/text()'));

1 row created.

SELECT e.url.getClob() FROM uri_tab e;

E.URL.GETCLOB()
---------------
Whalen
 
1 row selected.

Targeting a Collection

You can target a database collection, such as a varray or nested table. You must, however, target the entire collection – you cannot target individual members of a collection. When a collection is targeted, the XML document produced by the DBUri contains each collection member as an XML element, with all such elements enclosed in a element named for the type of the collection.

Example 19-11 Using a DBUri to Target a Collection

In this example, a DBUri targets a collection of numbers. The top-level XML element is named for the collection, and its children are named for the collection type (NUMBER).

CREATE TYPE num_collection AS VARRAY(10) OF NUMBER;
/
Type created.
 
CREATE TABLE orders (item VARCHAR2(10), quantities num_collection);
Table created.
 
INSERT INTO orders VALUES ('boxes', num_collection(3, 7, 4, 9));
1 row created.
 
SELECT * FROM orders;
 
ITEM
----
QUANTITIES
----------
boxes
NUM_COLLECTION(3, 7, 4, 9)
 
1 row selected.
 
SELECT DBURIType('/HR/ORDERS/ROW[ITEM="boxes"]/QUANTITIES').getClob() FROM DUAL;
 
DBURITYPE('/HR/ORDERS/ROW[ITEM="BOXES"]/QUANTITIES').GETCLOB()
--------------------------------------------------------------
<?xml version="1.0"?>
 <QUANTITIES>
  <NUMBER>3</NUMBER>
  <NUMBER>7</NUMBER>
  <NUMBER>4</NUMBER>
  <NUMBER>9</NUMBER>
 </QUANTITIES>
 
1 row selected.

Creating New Subtypes of URIType using Package URIFACTORY

You can use PL/SQL package URIFACTORY to do more than create URIType instances. Additional methods are listed in Table 19-2.

Table 19-2 URIFACTORY Methods

Method Description
getURI()

Returns the URL of the URIType instance.

escapeURI()

Escapes the URI string by replacing characters that are not permitted in URIs by their equivalent escape sequence.

unescapeURI()

Unescapes a given URI.

registerURLHandler()

Registers a particular type name for handling a particular URL. This is called by getURI() to generate an instance of the type.

A Boolean argument can be used to indicate that the prefix must be stripped off before calling the appropriate type constructor.

unregisterURLHandler()

Unregisters a URL handler.


Of particular note is that you can use package URIFACTORY to define new subtypes of type URIType. You can then use those subtypes to provide specialized processing of URIs. In particular, you can define URIType subtypes that correspond to particular protocols – URIFACTORY will then recognize and process instances of those subtypes accordingly.

Defining new types and creating database columns specific to the new types has these advantages:

  • It provides an implicit constraint on the columns to contain only instances of those types. This can be useful for implementing specialized indexes on a column for specific protocols. For a DBUri, for instance, you can implement specialized indexes that fetch data directly from disk blocks, rather than executing SQL queries.

  • You can have different constraints on different columns, based on the type. For a HTTPUri, for instance, you can define proxy and firewall constraints on a column, so that any access through the HTTP uses the proxy server.

Registering New URIType Subtypes with Package URIFACTORY

To provide specialized processing of URIs, you define and register a new URIType subtype, as follows:

  1. Create the new type using SQL statement CREATE TYPE. The type must implement method createURI().

  2. Optionally override the default methods, to perform specialized processing when retrieving data or to transform the XML data before displaying it.

  3. Choose a new URI prefix, to identify URIs that use this specialized processing.

  4. Register the new prefix using method registerURLHandler(), so that package URIFACTORY can create an instance of your new subtype when it receives a URI starting with the new prefix you defined.

After the new subtype is defined, a URI with the new prefix will be recognized by URIFACTORY methods, and you can create and use instances of the new type.

For example, suppose that you define a new protocol prefix, ecom://, and define a subtype of URIType to handle it. Perhaps the new subtype implements some special logic for method getCLOB(), or perhaps it makes some changes to XML tags or data in method getXML(). After you register prefix ecom:// with URIFACTORY, a call to getURI() will generate an instance of the new URIType subtype for a URI with that prefix.

Example 19-12 URIFACTORY: Registering the ECOM Protocol

This example creates a new type, ECOMURIType, to handle a new protocol, ecom://. The example stores three different kinds of URIs in a single table: an HTTPUri, a DBUri, and an instance of the new type, ECOMURIType. To actually run this example, you would need to define each of the ECOMURIType member functions.

CREATE TABLE url_tab (urlcol varchar2(80));
Table created.
 
-- Insert an HTTP URL reference
INSERT INTO url_tab VALUES ('http://www.oracle.com/');
1 row created.
 
-- Insert a DBUri
INSERT INTO url_tab VALUES ('/oradb/HR/EMPLOYEES/ROW[FIRST_NAME="Jack"]');
1 row created.
 
-- Create a new type to handle a new protocol called ecom://
-- This is just an example template. For this to run, the implementations
-- of these functions needs to be specified.
CREATE OR REPLACE TYPE ECOMURIType UNDER SYS.URIType (
  OVERRIDING MEMBER FUNCTION getClob RETURN CLOB,
  OVERRIDING MEMBER FUNCTION getBlob RETURN BLOB,
  OVERRIDING MEMBER FUNCTION getExternalURL RETURN VARCHAR2,
  OVERRIDING MEMBER FUNCTION getURI RETURN VARCHAR2,
  -- Must have this for registering with the URL handler
  STATIC FUNCTION createURI(url IN VARCHAR2) RETURN ECOMURIType);
/
-- Register a new handler for the ecom:// prefixes
BEGIN
  -- The handler type name is ECOMURIType; schema is HR
  -- 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 method createURI(),
  -- so that the string 'ecom://' is not stored inside the
  -- ECOMURIType object. It is added back automatically when
  -- you call ECOMURIType.getURI().
  URIFACTORY.registerURLHandler (prefix => 'ecom://',
                                 schemaname => 'HR',
                                 typename => 'ECOMURITYPE',
                                 ignoreprefixcase => TRUE,
                                 stripprefix => TRUE);
END;
/
PL/SQL procedure successfully completed.
 
-- Insert this new type of URI into the table
INSERT INTO url_tab VALUES ('ECOM://company1/company2=22/comp');
1 row created.
 
-- Use the factory to generate an instance of the appropriate
-- subtype for each URI in the table.

-- You would need to define the member functions for this to work:
SELECT urifactory.getURI(urlcol) FROM url_tab;

-- This would generate:
HTTPURIType('www.oracle.com'); -- an HTTPUri
DBURIType('/oradb/HR/EMPLOYEES/ROW[FIRST_NAME="Jack"]', null); -- a DBUri
ECOMURIType('company1/company2=22/comp'); -- an ECOMURIType instance

SYS_DBURIGEN SQL Function

You can create a DBUri by providing an XPath expression to constructor DBURIType or to appropriate URIFACTORY methods. With SQL function sys_DburiGen, you can alternatively create a DBUri with an XPath that is composed from database columns and their values.

SQL function sys_DburiGen takes as its argument one or more database columns or attributes, and optionally a rowid, and generates a DBUri that targets a particular column or row object. Function sys_DburiGen takes an additional parameter that indicates whether the text value of the node is needed. See Figure 19-2.

Figure 19-2 SYS_DBURIGEN Syntax

Description of Figure 19-2 follows
Description of "Figure 19-2 SYS_DBURIGEN Syntax"

All columns or attributes referenced must reside in the same table. They must each reference a unique value. If you specify multiple columns, then the initial columns identify the row, and the last column identifies the column within that row. If you do not specify a database schema, then the table name is interpreted as a public synonym.

Example 19-13 SYS_DBURIGEN: Generating a DBUri that Targets a Column

This example uses SQL function sys_DburiGen to generate a DBUri that targets column email of table hr.employees where employee_id is 206:

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

SYS_DBURIGEN(EMPLOYEE_ID,EMAIL)(URL, SPARE)
-------------------------------------------------------------------
DBURITYPE('/PUBLIC/EMPLOYEES/ROW[EMPLOYEE_ID = "206"]/EMAIL', NULL)

1 row selected.

Rules for Passing Columns or Object Attributes to SYS_DBURIGEN

A column or attribute passed to SQL function sys_DburiGen must obey the following rules:

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

  • Unique mapping: The column or object attribute must be uniquely mappable back to the table or view from which it came. The only virtual columns allowed are those produced with VALUE or REF. The column can come from a subquery with SQL function table or from 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.

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

  • Optional text() argument: By default, DBURIType constructs an XML document. Use text() as the third argument to sys_DburiGen to create a DBUri that targets a text node (no XML elements). For example:

    SELECT sys_DburiGen(employee_id, last_name, 'text()') FROM hr.employees,
      WHERE employee_id=200;
    
    

    This will construct a DBUri with the following URI:

    /HR/EMPLOYEES/ROW[EMPLOYEE_ID=200]/LAST_NAME/text()
    
    
  • Single-column argument: If there is a single-column argument, then the column is used as both the key column to identify the row and the referenced column.

Example 19-14 Passing Columns With Single Arguments to SYS_DBURIGEN

This query uses employee_id as both the key column and the referenced column. It generates a DBUri that targets the row with employee_id 7369.

SELECT sys_DburiGen(employee_id) FROM employees
  WHERE employee_id=200;
 
SYS_DBURIGEN(EMPLOYEE_ID)(URL, SPARE)
-------------------------------------
DBURITYPE('/PUBLIC/EMPLOYEES/ROW[EMPLOYEE_ID=''200'']/EMPLOYEE_ID', NULL)
 
1 row selected.

SYS_DBURIGEN SQL Function: Examples

Example 19-15 Inserting Database References Using SYS_DBURIGEN

CREATE TABLE doc_list_tab(docno NUMBER PRIMARY KEY, doc_ref SYS.DBURIType);
Table created.

-- Insert a DBUri that targets the row with employee_id=177
INSERT INTO doc_list_tab VALUES(1001, (SELECT sys_DburiGen(rowid, employee_id)
                                         FROM employees WHERE employee_id=177));
1 row created.
 
-- Insert a DBUri that targets the last_name column of table employees
INSERT INTO doc_list_tab VALUES(1002,
                                (SELECT sys_DburiGen(employee_id, last_name)
                                   FROM employees WHERE employee_id=177));
1 row created.
 
SELECT * FROM doc_list_tab;
 
     DOCNO
----------
DOC_REF(URL, SPARE)
-----------------------------------------------------
      1001
DBURITYPE('/PUBLIC/EMPLOYEES/ROW[ROWID=''AAAL3LAAFAAAABSABN'']/EMPLOYEE_ID', NULL)
 
      1002
DBURITYPE('/PUBLIC/EMPLOYEES/ROW[EMPLOYEE_ID=''177'']/LAST_NAME', NULL)
 
2 rows selected.

Returning Partial Results

When selecting from a large column, you might sometimes 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 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 search-result page. Instead, you might show just the first 20 characters of each story, to represent the gist, and then return a URL to the full story. This can be done as follows:

Example 19-16 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);
Table created.

INSERT INTO travel_story
  VALUES ('Egypt', 'This is the story of my time in Egypt....');
1 row created.

We create a function that returns only the first 20 characters from the story:

CREATE OR REPLACE FUNCTION charfunc(clobval IN CLOB) RETURN VARCHAR2 IS
  res VARCHAR2(20);
  amount NUMBER := 20;
BEGIN
  DBMS_LOB.read(clobval, amount, 1, res);
  RETURN res;
END;
/
Function created.

We next create a view that selects only the first twenty characters from the story, and returns a DBUri to the story column.

CREATE OR REPLACE VIEW travel_view AS
  SELECT story_name, charfunc(story) short_story,
         sys_DburiGen(story_name, story, 'text()') story_link
  FROM travel_story;
View created.
 
SELECT * FROM travel_view;
 
STORY_NAME
----------
SHORT_STORY
-----------
STORY_LINK(URL, SPARE)
----------------------
Egypt
This is the story of
DBURITYPE('/PUBLIC/TRAVEL_STORY/ROW[STORY_NAME=''Egypt'']/STORY/text()', NULL)
 
1 row selected.

RETURNING URLs to Inserted Objects

You can use SQL function sys_DburiGen in the RETURNING clause of DML statements to retrieve the URL of an object as it is inserted.

Example 19-17 Using SYS_DBURIGEN in the RETURNING Clause to Retrieve a URL

In this example, whenever a document is inserted into table clob_tab, its URL is inserted into table uri_tab. This is done using SQL function sys_DburiGen in the RETURNING clause of the INSERT statement.

CREATE TABLE clob_tab (docid NUMBER, doc CLOB);
Table created.
CREATE TABLE uri_tab (docs SYS.DBURIType);
Table created.

In PL/SQL, we specify the storage of the URL of the inserted document as part of the insertion operation, using the RETURNING clause and EXECUTE IMMEDIATE :

DECLARE
  ret SYS.DBURIType;
BEGIN
  -- execute the insert operation and get the URL
  EXECUTE IMMEDIATE 
    'INSERT INTO clob_tab VALUES (1, ''TEMP CLOB TEST'') 
       RETURNING sys_DburiGen(docid, doc, ''text()'') INTO :1'
    RETURNING INTO ret;
  -- Insert the URL into uri_tab
  INSERT INTO uri_tab VALUES (ret);
END;
/

SELECT e.docs.getURL() FROM hr.uri_tab e;
E.DOCS.GETURL()
------------------------------------------------
/ORADB/PUBLIC/CLOB_TAB/ROW[DOCID='1']/DOC/text()
 
1 row selected.

DBUriServlet

Oracle XML DB Repository resources can be retrieved using the HTTP server that is incorporated in Oracle XML DB. Oracle Database also includes a servlet, DBUriServlet, that makes any kind of database data available through HTTP(S) URLs. The data can be returned as plain text, HTML, or XML.

A Web client or application can access such data without using SQL or a specialized database API. You can retrieve the data by linking to it on a Web page or by requesting it through HTTP-aware APIs of Java, PL/SQL, and Perl. You can display or process the data using an application such as a Web browser or an XML-aware spreadsheet. DBUriServlet can generate content that is XML data or not, and it can transform the result using XSLT style sheets.

You make database data Web-accessible by using a URI that is composed of a servlet address (URL) plus a DBUri URI that specifies which database data to retrieve. This is the syntax, where http://server:port is the URL of the servlet (machine and port), and /oradb/database_schema/table is the DBUri URI (any DBUri URI can be used):

http://server:port/oradb/database_schema/table

When using XPath notation in a URL for the servlet, you might need to escape certain characters. You can use URIType method getExternalURL() to do this.

You can either use DBUriServlet, which is preinstalled as part of Oracle XML DB, or write your own servlet that runs on a servlet engine. The servlet reads the URI portion of the invoking URL, creates a DBUri using that URI, calls URIType methods to retrieve the data, and returns the values in a form such as a Web page, an XML document, or a plain-text document.

The MIME type to use is specified to the servlet through the URI:

  • By default, the servlet produces MIME types text/xml and text/plain. If the DBUri path ends in text(), then text/plain is used; otherwise, an XML document is generated with MIME type text/xml.

  • You can override the default MIME type, setting it to binary/x-jpeg or some other value, by using the contenttype argument to the servlet.

See Also:

Chapter 27, "Writing Oracle XML DB Applications in Java", for information on Oracle XML DB servlets

Example 19-18 Using a URL to Override the MIME Type

To retrieve the employee_id column of the employee table, you can use a URL such as one of the following, where computer server.oracle.com is running Oracle Database with a Web service listening to requests on port 8080. Step oradb is the virtual path that maps to the servlet.

-- Produces a content type of text/plain
http://server.oracle.com:8080/oradb/QUINE/A/ROW[B=200]/C/text()

-- Produces a content type of text/xml
http://server.oracle.com:8080/oradb/QUINE/A/ROW[B=200]/C

To override the content type, you can use a URL that passes text/html to the servlet as the contenttype parameter:

-- Produces a content type of text/html
http://server.oracle.com:8080/oradb/QUINE/A/ROW[B=200]/C?contenttype=text/html

Table 19-3 describes each of the optional URL parameters you can pass to DBUriServlet to customize its output.

Table 19-3 DBUriServlet: Optional Arguments

Argument Description
rowsettag

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

http://server:8080/oradb/HR/EMPLOYEES?rowsettag=OracleEmployees

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

contenttype

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

http://server:8080/oradb/HR/EMPLOYEES?contenttype=text/plain
transform

Passes a URL to URIFACTORY, which retrieves the XSL style sheet at that location. This style sheet is then applied to the XML document being returned by the servlet. For example:

http://server:8080/oradb/HR/EMPLOYEES?transform=/oradb/QUINE/XSLS/DOC/text()&contenttype=text/html

Customizing DBUriServlet

DBUriServlet is built into the database – to customize the servlet, you must edit the Oracle XML DB configuration file, xdbconfig.xml. You can edit it as the Oracle XML DB user (XDB), using WebDAV, FTP, Oracle Enterprise Manager, or PL/SQL. To update the file using FTP or WebDAV, download the document, edit it, and save it back into the database.

DBUriServlet is installed at /oradb/*, which is the address specified in the servlet-pattern tag of xdbconfig.xml. The asterisk (*) is necessary to indicate that any path following oradb is to be mapped to the same servlet. oradb is published as the virtual path. You can change the path that will be used to access the servlet.

Example 19-19 Changing the Installation Location of DBUriServlet

In this example, the configuration file is modified to install DBUriServlet under /dburi/*.

DECLARE
  doc XMLType;
  doc2 XMLType;
BEGIN
  doc := DBMS_XDB.cfg_get();
  SELECT 
    updateXML(doc,
'/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/
servlet-mappings/servlet-mapping[servlet-name="DBUriServlet"]/servlet-pattern/
text()', 
              '/dburi/*')
    INTO doc2 FROM DUAL;
  DBMS_XDB.cfg_update(doc2);
  COMMIT;
END;
/

Security parameters, the servlet display-name, and the description can also be customized in configuration file xdbconfig.xml . The servlet can be removed by deleting its servlet-pattern. This can also be done using SQL function updateXML to update the servlet-mapping element to NULL.

DBUriServlet 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 checks to ensure that the user logging has one of the roles specified in the configuration file using parameter security-role-ref). By default, the servlet is available to role authenticatedUser, and any user who logs into the servlet with a valid database password has this role.

The role parameter can be changed to restrict access to any specific database roles. To change from the default authenticated-user role to a role that you have created, you modify the Oracle XML DB configuration file.

Example 19-20 Restricting Servlet Access to a Database Role

This example changes the default authenticated-user role to role servlet-users (which it is assumed you have created).

DECLARE
  doc  XMLType;
  doc2 XMLType;
  doc3 XMLType;
BEGIN
  doc := DBMS_XDB.cfg_get();
  SELECT updateXML(doc,
'/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/
servlet-list/servlet[servlet-name="DBUriServlet"]/security-role-ref/role-name/
text()',
                   'servlet-users')
    INTO doc2 FROM DUAL;
  SELECT updateXML(doc2,
'/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/
servlet-list/servlet[servlet-name="DBUriServlet"]/security-role-ref/role-link/
text()',
                   'servlet-users')
    INTO doc3 FROM DUAL;
  DBMS_XDB.cfg_update(doc3);
  COMMIT;
END;
/

Configuring Package URIFACTORY to Handle DBUris

A URL such as http://server/servlets/oradb is handled by DBUriServlet (or by a custom servlet). When a URL such as this is stored as a URIType instance, it is generally desirable to use subtype DBURIType, since this URI targets database data.

However, if a URIType instance is created using methods of package URIFACTORY such as getURI(), then by default the subtype used is HTTPURIType, not DBURIType. This is because URIFACTORY looks only at the URI prefix, sees http://, and assumes that the URI targets a Web page. This results in unnecessary layers of communication and perhaps extra character conversions.

To make things more efficient, you can teach URIFACTORY that URIs of the given form represent database accesses and so should be realized as DBUris, not HTTPUris. You do this by registering a handler for this URI as a prefix, specifying DBURIType as the type of instance to generate.

Example 19-21 Registering a Handler for a DBUri Prefix

This example effectively tells URIFACTORY that any URI string starting with http://server/servlets/oradb corresponds to a database access.

BEGIN
  URIFACTORY.registerURLHandler('http://server/servlets/oradb',
                             'SYS', 'DBURIType', true, true);
END;
/

After you execute this code, all getURI() calls in the same session automatically create DBUris for any URI strings with prefix http://server/servlets/oradb.

See Also:

Oracle Database PL/SQL Packages and Types Reference for information on URIFACTORY functions