You can use a URI as an indirection mechanism to access database data, and you can use a URI that targets database data to produce XML documents.

• 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.

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.example.com/document1#some_anchor, where some_anchor is a named anchor in the HTML document.

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

  • http://www.example.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.

You can represent paths of various kinds as database objects. These provide unified access to data stored inside and outside the server, and they can be used to map URIs in XML documents to database columns, letting documents reference data stored in relational columns and expose it externally.

The available path object types are HTTPURIType, DBURIType, and XDBURIType, all of which are derived from abstract object type URIType.

• 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. Applications using HTTPUriType must have the proper access privileges. HTTPUriType implements the Hyper Text Transfer Protocol (HTTP(S)) for accessing remote Web pages. HTTPURIType uses package UTL_HTTP to fetch data, so session settings and access control for this package can also be used to influence HTTP fetches.

  See Also:

  Oracle Database Security Guide for information about managing fine-grained access to external network services

• 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 table HR.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 PL/SQL constructor XDBURIType you can create objects that represent links to repository resources. You can then retrieve all or part of any resource by calling methods on those objects. 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 broken up and stored in object-relational 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 data store is needed.

• Overview of DBUris and XDBUris
  Important uses of DBUris and XDBUris include referencing XSLT stylesheets from Web pages, referencing data in database tables or in repository folders without using SQL, and improving performance by bypassing the Web server.
• URIType PL/SQL Methods
  Abstract object type URIType includes PL/SQL methods that can be used with each of its subtypes. Each of these methods can be overridden by any of the subtypes.

To use instances of URIType subtypes for indirection, you store such instances in the database and then query to retrieve the targeted data with a PL/SQL method such as getCLOB().

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.0 Transitional//EN">
<html>
<head>
. . .
 
<?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-06</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.

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 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.0 Transitional//EN">
<html>
<head>
. . .
AbsURL
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
. . .
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-03</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-03</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.

XDBURIType is a subtype of URIType that exposes resources in Oracle XML DB Repository using URIs. Instances of object 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). An optional fragment portion of the URI, after the number-sign (#), uses XPath syntax to target parts of an XML document.
• Using XDBUri: Examples
  XDBUri examples here use URIs in a table to access a repository resource and, together with PL/SQL method getXML, to query and retrieve XML documents.

A DBUri is a URI that targets database data. As for all instances of URIType subtypes, a DBUri provides indirect access to data. DBURIType also lets you address database data using XPath and construct XML documents containing database data that is targeted by a DBUri that reflects the database structure.

• 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 table HR.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 .

• View the Database as XML Data
  Using DBURIType, you can have what amounts to XML views of the portions of the database to which you have access, presented in the form of XML data. When visualized this way, the database data is effectively wrapped in XML elements, resulting in one or more XML documents.
• DBUri URI Syntax
  An XPath expression is a path into XML data that addresses one or more nodes. A DBUri exploits virtual XML visualization of the database to use a simple form of XPath expression as a URI to address database data. This is so, whether or not the data is XML.
• DBUris are Scoped to a Database and Session
  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).
• Using DBUris —Examples
  A DBUri can identify a table, a row, a column in a row, or an attribute of an object column. Examples here show how to target different object types.

You can define your own subtypes of URIType that correspond to particular protocols. You can use PL/SQL package URIFACTORY to obtain the URI of a URIType instance, escape characters in a URI string or remove such escaping, and register or unregister a type name for handling a given URL.

Additional PL/SQL methods are listed in Table 32-2.

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 then recognizes and processes 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.

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

Oracle 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 32-2.

Figure 32-2 SYS_DBURIGEN Syntax

Description of "Figure 32-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 32-13 uses Oracle 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 Oracle SQL function sys_DburiGen must obey certain rules.
• Using SQL Function SYS_DBURIGEN: Examples
  Examples are presented that use SQL function sys_DburiGen to insert database references, return partial results from a large column, and return URLs to inserted objects.

You can retrieve repository resources using the Oracle XML DB HTTP server. 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 stylesheets.

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 (server 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 PL/SQL method getExternalURL() to do this.

You can either use DBUriServlet, which is pre-installed 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 PL/SQL 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.

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

rowsettag

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

contenttype

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

transform

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

^Footnote 1

This URL is split across two lines for the purpose of documentation.

• Overriding the MIME Type Using a URL
  You can override MIME content type by using a URL that passes a different MIME type to the servlet as the contenttype parameter.
• Customizing DBUriServlet
  To customize DBUriServlet you modify the Oracle XML DB configuration file, xdbconfig.xml.
• Using Roles for DBUriServlet Security
  Servlet security is handled by Oracle Database using roles. When users log in to the servlet, they use their database user name 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).
• Configuring Package URIFACTORY to Handle DBUris
  To improve efficiency, you can teach URIFACTORY that a URI of a given form represents database access and so should be realized as a DBUri, not an HTTPUri. You do this by registering a handler for the URI as a prefix, specifying DBURIType as the type of instance to generate.
• Table or View Access from a Web Browser Using DBUri Servlet
  Oracle XML DB includes the DBUri servlet, which lets you access the content of any table or view directly from a web browser. It uses DBURIType to generate a simple XML document from the table contents. The servlet is C language-based and installed in the Oracle XML DB HTTP server.