33 Data Access Using URIs

DBUris and XDBUris – What For?

The following are typical uses of DBUris and XDBUris:

• You can reference XSLT stylesheets from within database-generated Web pages. PL/SQL package DBMS_METADATA uses DBUris to reference XSLT stylesheets. An XDBUri can be used to reference XSLT stylesheets 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 an 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.

URIType 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. Table 33-1 lists the URIType PL/SQL methods. In addition, each of the subtypes has a constructor with the same name as the subtype.

getURL()

getExternalURL()

getContentType()

getCLOB()

getBLOB()

getXML()

createURI()

DECLARE
  httpuri HTTPURIType;
  y CLOB;
  x BLOB;
BEGIN
  httpuri := HTTPURIType('http://www.oracle.com/index.html');
  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

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

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

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

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

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:

• /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 PL/SQL method getURI() of package URIFACTORY.

XDBURIType is the default URIType used when generating instances using URIFACTORY PL/SQL 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 returns 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 object type is XDBURIType, not any particular resource file extension or the presence of # followed by an XPath expression. Even if the resource were named foo.bar instead of doc1.xml, the returned URIType instance would still be an XDBUri.

XDBUri Examples

Example 33-4 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_REPOS.createFolder('/public/orders/');
res:=DBMS_XDB_REPOS.createResource('/public/orders/po1.xml', postring);
END;
/
PL/SQL procedure successfully completed.
 
CREATE TABLE uri_tab (poUrl SYS.URIType, poName VARCHAR2(1000));
Table created.
 
-- 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.

Because PL/SQL method getXML() returns an XMLType instance, you can use it with SQL/XML functions such as XMLQuery. The query in Example 33-5 illustrates this. The query retrieves all purchase orders numbered 999.

SELECT e.poUrl.getCLOB() FROM uri_tab e
  WHERE XMLCast(XMLQuery('$po/ROW/PO'
                         PASSING e.poUrl.getXML() AS "po"
                         RETURNING CONTENT)
                AS VARCHAR2(24))
        = '999';

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

Viewing the Database as XML Data

You can access only 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 33-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 33-1 A DBUri Corresponds to an XML Visualization of Relational Data

Description of "Figure 33-1 A DBUri Corresponds to an XML Visualization 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 PL/SQL 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 supports only a subset of the full XPath or XPointer syntax. 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. Instead, the entire URI is treated as a (simple) XPath expression.

You can create DBUris to any database data to which you have access. XPath expressions 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 column email 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)

  • // (descendant 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 XPath expressions generally. Thus, for example, to specify table 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.

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 for accessing 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 and 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.

/database_schema/table

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-03</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-05</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.

/database_schema/table/ROW[predicate_expression]

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

/database_schema/table/ROW[predicate_expression]/column

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

/database_schema/table/ROW[predicate_expression_with_attributes]/object_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.

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.

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

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.

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.

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 PL/SQL 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 PL/SQL 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 is 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 PL/SQL 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() generates an instance of the new URIType subtype for a URI with that prefix.

Example 33-12 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 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 must 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 PL/SQL 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

Rules for Passing Columns or Object Attributes to SYS_DBURIGEN

A column or attribute passed to Oracle 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 a SQL TABLE collection expression, that is, TABLE(...), or from an inline view (as long as the inline view does not rename the columns).

  See Oracle Database SQL Language Reference for information about the SQL TABLE collection expression.

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

The query in Example 33-14 uses employee_id as both the key column and the referenced column. It generates a DBUri that targets the row with employee_id 200.

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

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=''AAAQCcAAFAAAABSABN'']/EMPLOYEE_ID', NULL)
 
      1002
DBURITYPE('/PUBLIC/EMPLOYEES/ROW[EMPLOYEE_ID=''177'']/LAST_NAME', NULL)
 
2 rows selected.

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.

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.

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.

CREATE TABLE clob_tab (docid NUMBER, doc CLOB);
Table created.

CREATE TABLE uri_tab (docs SYS.DBURIType);
Table created.

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.

Overriding the MIME Type Using a URL

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.

• http://server.oracle.com:8080/oradb/QUINE/A/ROW[B=200]/C/text()
  

  Produces a content type of text/plain

• http://server.oracle.com:8080/oradb/QUINE/A/ROW[B=200]/C
  

  Produces a content type of text/xml

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

• http://server.oracle.com:8080/oradb/QUINE/A/ROW[B=200]/C?contenttype=text/html
  

  Produces a content type of 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 with database schema (user account) 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 is used to access the servlet.

In Example 33-20, the configuration file is modified to install DBUriServlet under /dburi/*. (The long XPath expression has been split here for documentation purposes. It actually needs to be on a single line.)

DECLARE
  doc XMLType;
  doc2 XMLType;
BEGIN
  doc := DBMS_XDB_CONFIG.cfg_get();
  SELECT XMLQuery('declare default element namespace 
                   "http://xmlns.oracle.com/xdb/xdbconfig.xsd";
                   copy $i := $doc modify
                   for $j in
$i/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-mappingsFoot 1 
/servlet-mapping[servlet-name="DBUriServlet"]/servlet-pattern
                   return replace value of node $j with $i/dburi/*
                   return $i'
                  PASSING DBMS_XDB_CONFIG.cfg_get() AS "doc"
                  RETURNING CONTENT) INTO doc2 FROM DUAL;
  DBMS_XDB_CONFIG.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 XQuery Update 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 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). 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 authenticatedUser role to a role that you have created, you modify the Oracle XML DB configuration file.

Example 33-21 changes the default role authenticatedUser to role servlet-users (which you must have created).

DECLARE
  doc  XMLType;
  doc2 XMLType;
  doc3 XMLType;
BEGIN
  doc := DBMS_XDB_CONFIG.cfg_get();
  SELECT XMLQuery('copy $i := $p1 modify
                     (for $j in $i/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/Foot 2 
servlet-list/servlet[servlet-name="DBUriServlet"]/security-role-ref/role-name
                      return replace value of node $j with $p2)
                  return $i'
                 PASSING DOC AS "p1", 'servlet-users' AS "p2" RETURNING CONTENT)
    INTO doc2 FROM DUAL;
  SELECT XMLQuery('copy $i := $p1 modify
                      (for $j in $i/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/
servlet-list/servlet[servlet-name="DBUriServlet"]/security-role-ref/role-link
                    return replace value of node $j with $p2)
                   return $i'
                  PASSING DOC2 AS "p1", 'servlet-users' AS "p2" RETURNING CONTENT)
    INTO doc3 FROM DUAL;
  DBMS_XDB_CONFIG.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 PL/SQL methods of package URIFACTORY, 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 33-22 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.

Table or View Access from a Browser Using DBUri Servlet

Oracle XML DB includes the DBUri servlet, which makes it possible to access the content of any table or view directly from a browser. DBUri servlet uses the facilities of the DBURIType to generate a simple XML document from the contents of the table. The servlet is C language-based and installed in the Oracle XML DB HTTP server. By default, the servlet is installed under the virtual directory /oradb.

The URL passed to the DBUri Servlet is an extension of the URL passed to the DBURIType. The URL is extended with the address and port number of the Oracle XML DB HTTP server and the virtual root that directs HTTP(S) requests to the DBUri servlet. The default configuration for this is /oradb.

The URL http://localhost:8080/oradb/HR/DEPARTMENTS would thus return an XML document containing the contents of the DEPARTMENTS table in the HR database schema. This assumes that the Oracle XML DB HTTP server is running on port 8080, the virtual root for the DBUri servlet is /oradb, and that the user making the request has access to the HR database schema.

DBUri servlet accepts parameters that allow you to specify the name of the ROW tag and MIME-type of the document that is returned to the client.

Content in XMLType table or view can also be accessed through the DBUri servlet. When the URL passed to the DBUri servlet references an XMLType table or XMLType view the URL can be extended with an XPath expression that can determine which documents in the table or row are returned. The XPath expression appended to the URL can reference any node in the document.

XML generated by DBUri servlet can be transformed using the XSLT processor built into Oracle XML DB. This lets XML that is generated by DBUri servlet be presented in a more legible format such as HTML.

XSLT stylesheet processing is initiated by specifying a transform parameter as part of the URL passed to DBUri servlet. The stylesheet is specified using a URI that references the location of the stylesheet within database. The URI can either be a DBURIType value that identifies a XMLType column in a table or view, or a path to a document stored in Oracle XML DB Repository. The stylesheet is applied directly to the generated XML before it is returned to the client. When using DBUri servlet for XSLT processing, it is good practice to use the contenttype parameter to explicitly specify the MIME type of the generated output.

If the XML document being transformed is stored as an XML schema-based XMLType instance, then Oracle XML DB can reduce the overhead associated with XSL transformation by leveraging the capabilities of the lazily loaded virtual DOM.

The root of the URL is /oradb, so the URL is passed to the DBUri servlet that accesses the purchaseorder table in the SCOTT database schema, rather than as a resource in Oracle XML DB Repository. The URL includes an XPath expression that restricts the result set to those documents where node /PurchaseOrder/Reference/text() contains the value specified in the predicate. The contenttype parameter sets the MIME type of the generated document to text/xml.