Oracle9i XML Database Developer's Guide - Oracle XML DB Release 2 (9.2) Part Number A96620-02 |
|
|
View PDF |
This chapter describes how to generate and store URLs inside the database and to retrieve the data pointed to by the URLs. It also introduces the concept of DBUris which are URLs to relational data stored inside the database. It explains how to create and store references to data stored in Oracle XML DB Repository hierarchy.
This chapter contains these sections:
In developing Internet applications, and particularly Internet-based XML applications, you often need to refer to data somewhere on a network using URLs or URIs.
Oracle9i can represent various kinds of paths within the database. Each corresponds to a different object type, all derived from a general type called UriType
:
Any resources stored inside ORACLE XML DB Repository can also be retrieved by using the HTTP Server in Oracle XML DB. Oracle9i also includes a servlet that makes table data available through HTTP URLs. The data can be returned as plain text, HTML, or XML.
Any Web-enabled client or application can use the data without SQL programming or any specialized database API. You can retrieve the data by linking to it in a Web page or by requesting it through the HTTP-aware APIs of Java, PL/SQL, or Perl. You can display or process the data through any kind of application, including a regular Web browser or an XML-aware application such as a spreadsheet. The servlet supports generating XML and non-XML content and also transforming the results using XSLT stylesheets.
You can create database columns using UriType
or its child types, or you can store just the text of each URI or URL and create the object types as needed. When storing a mixture of subtypes in the database, you can define a UriType
column that can store various subtypes within the same column.
Because these capabilities use object-oriented programming features such as object types and methods, you can derive your own types that inherit from the Oracle-supplied ones. Deriving new types lets you use specialized techniques for retrieving the data or transforming or filtering it before returning it to the program.
See Also:
Chapter 26, "Oracle XML DB Basic Demo", section "8.0 XML DB Demo: Accessing Content Using DBUriServlet; Transforming Content Using XSL". |
When storing just the URI text in the database, you can use the UriFactory
package to turn each URI into an object of the appropriate subtype. UriFactory
package creates an instance of the appropriate type by checking what kind of URI is represented by a given string. For example, any URI that begins with http://
is considered an HTTP URL. When the UriFactory
package is passed such a URI string, it returns an instance of a HttpUriType
object.
Before you explore the features in this chapter, you should be familiar with the notation for various kinds of URIs.
See:
|
This section introduces you to URI concepts.
A URI, or Uniform Resource Identifier, is a generalized kind of URL. Like a URL, it can reference any document, and can reference a specific part of a document. It is more general than a URL because it has a powerful mechanism for specifying the relevant part of the document. A URI consists of two parts:
The fragment appears after the #
in the following examples.
Note: Only |
Figure 12-1 shows a view of the XML data stored in a relational table, EMP
, in the database, and the columns of data mapped to elements in the XML document. This mapping is referred to as an XML visualization. The resulting URL path can be derived from the XML document view.
Typical URIs look like the following:
http://www.url.com/document1#Anchor
where Anchor
is a named anchor inside the document.
http://www.xml.com/xml_doc#//po/cust/custname
where:
Oracle9i introduces new datatypes in the database to store and retrieve objects that represent URIs. See "UriTypes Store Uri-References" in the following section. Each datatype uses a different protocol, such as HTTP, to retrieve data.
Oracle9i also introduces new forms of URIs that represent references to rows and columns of database tables.
The following are advantages of using DBUri
and XDBUri
:
DBMS_METADATA
uses DBUri
s to reference XSL stylesheets. XDBUri
can also be used to reference XSL stylesheets stored in ORACLE XML DB Repository.Using DBUri
/XDBUri
has performance benefits because you interact directly with the database rather than through a Web server.
DBUri
you can access an XML document from the database without using SQL.
Since the files or resources in ORACLE XML DB Repository are stored in tables, you can access them either through the XDBUri
or by using the table metaphor through the DBUri
.
URIs or Universal Resource Identifiers identify resources such as Web pages anywhere on the Web. Oracle9i provides the following UriTypes
for storing and accessing external and internal Uri-references:
.
Implements the HTTP protocol for accessing remote pages.
Stores URLs to external Web pages or files. Accesses these files using Hyper Text Transfer Protocol (HTTP) protocol.These datatypes are object types with member functions that can be used to access objects or pages pointed to by the objects. By using UriType
, you can:
UriType
.These are related by an inheritance hierarchy. UriType
is an abstract type and the DBUriType,
HttpUriType
, and XDBUriType
are subtypes of UriType
. You can reference data stored in CLOBs or other columns and expose them as URLs to the external world. Oracle9i provides a standard servlet than can be installed and run under the Oracle Servlet engine that interprets these URLs.
Oracle already provides the PL/SQL package UTL_HTTP
and the Java class java.net.URL
to fetch URL references. The advantages of defining this new UriType
datatype in SQL are:
The UriType
abstract type supports a variety of functions that can be used over any subtype. Table 12-1 lists the UriType
member functions.
Use HttpUriType
to store references to data that can be accessed through the HTTP protocol. HttpUriType
uses the UTL_HTTP
package to fetch the data and hence the session settings for the package can also be used to influence the HTTP fetch using this mechanism. Table 12-2 lists the HttpUriType
member functions.
HttpUriType Method | Description |
---|---|
getClob |
Returns the value pointed to by the URL as a character LOB value. The character encoding is the same as the database character set. |
getUrl |
Returns stored URL. |
getExternalUrl |
Similar to |
getBlob |
Gets the binary content as a BLOB. If the target data is non-binary then the BLOB will contain the XML or text representation of the data in the database character set. |
getXML |
Returns the |
getContentType() |
Returns the MIME information for the URL. See also "getContentType() Function". |
createUri() |
httpUriType constructor. Constructs in |
httpUriType() |
httpUriType constructor. Constructs in |
getContentType()
function returns the MIME information for the URL. The HttpUriType
de-references the URL and gets the MIME header information. You can use this information to decide whether to retrieve the URL as BLOB or CLOB based on the MIME type. You would treat a Web page with a MIME type of x/jpeg
as a BLOB, and one with a MIME type of text/plain
or text/html
as a CLOB.
Getting the content type does not fetch all the data. The only data transferred is the HTTP headers (for HTTPURiType
) or the metadata of the column (for DBUriType
). For example:
declare httpuri HttpUriType; x clob; y blob; begin httpuri := HttpUriType('http://www.oracle.com/object1'); if httpuri.getContentType() = 'application-x/bin' then y := httpuri.getblob(); else x := httpuri.getclob(); end if; end;
getXML()
function returns XMLType
information for the result. If the document is not valid XML (or XHTML) an error is thrown.
DBUri
, a database relative to URI, is a special case of the Uri-ref
mechanism, where ref
is guaranteed to work inside the context of a database and session. This ref
is not a global ref
like the HTTP URL; instead it is local ref
(URL) within the database.
You can also access objects pointed to by this URL globally, by appending this DBUri
to an HTTP URL path that identifies the servlet that can handle DBUri
. This is discussed in "Turning a URL into a Database Query with DBUri Servlet" .
The URL syntax is obtained by specifying XPath-like syntax over a virtual XML visualization of the database. See Figure 12-1, "DBUri: Visual or SQL View, XML View, and Associated XPath":
For example, the user scott can see the following virtual XML document.
<?xml version='1.0'?> <oradb SID="ORCL"> <PUBLIC> <ALL_TABLES> .. </ALL_TABLES> <EMP> <!-- EMp table --> </EMP> </PUBLIC> <SCOTT> <ALL_TABLES> .... </ALL_TABLES> <EMP> <ROW> <EMPNO>1001</EMPNO> <ENAME>John</ENAME> <EMP_SALARY>20000</EMP_SALARY> </ROW> <ROW> <EMPNO>2001</EMPNO> </ROW> </EMP> <DEPT> <ROW> <DEPTNO>200</DEPTNO> <DNAME>Sports</DNAME> </ROW> </DEPT> </SCOTT> <JONES> <CUSTOMER_OBJ_TAB> <ROW> <NAME>xxx</NAME> <ADDRESS> <STATE>CA</STATE> <ZIP>94065</ZIP> </ADDRESS> </ROW> </CUSTOMER_OBJ_TAB> </JONES> </oradb>
This XML document is constructed at the time you do the query and based on the privileges that you have at that moment.
You can make the following observations from the previous example:
scott
can see the scott
database schema and jones
database schema. These are schemas on which the user has some table or views that he can read.emp
shows up as EMP with row element tags. This is the default mapping for all tables. The same for dept
and the customer_obj_tab
table under the jones
schema.PUBLIC
element under which tables and views are accessible without schema qualification. For example, a SELECT query such as:
SELECT * FROM emp;
when queried by user scott
, matches the table emp
under the scott
schema and, if not found, tries to match a public synonym named emp
. In the same way, the PUBLIC
element contains:
With the Oracle9i database being visualized as an XML tree, you can perform XPath traversals to any part of the virtual document. This translates to any row-column intersection of the database tables or views. By specifying an XPath over the visualization model, you can create references to any piece of data in the database.
DbUri
is specified in a simplified XPath format. Currently, Oracle does not support the full XPath or XPointer recommendation for DBURType
. The following sections discuss the structure of the DBUri
.
As stated in the previous paragraphs, you can now create DBUris
to any piece of data. You can use the following instances in a column as reference:
.../ROW[empno=7263]/COL_OBJ/OBJ_ATTR
These are the smallest addressable units. For example, you can use:
/oradb/SCOTT/EMP
or
/oradb/SCOTT/EMP/ROW[empno=7263]
There are restrictions on the kind of XPath queries that can be used to specify a reference. In general, the fragment part must:
ROW
element.For example, if you wanted to specify the predicate pono = 100, but the selection path is:
/oradb/scott/purchase_obj_tab/ROW/line_item_list
you must include the pono
predicate along with the ROW
node as:
/oradb/scott/purchase_obj_tab/ROW[pono=100]/line_item_list
DBUri
must identify exactly a single data value, either an object type or a collection. If the data value is an entire row, you indicate that by including a ROW
node. The DBUri
can also point to an entire table.The predicate expressions can use the following XPath expressions:
The predicates can be defined at any element other than the schema and table elements. If you have object columns, you can search on the attribute values as well.
For example, the following DBUri refers to an ADDRESS
column containing state, city, street, and zip code attributes:
/oradb/SCOTT/EMP/ROW[ADDRESS/STATE='CA' OR ADDRESS/STATE='OR']/ADDRESS[CITY='Portland' OR /ZIPCODE=94404]/CITY
This DBUri
identifies the city
attribute whose state is either California or Oregon, or whose city name is Portland, or whose zipcode is 94404.
See Also:
|
The DBUri
can identify various objects, such as a table, a particular row, a particular column in a row, or a particular attribute of an object column. The following subsections describe how to identify different object types.
This returns an XML document that retrieves the whole table. The enclosing tag is the name of the table. The row values are enclosed inside a ROW
element, as follows, using the following syntax:
/oradb/schemaname
/tablename
For example:
/oradb/SCOTT/EMP
returns an XML document with a format like the following:
<?xml version="1.0"?> <EMP> <ROW> <EMPNO>7369</EMPNO> <ENAME>Smith</ENAME> ... <!-- other columns --> </ROW> <!-- other rows --> </EMP>
This identifies a particular ROW
element in the table. The result is an XML document that contains the ROW
element with its columns as child elements. Use the following syntax:
/oradb/schemaname
/tablename
/ROW[predicate_expression
]
For example:
/oradb/SCOTT/EMP/ROW[EMPNO=7369]
returns the XML document with a format like the following:
<?xml version="1.0"?> <ROW> <EMPNO>7369</EMPNO> <ENAME>SMITH</ENAME> <JOB>CLERK</JOB> <!-- other columns --> </ROW>
In this case, a target column or an attribute of a column is identified and retrieved as XML.
Use the following syntax:
/oradb/schemaname
/tablename
/ROW[predicate_expression
]/columnname
/oradb/schemaname
/tablename
/ROW[predicate_expression
]/columnname
/attribute1/../attributen
/oradb/SCOTT/EMP/ROW[EMPNO=7369 and DEPTNO=20]/ENAME
retrieves the ename
column in the emp
table, where empno
is 7369
, and department
number
is 20
, as follows:
<?xml version="1.0"?> <ENAME>SMITH</ENAME>
/oradb/SCOTT/EMP/ROW[EMPNO=7369]/ADDRESS/STATE
retrieves the state
attribute inside an address
object column for the employee
whose empno
is 7369
, as follows:
<?xml version="1.0"?> <STATE>CA</STATE>
In many cases, it can be useful to retrieve only the text values of a column and not the enclosing tags. For example, if XSL stylesheets are stored in a CLOB column, you can retrieve the document text without having any enclosing column name tags. You can use the text()
function for this. It specifies that you only want the text value of the node. Use the following syntax:
/oradb/schemaname
/tablename
/ROW[predicate_expression
]/columnname
/text()
For example:
/oradb/SCOTT/EMP/ROW[EMPNO=7369]/ENAME/text()
retrieves the text value of the employee name, without the XML tags, for an employee with empno
= 7369
. This returns a text document, not an XML document, with value SMITH
.
Note: The XPath alone does not constitute a valid URI. Oracle calls it a |
A DBUri can access columns and attributes and is loosely typed Object references can only access row objects. DBUri is a superset of this reference mechanism.
A DBUri is scoped to a database and session. You must already be connected to the database in a particular session context. The schema and permissions needed to access the data are resolved in that context.
Uri-ref can be used in a number of scenarios, including those described in the following sections:
In the case of a travel story Web site where you store travel stories in a table, you might create links to related stories. By representing these links in a DBUriType
column, you can create intra-database links that let you retrieve related stories through queries.
Applications can use XSL stylesheets to convert XML into other formats. The stylesheets are represented as XML documents, stored as CLOBs. The application can use DBUriType
objects:
Table 12-3 lists the DBUriType
methods and functions.
Some of the functions that have a different or special behavior in the DBUriType
are described in the following subsections.
This function returns the MIME information for the URL. The content type for a DBUriType
object can be:
DBUri
points to a scalar value, where the MIME type is text/plain
.text/xml
.For example, consider the table dbtab
under SCOTT
:
CREATE TABLE DBTAB( a varchar2(20), b blob);
A DBUriType
of '/SCOTT/DBTAB/ROW/A'
has a content type of text/xml
, since it points to the whole column and the result is XML.
A DBUriType
of '/SCOTT/DBTAB/ROW/B'
also has a content type of text/xml
.
A DBUriType
of '/SCOTT/DBTAB/ROW/A/text()'
has a content type of text/plain.
A DBUriType
of '/SCOTT/DBTAB/ROW/B/text()'
has a content type of text/plain
.
In the case of DBUri
, scalar binary data is handled specially. In the case of a getClob()
call on a DBUri
'/SCOTT/DBTAB/ROW/B/text()'
where B is a BLOB column, the data is converted to HEX and sent out.
In the case of a getBlob()
call, the data is returned in binary form. However, if an XML document is requested, as in '/SCOTT/DBTAB/ROW/B'
, then the XML document will contain the binary in HEX form.
XDBUriType
is a new subtype of UriType
. It provides a way to expose documents in the ORACLE XML DB Repository as URIs that can be embedded in any UriType
column in a table.
The URL part of the URI is the hierarchical name of the XML document it refers to. The optional fragment part uses the XPath syntax, and is separated from the URL part by '#'
.
The following are examples of ORACLE XML DB URIs:
/home/scott/doc1.xml /home/scott/doc1.xml#/purchaseOrder/lineItem
where:
'/home/scott'
is a folder in Oracle XML DB Repositorydoc1.xml
is an XML document in this folderpurchaseOrder/lineItem
refers to the line item in this purchase order document.Table 12-4 lists the XDBUriType
methods. These methods do not take any arguments.
XDBUriType
is automatically registered with UriFactory
so that an XDBUriType
instance can be generated by providing the URI to the getURI
method.
Currently, XDBUriType
is the default UriType
generated by the UriFactory.getUri
method, when the URI does not have any of the recognized prefixes, such as "http://","/DBURI"
, or "/ORADB"
.
All DBUriType
URIs should have a prefix of either /DBURI
or /ORADB,
case insensitive.
For example, the following statement returns an XDBUriType
instance that refers to /home/scott/doc1.xml
:
SELECT sys.UriFactory.getUri('/home/scott/doc1.xml') FROM dual;
The following is an example of how XDBUriType
is used:
CREATE TABLE uri_tab ( poUrl SYS.UriType, -- Note that we have created an abstract type column --so that any type of URI can be used poName VARCHAR2(1000) ); -- insert an absolute url into poUrl -- the factory will create an XDBUriType since there's no prefix INSERT INTO uri_tab VALUES (UriFactory.getUri('/public/orders/po1.xml'), 'SomePurchaseOrder'); -- Now get all the purchase orders SELECT e.poUrl.getClob(), poName FROM uri_tab e; -- Using PL/SQL, you can access table uri_tab as follows: declare a UriType; begin -- Get the absolute URL for purchase order named like 'Some%' SELECT poUrl into a from uri_tab WHERE poName like 'Some%'; printDataOut(a.getClob()); end; /
Since getXML()
returns an XMLType
, it can be used in the EXTRACT
family of operators. For example:
SELECT e.poUrl.getClob() FROM uri_tab e WHERE extractValue(e.poUrl.getXML(),'/User') = 'SCOTT';
This statement retrieves all Purchase Orders for user SCOTT.
UriType
columns can be indexed natively in Oracle9i database using Oracle Text. No special datastore is needed.
This section describes how to store pointers to documents and retrieve these documents across the network, either from the database or a Web site.
As explained earlier, UriType
is an abstract type containing a VARCHAR2
attribute that specifies the URI. The object type has functions for traversing the reference and extracting the data.
You can create columns using UriType
to store these pointers in the database. Typically, you declare the column using the UriType
, and the objects that you store use one or more of the derived types such as HttpUriType
.
Table 12-4 lists some useful UriType
methods.
You can create a list of all purchase orders with URL references to them as follows:
CREATE TABLE uri_tab ( poUrl SYS.UriType, -- Note that we have created abstract type columns -- if you know what kind of uri's you are going to store, you can -- create the appropriate types. poName VARCHAR2(200) ); -- insert an absolute url into SYS.UriType..! -- the Urifactory creates the correct instance (in this case a HttpUriType INSERT INTO uri_tab VALUES (sys.UriFactory.getUri('http://www.oracle.com/cust/po'),'AbsPo'); -- insert a URL by directly calling the SYS.HttpUriType constructor. -- Note this is strongly discouraged. Note the absence of the -- http:// prefix when creating SYS.HttpUriType instance through the default -- constructor. INSERT INTO uri_tab VALUES (sys.HttpUriType('proxy.us.oracle.com'),'RelPo'); -- Now extract all the purchase orders SELECT e.poUrl.getClob(), poName FROM uri_tab e; -- In PL/SQL declare a SYS.UriType; begin -- absolute URL SELECT poUrl into a from uri_Tab WHERE poName like 'AbsPo%'; SELECT poUrl into a from uri_Tab WHERE poName like 'RelPo%'; -- here u need to supply a prefix before u can get at the data..! printDataOut(a.getClob()); end; /
See:
"Creating Instances of UriType Objects with the UriFactory Package" for a description of how to use |
You can create columns of the UriType
directly and insert HttpUriTypes
, XDBUriTypes
, and DBUriTypes
into that column. You can also query the column without knowing where the referenced document lies. For example, from the previous example, you inserted DBUri
references into the uri_tab table as follows:
INSERT INTO uri_tab VALUES (UriFactory.getUri( '/SCOTT/PURCHASE_ORDER_TAB/ROW[PONO=1000]'),'ScottPo');
This insert assumes that there is a purchase order table in the SCOTT
schema. Now, the URL column in the table contains values that are pointing through HTTP to documents globally as well as pointing to virtual documents inside the database.
A SELECT on the column using the getClob()
method would retrieve the results as a CLOB irrespective of where the document resides. This would retrieve values from the global HTTP address stored in the first row as well as the local DBUri
reference.:
SELECT e.poURL.getclob() FROM uri_tab e;
HttpUriType
and DBUriType
are subtypes of UriType
and implement the functions for HTTP and DBUri
references respectively.
The following example creates a table with a column of type DBUriType
and assigns a value to it.
CREATE TABLE DBURiTab(DBUri DBUriType, dbDocName VARCHAR2(2000)); -- insert values into it..! INSERT INTO DBUriTab VALUES (sys.DBUriType.createUri('/ORADB/SCOTT/EMP/ROW[EMPNO=7369]'),'emp1'); INSERT INTO DBUriTab VALUES (sys.DBUriType('/SCOTT/EMP/ROW[EMPNO=7369]/',null); -- access the references SELECT e.DBUri.getCLOB() from DBUriTab e;
The functions in the UriFactory
package generate instances of the appropriate UriType
subtype (HttpUriType, DBUriType,
and XDBUriType
). This way, you can avoid hardcoding the implementation in the program and handle whatever kinds of URI strings are used as input. See Table 12-5.
The getUri
method takes a string representing any of the supported kinds of URI and returns the appropriate subtype instance. For example:
http://
, getUri
creates and returns an instance of a SYS.HttpUriType
object./oradb/
or /dburi/
, getUri
creates and returns an instance of a SYS.DBUriType
object.getUri
creates and returns an instance of a SYS.XDBUriType
object.
The UriFactory package lets you register new UriType subtypes:
CREATE TYPE
statement in SQL.UriFactory.registerURLHandler
, so that the UriFactory
package can create an instance of your new subtype when it receives a URI starting with the new prefix you defined.For example, you can invent a new protocol ecom://
and define a subtype of UriType
to handle that protocol. Perhaps the subtype implements some special logic for getCLOB
, or does some changes to the XML tags or data within getXML
. When you register the ecom://
prefix with UriFactory, any calls to UriFactory.getUri
generate the new subtype instance for URIs that begin with the ecom://
prefix.
Assume you are storing different kinds of URIs in a single table:
CREATE TABLE url_tab (urlcol varchar2(80)); -- Insert an HTTP URL INSERT INTO url_tab VALUES ('http://www.oracle.com/'); -- Insert a database URI INSERT INTO url_tab VALUES ('/oradb/SCOTT/EMPLOYEE/ROW[ENAME="Jack"]'); -- Create a new type to handle a new protocol called ecom:// CREATE 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 getUrl 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:// prefix. begin -- register a new handler for ecom:// prefixes. The handler -- type name is ECOMUriTYPE, schema is SCOTT -- Ignore the prefix case, so that UriFactory creates the same subtype -- for URIs beginning with ECOM://, ecom://, eCom://, and so on. -- Strip the prefix before calling the createUri function -- so that the string 'ecom://' is not stored inside the -- ECOMUriTYPE object. (It is added back automatically when -- you call ECOMUriTYPE.getURL.) urifactory.registerURLHandler ( prefix => 'ecom://', schemaname => 'SCOTT', typename => 'ECOMURITYPE', ignoreprefixcase => true, stripprefix => true ); end; / -- Now the example inserts this new type of URI into the table. insert into url_tab values ('ECOM://company1/company2=22/comp'); -- Use the factory to generate an instance of the appropriate -- subtype for each URI in the table. select urifactory.getUri(urlcol) from url_tab; -- would now generate HttpUriType('www.oracle.com'); -- a Http uri type instance DBUriType('/oradb/SCOTT/EMPLOYEE/ROW[ENAME="Jack"],null); -- a DBUriType EComUriType('company1/company2=22/comp'); -- an EComUriType instance
Deriving a new class for each protocol has these advantages:
DBUri
you can implement some specialized indexes that can directly go and fetch the data from the disk blocks rather than executing SQL queries.You can create an instance of DBUriType
type by specifying the path expression to the constructor or the UriFactory
methods. However, you also need methods to generate these objects dynamically, based on strings stored in table columns. You do this with the SQL function SYS_DBURIGEN()
.
The following example uses SYS_DBURIGEN()
to generate a URI of datatype DBUriType
pointing to the email column of the row in the sample table hr.employees
where the employee_id = 206
:
SELECT SYS_DBURIGEN(employee_id, email) FROM employees WHERE employee_id = 206; SYS_DBURIGEN(EMPLOYEE_ID,EMAIL)(URL, SPARE) ------------------------------------------------------------------- DBURITYPE('/PUBLIC/EMPLOYEES/ROW[EMPLOYEE_ID = "206"]/EMAIL', NULL)
SYS_DBURIGEN()
takes as its argument one or more columns or attributes, and optionally a rowid, and generates a URI of datatype DBUriType
to a particular column or row object. You can use the URI to retrieve an XML document from the database. The function takes an additional parameter to indicate if the text value of the node is needed. See Figure 12-2.
All columns or attributes referenced must reside in the same table. They must reference a unique value. If you specify multiple columns, the initial columns identify the row in the database, and the last column identifies the column within the row.
By default, the URI points to a formatted XML document. To point only to the text of the document, specify the optional text()
keyword.
See Also:
Oracle9i SQL Reference for |
If you do not specify an XML schema, Oracle interprets the table or view name as a public synonym.
The column or attribute passed to the SYS_DBURIGEN()
function must obey the following rules:
VALUE
and REF
operators. The column may come from a TABLE()
subquery or an inline view, as long as the inline view does not rename the columns.SYS_DBURIGEN()
function must come from the same table or view.PUBLIC
keyword is used instead of the schema. When the DBUri
is accessed, the table name resolves to the same table, synonym, or view that was visible by that name when the DBUri was created.DBUri
, by default, retrieves an XML document containing the result. To retrieve only the text value, use the text()
keyword as the final argument to the function.
For example:
select SYS_DBURIGEN(empno,ename,'text()') from scott.emp, WHERE empno=7369;
or you can just generates a URL of the form:
/SCOTT/EMP/ROW[EMPNO=7369]/ENAME/text()
For example:
select SYS_DBURIGEN(empno) from emp WHERE empno=7369;
uses the empno
both as the key column and the referenced column, generating a URL of the form:
/SCOTT/EMP/ROW[EMPNO=7369]/EMPNO,
for the row with empno=7369
CREATE TABLE doc_list_tab(docno number primary key, doc_ref SYS.DBUriType); -- inserts /SCOTT/EMP/ROW[rowid='xxx']/EMPNO INSERT INTO doc_list_tab values(1001, (select SYS_DBURIGEN(rowid,empno) from emp where empno = 100)); -- insert a Uri-ref to point to the ename column of emp! INSERT INTO doc_list_tab values(1002, (select SYS_DBURIGEN(empno, ename) from emp where empno = 7369)); -- result of the DBURIGEN looks like, /SCOTT/EMP/ROW[EMPNO=7369]/ENAME
When selecting the results of a large column, you might want to retrieve only a portion of the result and create a URL to the column instead. For example, consider the case of a travel story Web site. If all the travel stories are stored in a table, and users search for a set of relevant stories, you do not want to list each entire story in the result page. Instead, you show the first 100 characters or gist of the story and then return a URL to the full story.This can be done as follows:
Assume that the travel story table is defined as follows:
CREATE TABLE travel_story ( story_name varchar2(100), story clob ); -- insert some value..! INSERT INTO travel_story values ('Egypt','This is my story of how I spent my time in Egypt, with the pyramids in full view from my hotel room');
Now, you create a function that returns only the first 20 characters from the story:
create 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; /
Now, you create a view that selects out only the first 100 characters from the story and then returns a DBUri
reference to the story column:
CREATE VIEW travel_view as select story_name, charfunc(story) short_story, SYS_DBURIGEN(story_name,story,'text()') story_link FROM travel_story;
Now, a SELECT from the view returns the following:
SELECT * FROM travel_view; STORY_NAME SHORT_STORY STORY_LINK ----------------------------------------------------------------------------- Egypt This is my story of h SYS.DBUriType('/PUBLIC/TRAVEL_STORY/ROW[STORY_NAME='Egypt']/STORY/text()')
You can use SYS_DBURIGEN()
in the RETURNING
clause of DML statements to retrieve the URL of an object as it is inserted.
For example, consider the table CLOB_TAB
:
CREATE TABLE clob_tab ( docid number, doc clob);
When you insert a document, you might want to store the URL of that document in another table, URI_TAB
.
CREATE TABLE uri_tab (docs sys.DBUriType);
You can specify the storage of the URL of that document as part of the insert into CLOB_TAB
, using the RETURNING
clause and the EXECUTE IMMEDIATE
syntax to execute the SYS_DBURIGEN
function inside PL/SQL as follows:
declare ret sys.dburitype; begin -- exucute the insert 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; /
The URL created has the form:
/SCOTT/CLOB_TAB/ROW[DOCID="xxx"]/DOC/text()
Note: The |
You can make table data accessible from your browser or any Web client, using the URI notation within a URL to specify the data to retrieve:
DBUri
servlet linked in with the database server.DBUriType
object using that URI, call the UriType
methods to retrieve the data, and return the values in the form of a Web page or an XML document.
Note: The Oracle servlet engine is being desupported. Consequently the |
For the preceding methods, a servlet runs for accessing this information through HTTP. This servlet takes in a path expression following the servlet name as the DBUri
reference and outputs the document pointed to by the DBUri to the output stream.
The generated document can be a Web page, an XML document, plain text, and so on. You can specify the MIME type so that the browser or other application knows what kind of content to expect:
text/xml
and text/plain
. If the URI ends in a text()
function, then the text/plain
MIME type is used, else an XML document is generated with the MIME type of text/xml
.binary/x-jpeg
or some other value using the contenttype
argument to the servlet.For example, to retrieve the empno
column of the employee
table, you can write a URL such as one of the following:
-- Generates a contenttype of text/plain http://machine.oracle.com:8080/oradb/SCOTT/EMP/ROW[EMPNO=7369]/ENAME/text() -- Generates a contenttype of text/xml http://machine.oracle.com:8080/oradb/SCOTT/EMP/ROW[EMPNO=7369/ENAME
where the machine machine.oracle.com
is running the Oracle9i database, with a Web service at port 8080 listening to requests. oradb
is the virtual path that maps to the servlet.
Table 12-6 describes the three optional arguments you can pass to DBUri
servlet to customize the output.
DbUriServlet
is built into the database, and the installation is handled by the ORACLE XML DB configuration file. To customize the installation of the servlet, you need to edit it. You can edit the config file, xdbconfig.xml
under the ORACLE XML DB user, through WebDAV, FTP, from Oracle Enterprise Manager, or in the database. To update the file using FTP or WebDAV, simply download the document, edit it as necessary, and save it back in the database. There are several things that can be customized using the configuration file.
Notice that the servlet is installed at /oradb/*
specified in the servlet-pattern tag. The * is necessary to indicate that any path following oradb
is to be mapped to the same servlet. The oradb is published as the virtual path. Here, you can change the path that will be used to access the servlet.
For example, to have the servlet installed under /dburi/*
, you can run the following PL/SQL:
declare doc XMLType; doc2 XMLType; begin doc := dbms_xdb.cfg_get(); select updateXML(doc, '/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servl et-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 the xdbconfig.xml
configuration file. See Appendix A, "Installing and Configuring Oracle XML DB" and Chapter 20, "Writing Oracle XML DB Applications in Java". The servlet can be removed by deleting the servlet-pattern for this servlet. This can also be done using updateXML()
to update the servlet-mapping element to null.
Servlet security is handled by Oracle9i database using roles. When users log in to the servlet, they use their database username and password. The servlet will check to make sure the user logging in belongs to one of the roles specified in the configuration file. The roles allowed to access the servlet are specified in the security-role-ref
tag. By default, the servlet is available to the special role authenticatedUser. Any user who logs into the servlet with any valid database username and password belongs to this role.
This parameter can be changed to restrict access to any role(s) in the database. To change from the default authenticated-user role to a role that you have created, say servlet-users
, run:
declare doc XMLType; doc2 XMLType; doc3 XMLType; begin doc := dbms_xdb.cfg_get(); select updateXML(doc, '/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servl et-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/servl et-list/servlet[servlet-name="DBUriServlet"]/security-role-ref/role-link/text()' , 'servlet-users') into doc3 from dual; dbms_xdb.cfg_update(doc3); commit; end;
/
The UriFactory
, as explained in "Creating Instances of UriType Objects with the UriFactory Package", takes a URL and generates the appropriate subtypes of the UriType to handle the corresponding protocol. For HTTP URLs, UriFactory
creates instances of the HttpUriType
. But when you have an HTTP URL that represents a URI path, it is more efficient to store and process it as a DBUriType
instance in the database. The DBUriType
processing involves fewer layers of communication and potentially fewer character conversions.
After you install OraDBUriServlet
, so that any URL such as http://machine-name/servlets/oradb/
gets handled by that servlet, you can configure the UriFactory
to use that prefix and create instances of the DBUriType
instead of HttpUriType
:
begin -- register a new handler for the dburi prefix.. urifactory.registerHandler('http://machine-name/servlets/oradb' ,'SYS','DBUriTYPE', true,true); end; /
After you execute this block in your session, any UriFactory.getUri()
call in that session automatically creates an instance of the DBUriType for those HTTP URLs that have the prefix.
See Also:
Oracle9i XML API Reference - XDK and Oracle XML DB for details of all functions in |