The URI Types

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

From an object-oriented design standpoint, there are better ways of dealing 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.

 

 

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