13.6. Predefined Object Types
Starting with Oracle9i Database Release 1, Oracle provides a collection of useful, predefined
object types:
XMLType
Use this to store and manipulate XML data.
Various URI types
Use these to store uniform resource identifiers (such as HTML addresses).
Various Any types
Use these to define a PL/SQL variable that can hold any type of data.
The following subsections discuss these predefined object types in more detail.
13.6.1. The XMLType Type
Oracle9i Database Release 1 introduced a native object type called XMLType. You can use XMLType to define database columns and PL/SQL variables containing XML documents. Methods defined on XMLType enable you to instantiate new XMLType values, to extract portions of an XML document, and to otherwise manipulate the contents of an XML document in various ways.
| In Oracle9i Database Release 1, you needed to use the "SYS." prefix when referencing the XMLType object type. Staring with Oracle9i Database Release 2, Oracle allows synonyms to point to object types, and the database creation script ($ORACLE_HOME/rdbms/admin/dbmsxmlt.sql) that creates XMLType now also creates the public synonym XMLTYPE, which points to the SYS.XMLType predefined object type. |
|
XML is a huge subject that we can't hope to cover in any detail in this book. However, if you're working with XML from PL/SQL, there are at least two things you need to know about:
XMLType
A built-in object type, introduced in this section, which enables you to store XML documents in a database column or in a PL/SQL variable. XMLType was introduced in Oracle9i Database Release 1.
XQuery
A query language to use in retrieving and constructing XML documents. XQuery is new in Oracle Database 10g Release 2.
If you begin with these two things and drill down, you'll be led to many other XMLish topics you need to know about: XPath for referring to portions of an XML document, XML Schema for describing document structure, and so forth.
|
Using XMLType, you can easily create a table to hold XML data:
CREATE TABLE falls (
fall_id NUMBER,
fall XMLType
);
The fall column in this table is of XMLType
and can hold XML
data. To store XML data into this column, you must invoke the static CreateXML method, passing it your XML data. CreateXML accepts XML data as input and instantiates a new XMLType object to hold that data. The new object is then returned as the method's result, and it is that object that you must store in the column. CreateXML is overloaded to accept both VARCHAR2 strings and CLOBs as input.
Use the following INSERT statements to create three XML documents in the falls table:
INSERT INTO falls VALUES (1, XMLType.CreateXML(
'<?xml version="1.0"?>
<fall>
<name>Munising Falls</name>
<county>Alger</county>
<state>MI</state>
<url>
http://michiganwaterfalls.com/munising_falls/munising_falls.html
</url>
</fall>'));
INSERT INTO falls VALUES (2, XMLType.CreateXML(
'<?xml version="1.0"?>
<fall>
<name>Au Train Falls</name>
<county>Alger</county>
<state>MI</state>
<url>
http://michiganwaterfalls.com/autrain_falls/autrain_falls.html
</url>
</fall>'));
INSERT INTO falls VALUES (3, XMLType.CreateXML(
'<?xml version="1.0"?>
<fall>
<name>Laughing Whitefish Falls</name>
<county>Alger</county>
<state>MI</state>
</fall>'));
You can query XML data in the table using various XMLType methods. The existsNode method
used in the following example allows you to test for the existence of a specific XML node in an XML document. The built-in SQL EXISTSNODE function, also in the example, performs the same test. Whether you use the method or the built-in function, you identify the node of interest using an XPath expression.
Both of the following statements produce the same output:
SQL> SELECT fall_id
2 FROM falls f
3 WHERE f.fall.existsNode('/fall/url') > 0;
SQL> SELECT fall_id
2 FROM falls
3 WHERE EXISTSNODE(fall,'/fall/url') > 0;
FALL_ID
----------
1
2
You can, of course, also work with XML data from within PL/SQL. In the following example, we retrieve the fall column for Munising Falls into a PL/SQL variable that is also of XMLType. Thus, we retrieve the entire XML document into our PL/SQL program, where we can work further with it. After retrieving the document, we extract and print the text from the /fall/url node.
<<demo_block>>
DECLARE
fall XMLType;
url VARCHAR2(100);
BEGIN
--Retrieve XML for Munising Falls
SELECT fall INTO demo_block.fall
FROM falls f
WHERE f.fall_id = 1;
--Extract and display the URL for Munising Falls
url := fall.extract('/fall/url/text( )').getStringVal;
DBMS_OUTPUT.PUT_LINE(url);
END;
We'd like to call your attention to the following two lines:
SELECT fall INTO demo_block.fall
Our variable name, fall, matches the name of the column in the database table. In our SQL query, therefore, we qualify our variable name with the name of our PL/SQL block.
url := fall.extract('/fall/url/text( )').getStringVal;
To get the text of the URL, we invoke two of XMLType's methods:
extract
Returns an XML document, of XMLType, containing only the specified fragment of the original XML document. Use XPath notation to specify the fragment you want returned.
getStringVal
Returns the text of an XML document.
In our example, we apply the getStringVal method to the XML document returned by the extract method, thus retrieving the text for the Munising Fall's URL. The extract method returns the contents of the <url> node as a XMLType object, and getStringVal then returns that content as a text string that we can display.
You can even index XMLType columns to allow for efficient retrieval of XML documents based on their content. You do this by creating a function-based index, for which you need the QUERY REWRITE privilege. The following example creates a function-based index on the first 80 characters of each falls name:
CREATE INDEX falls_by_name
ON falls f (
SUBSTR(
XMLType.getStringVal(
XMLType.extract(f.fall,'/fall/name/text( )')
),1,80
)
);
We had to use the SUBSTR function in the creation of this index. The getStringVal method returns a string that is too long to index, resulting in an ORA-01450: maximum key length (3166) exceeded error. Thus, when creating an index like this, you need to use SUBSTR to restrict the results to some reasonable length.
If you decide to use XMLType in any of your applications, be sure to consult Oracle's documentation for more complete and current information. The XML DB Developer's Guide for Oracle Database 10g Release 2 is an important, if not critical, reference for developers working with XML. The SQL Reference also has some useful information on XMLType and on the built-in SQL functions that support XML.
13.6.2. The URI Types
The URI types
introduced in Oracle9i Database Release 1 consist of a supertype and a collection of subtypes that provide support for storing URIs in PL/SQL variables and in database columns. UriType is the supertype, and a UriType variable can hold any instance of one of the subtypes:
HttpUriType
A subtype of UriType that is specific to HTTP URLs, which usually point to web pages.
DBUriType
A subtype of UriType that supports URLs that are XPath expressions.
XDBUriType
A subtype of UriType that supports URLs that reference Oracle XML DB objects. XML DB is Oracle's name for a set of XML technologies built into the database.
To facilitate your work with URIs, Oracle also provides a UriFactory
package that automatically generates the appropriate URI type for whatever URI you pass to it.
The URI types are created by the script named dbmsuri.sql $ORACLE_HOME/rdbms/admin. All the types and subtypes are owned by the user SYS. In Oracle9i Database Release 1, you must use the "SYS." prefix to reference the URI types. From Oracle9i Database Release 2 onwards, you do not need to use the "SYS." prefix.
The following code example demonstrates the use of HttpUriType:
DECLARE
WebPageURL HttpUriType;
WebPage CLOB;
BEGIN
--Create an instance of the type pointing
--to a message from Jonathan Gennick
WebPageURL := HttpUriType.createUri(
'http://gennick.com/message.plsql');
--Retrieve the message via HTTP
WebPage := WebPageURL.getclob( );
--Display the message
DBMS_OUTPUT.PUT_LINE((SUBSTR(WebPage,1,60)));
END;
The output from this code example will be:
Brighten the corner where you are.
For more information on the use of the UriType family, see Chapter 9, Accessing Data Through URIs, of the XML DB Developer's Guide for Oracle Database 10g Release 2.
13.6.3. The Any Types
Beginning with Oracle9i Database Release 1, a family of types known as the Any types
enables you to write programs to manipulate data when you don't know the type of that data until runtime. Member functions support introspection, allowing you to determine the type of a value at runtime and to access that value.
The following predefined types belong to this family:
AnyData
Can hold a single value of any type, whether it's a built-in scalar datatype, a user-defined object type, a nested table, a large object, a varying array (VARRAY), or any other type not listed here.
AnyDataSet
Can hold a set of values of any type, as long as all values are of the same type.
AnyType
Can hold a description of a type. Think of this as an AnyData without the data.
The Any types are created by a script named dbmsany.sql found in $ORACLE_HOME/rdbms/admin, and are owned by the user SYS. As with the URI types, in Oracle9i Database Release 1 you must use a "SYS." prefix to reference the Any types, but this is no longer necessary beginning in Oracle 9i Database Release 2).
In addition to creating the Any types, the dbmsany.sql script also creates a package named DBMS_TYPES
that defines the constants in the following list. You can use these constants in conjunction with introspection functions
such as GETTYPE in order to determine the type of data held by a given AnyData or AnyDataSet variable. The specific numeric values assigned to the constants are not important; rely on the constants, not on their underlying values.
TYPECODE_DATE
TYPECODE_NUMBER
TYPECODE_RAW
TYPECODE_CHAR
TYPECODE_VARCHAR2
TYPECODE_VARCHAR
TYPECODE_MLSLABEL
TYPECODE_BLOB
TYPECODE_BFILE
TYPECODE_CLOB
TYPECODE_CFILE
TYPECODE_TIMESTAMP
TYPECODE_TIMESTAMP_TZ
TYPECODE_TIMESTAMP_LTZ
TYPECODE_INTERVAL_YM
TYPECODE_INTERVAL_DS
TYPECODE_REF
TYPECODE_OBJECT
TYPECODE_VARRAY
TYPECODE_TABLE
TYPECODE_NAMEDCOLLECTION
TYPECODE_OPAQUE
The following example creates two user-defined types representing two kinds of geographic features. The subsequent PL/SQL block then uses SYS.AnyType to define a heterogeneous array of features (i.e., each array element can be of a different datatype).
First, you'll need to create the following two types:
CREATE OR REPLACE TYPE waterfall AS OBJECT (
name VARCHAR2(30),
height NUMBER
);
CREATE OR REPLACE TYPE river AS OBJECT (
name VARCHAR2(30),
length NUMBER
);
Next, execute the following PL/SQL code block:
DECLARE
TYPE feature_array IS VARRAY(2) OF SYS.AnyData;
features feature_array;
wf waterfall;
rv river;
ret_val NUMBER;
BEGIN
--Create an array where each element is of
--a different object type
features := feature_array(
AnyData.ConvertObject(
waterfall('Grand Sable Falls',30)),
AnyData.ConvertObject(
river('Manistique River', 85.40))
);
--Display the feature data
FOR x IN 1..features.COUNT LOOP
--Execute code pertaining to whatever object type
--we are currently looking at. NOTE! Replace GENNICK
--with whatever schema you are using.
CASE features(x).GetTypeName
WHEN 'GENNICK.WATERFALL' THEN
ret_val := features(x).GetObject(wf);
DBMS_OUTPUT.PUT_LINE('Waterfall: '
|| wf.name || ', Height = ' || wf.height || ' feet.');
WHEN 'GENNICK.RIVER' THEN
ret_val := features(x).GetObject(rv);
DBMS_OUTPUT.PUT_LINE('River: '
|| rv.name || ', Length = ' || rv.length || ' miles.');
END CASE;
END LOOP;
END;
Finally, your output should appear as follows:
Waterfall: Grand Sable Falls, Height = 30 feet.
River: Manistique River, Length = 85.4 miles.
Let's look at this code one piece at a time. The features are stored in a VARRAY, which is initialized as follows:
features := feature_array(
AnyData.ConvertObject(
waterfall('Grand Sable Falls',30)),
AnyData.ConvertObject(
river('Manistique River, 85.40))
);
Working from the inside out and focusing on Grand Sable Falls, you can interpret this code as follows:
waterfall('Grand Sable Falls',30)
Invokes the constructor for the waterfall type to create an object of that type.
AnyData.ConvertObject(
Converts the waterfall object into an instance of SYS.AnyData, allowing it to be stored in our array of SYS.AnyData objects.
feature_array(
Invokes the constructor for the array. Each argument to feature_array is of type AnyData. The array is built from the two arguments we pass.
VARRAYs were discussed in Chapter 12, and you can read about object types in more detail in Chapter 25.
The next significant part of the code is the FOR loop in which each object in the features array is examined. A call to:
features(x).GetTypeName
returns the fully qualified type name of the current features object. For user-defined objects, the type name is prefixed with the schema name of the user who created the object. We had to include this schema name in our WHEN clauses; for example:
WHEN 'GENNICK.WATERFALL' THEN
If you're running this example on your own system, be sure to replace the schema we used (GENNICK) with the one that is valid for you.
| For built-in types such as NUMBER, DATE, and VARCHAR2, GetTypeName will return just the type name. Schema names apply only to user-defined types (i.e., those created using CREATE TYPE). |
|
Once we determined which datatype we were dealing with, we retrieved the specific object using the following call:
ret_val := features(x).GetObject(wf);
In our example, we ignored the return code. There are two possible return code values:
DBMS_TYPES.SUCCESS
The value (or object, in our case) was successfully returned.
DBMS_TYPES.NO_DATA
No data was ever stored in the AnyData variable in question, so no data can be returned.
Once we had the object in a variable, it was an easy enough task to write a DBMS_OUTPUT statement specific to that object type. For example, to print information about waterfalls, we used:
DBMS_OUTPUT.PUT_LINE('Waterfall: '
|| wf.name || ', Height = ' || wf.height || ' feet.');
For more information on the "Any" family of types:
Visit Chapter 25, which examines the Any datatypes from an object-oriented perspective. Check out Oracle's PL/SQL Packages and Types Reference and the SQL Reference. Try out the anynums.pkg and anynums.tst scripts on the book's web site.
| From an object-oriented design standpoint, there are better ways to deal with multiple feature types than the method we used in this section's example. In the real world, however, not everything is ideal, and our example does serve the purpose of demonstrating the utility of the SYS.AnyData predefined object type. |
|
| 
No comments:
Post a Comment