The URI types 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, Oracle9i also implements 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 directory. All the types and subtypes are owned by the user SYS, and as with XMLType in Oracle9i Release 1 you must use a "SYS." prefix to reference them (no longer necessary in Release 2).
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 the Oracle9i XML API Reference—XDK and Oracle XML DB.
12.6.3 The "Any" Types
A new 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.[3]
[3] An introspection function is one that you can use in a program to examine and learn about variables declared by your program. In essence, your program learns about itself—hence the term introspection.
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, and as with XMLType in Oracle9i Release 1, you must use a "SYS." prefix to reference them (no longer necessary in 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 11, and you can read about object types in more detail in Chapter 21.
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).GetTypeNamereturns 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:
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.
|
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 21, which examines the "Any" datatypes from an object-oriented perspective.
· Check out the Oracle9i Supplied PL/SQL Packages and Types Reference and the Oracle9i SQL Reference.
· Try out the anynums.pkg and anynums.tst scripts on the O'Reilly web site.
|
Part IV: SQL in PL/SQL
This part of the book addresses a central element of PL/SQL code construction: the connection to the underlying Oracle database (which takes places through SQL (Structured Query Language). Chapters 13 through 15 show you how to define transactions that update, insert, and delete tables in the database; query information from the database for processing in a PL/SQL program; and execute SQL statements dynamically, using native dynamic SQL (NDS), which was introduced in Oracle8i.
Chapter 13
Chapter 14
Chapter 15
