9.7.1 Large Object Data Types

SQL:1999 added the LOB(Large Object) type to standard SQL. The LOB type can be used to store semistructured data such as XML or text files, and unstructured data such as images, audio, video, and other multimedia objects. The LOB type has variants BLOB (Binary Large Object), CLOB (Character Large Object), and NCLOB (National Character Large Object). LOB types have very restricted functions, such as substring operations. They allow comparisons only for equality or inequality, and they cannot be used for ordering or in GROUP BY or ORDER BY clauses. Because they are large files, LOBs are normally stored and rarely retrieved again. They can be stored in the database itself if they are not too large, or in a file outside the table, by using an LOB locator, a system-generated binary surrogate for the actual address. For example, if we wish to add a student’s picture to a student record, we would add the picture attribute in the CREATE TABLE command for the Student table, using the attribute declaration

The picId is a reference pointer to the file containing an image of the picture. We will discuss the use of reference types in Section 9.7.4.

Oracle does not support all the features of the SQL standard for LOBs, although it includes BLOB, CLOB, and NCLOB types, as well as Bfile, which is used for extremely large objects that are stored outside the database space.

9.7.2 Structured Types

The SQL standard includes structured collection types, including array and row, as well as set (an unordered collection of distinct elements) and multiset (an unordered collection that allows repetition of elements, also called a bag). Oracle has two collection types—varray and nested table—that perform many of the same functions as the SQL standard collection types. A third collection type, an associative array, consists of key-value pairs.

To create an Oracle varray, we must provide a name for the varray type, specify the base type for the values to be stored in the cells of the varray, and specify the size of the varray, using the form

For example, if students can have double majors, we can create a user-defined type for an array with two cells to hold major values, and then use that type as the data type of the majors attribute in a new Stu table. FIGURE 9.9(A) shows the Oracle commands for the MajorsType varray type and the table declaration where the attribute majors is of that type.

Note that Oracle does not support the SQL standard Boolean data type, so the attribute matriculated is given the datatype VARCHAR2(5) here. It uses string values of 'true' or 'false', not Boolean values.

To insert a record into a table with a varray type, we use the type constructor, listing the values to be inserted into the varray, as shown for the three rows of the Stu table inserted in FIGURE 9.9(B) where the constructor MajorsType appears.

Students with only one major are assigned a null value for the second major, as shown in the second row inserted. Queries can be written as usual, or we can use dot notation, as we did when we employed aliases in previous SQL queries, to get the entire majors array as shown in FIGURE 9.9(C). However, Oracle does not allow us to access cells of the array individually in queries by using an index directly. Instead we must use PL/SQL, as shown in the printarray procedure in FIGURE 9.9(D). Recall that Oracle is not case-sensitive, so we have used both uppercase and lowercase in these examples.

9.7.3 User-Defined Data Types (UDTs)

Both standard SQL and Oracle allow users to create user-defined object types. Object types are like classes in the OO model. The attributes can be any SQL type, including other previously defined object types, allowing nesting of object types. An object type can be either the type used for an attribute or the only type used for a table. For example, we can create an AddressType as follows

For each such type, SQL provides a default constructor to allow new instances of the type to be created. This constructor always has the same name and data type as the UDT, and we can invoke it by simply using the name of the type. Users can also create their own constructors when they define the new type.

Object Tables

An object table is one where each row represents an object. Each row, called a row object, has a unique OID as described in Section 9.2.4. In both standard SQL and Oracle, the system generates an OID by default when we insert a row object. Oracle creates an index on the OID of object tables. Note that the value is an internal one that does not appear as part of the row object it refers to and cannot be accessed directly by the user. To create an object table in Oracle, we first define the object type. For example, FIGURE 9.10(A) shows how to create a new StudentType1 as an object type (a class).

As this example illustrates, an object type definition contains the name of the type, a list of attributes with their data types, and member functions and procedures within parentheses, as well as optional specifications [NOT] INSTANTIABLE and [NOT] FINAL. The specification NOT FINAL means that we can create subtypes for the type. The specification NOT INSTANTIABLE means that no instances of that type can be created, so such types have no constructors. However, they can be used as supertypes from which subtypes can inherit, and those subtypes can be instantiable. Types that have been created as NOT INSTANTIABLE can be changed later by the statement

However, altering a type from INSTANTIABLE to NOT INSTANTIABLE is possible only if the type has not previously been used to create columns, tables, or any other items that reference it.

Note that we do not indicate a primary key constraint or other constraints for the type because we are defining a type, not a table. The attributes can themselves be previously defined UDTs, as we see in the student attribute address, which uses AddressType. We can also write declarations of methods as functions or procedures for the type, indicating the parameters and return types. In Oracle, the code body for the methods is written in PL/SQL, but all the member methods must be coded together, as illustrated in FIGURE 9.10(B) for StudentType1. No code is needed for the built-in methods. Within the code for a member method, the implicit parameter, the calling object, is referred to as self. When invoked, a member method takes an instance of the type as an implicit parameter. Member methods are invoked using dot notation because, like attributes, they are members of the type. For example, if firstStudent is an instance of the StudentType1, we can write in an application program

Using the new StudentType1, we can create a new table, called NewStudent1, which differs slightly from the Student table used in previous chapters, for purposes of illustration. The table definition does not provide a list of attributes, but it states that the table is of the StudentType1 object type as specified in the type definition. This is an example of an object table, where each row is an instance of StudentType1. This object table also contains the embedded column object, address

Any constraints, including primary and foreign keys, are specified when the table is created because constraints apply to the table, not to the type. For the present, we are using the facId of the advisor in the existing Faculty table as a foreign key. We can create as many tables as we wish using the same UDT. Each row of an object table is called a row object.

Although we can insert values in the usual way, it is preferable to use the StudentType1 type constructor to create the row objects, as in

Besides the constructor StudentType1, used here, the new type has the addCredits() and FindAge() methods that we wrote in Figure 9.10(B), as well as the built-in observer and mutator methods for each of its attributes.

Although the user can override observer and mutator methods by redefining them, they cannot be overloaded, unlike the corresponding methods in some OO languages. The observer methods (usually called GET in OO terminology) are called using standard SQL DML commands, with path expressions using dot notation and iterator variables that look like aliases, as illustrated in the following example, which retrieves the ID, street, and age of a student in the NewStudents1 table

The SELECT statement can be used to retrieve entire row objects using the VALUE function, as in

The mutator methods (usually called SET in OO terminology) are invoked by using the SQL INSERT command already illustrated, or using the UPDATE command, again using path expressions and iterator variables, as in

When users define their own data types, the only built-in operations are testing for equality of objects of that type or membership (IN). Most operations that are built in for standard data types are not defined for UDTs. Therefore, users must define operations on the new types they create, building on the basic operations defined for the source types. The definition of a new type includes a list of its attributes and its methods, but the methods are defined only for a single user-defined type and do not apply to other types the user creates. Methods for a type can be classified as member methods or static methods. Member methods have the implicit self parameter, as described earlier, and can be either functions, which have a single return type, or procedures, which have no return but may have input and/or output parameters. We can also create nonmember or static methods, which are defined for the type and which do not have an implicit self parameter.

Attributes of objects can be compared or ordered by using comparison operators as usual or by comparisons of returns of their methods, as in

However, entire row objects cannot be compared, so the following query will cause an error

To provide comparison of objects of the same user-defined type, the user can define an order for the type as a method. In Oracle, users can impose an order by identifying either a map method or an order method for the type. A map method directs Oracle to apply the comparison method it normally uses for one of its built-in types (normally, one of the attributes) as the comparison method for the entire type. For example, if we had defined a member function

in the definition of the type, and then coded this function in the type body to return the value of stuId, as follows

Then Oracle would use its built-in method of comparing VARCHAR2 values applied to the stuId values whenever we compared StudentType objects, because we listed this method as MAP. For example, for the query

the ordering will be by the value of stuId.

Users can instead write their own comparison method, called an order method, to tell Oracle how to compare two objects of a UDT. If we had chosen to write a compareAge() function in the type definition and make it an order method by declaring

we could write code for the function in the type body as follows

Then Oracle would always compare two StudentType1 objects based on age, not stuId. The previous query would return the rows in order by age. A UDT can have at most one map method or order method for the entire type. A subtype can create its own map method if its root supertype has one, but not its own order method.

9.7.4 Reference Types

In strictly relational databases, foreign keys are used to establish relationships between tables. For example, in the relational model, if we assume each student has an advisor who is a faculty member, the corresponding facId is identified as a foreign key when we create the Student table. Object-relational systems use a similar mechanism, allowing attributes of a type that are references or pointers to another type. By default, the value for a reference is the OID of the referenced object, not the value of the primary key. For example, if we define FacultyType2 as an object, we can create a new StudentType2 with a reference, advisor, to FacultyType2 as shown in FIGURE 9.11(A), using Oracle syntax

After we create the Faculty2 table, we can create a new Students2 table. We can specify the scope for the reference attribute, limiting the reference values to the new Faculty2 table instead of permitting them to refer to any FacultyType2 row, regardless of what table it belongs to. The reference attribute, advisor, which is a reference to a FacultyType2, refers to rows in an existing table, Faculty2, as indicated by the scope specification for it. Note that the reference itself specifies the type of object referred to, while the scope specifies the table containing row objects of that type. This limits the allowable values of advisor to OIDs of rows in the Faculty2 table. However, we should also ensure that if one of those rows is deleted, then the advisor value in any Student2 row that points to it is modified, or else we are left with dangling pointers that lead nowhere. This can be done by adding a referential integrity constraint with an ON DELETE option, as shown in Figure 9.11(A).

When we wish to insert a Students2 object, we must use a REF() operator to find the value of the OID of the advisor using a query. If we have already inserted the faculty record for the advisor, as shown in FIGURE 9.11(B), we can then insert a Students2 row with a query to obtain the correct reference for the advisor, as shown in the same figure.

Using REF(f), the value placed in the advisor attribute is the OID of the Faculty2 record. Besides REF(), which returns the REF to a row object, the DEREF() function can be used with references. The DEREF() function takes a REF to an object (normally found in another object table) and returns the row object for that REF. If we write a query on the Students2 table, we can follow the reference from the advisor and get the Faculty2 row for the advisor, as shown in Figure 9.11(B). The query will return the values in the Faculty2 object for F101. In Oracle, we can use dot notation to create a path expression that allows us to follow the reference. For example, the last query shown in Figure 9.11(B) finds a student’s first and last name and the name and department of his or her advisor. Notice that this query returns values from both tables, even though only the Students2 table is named in the FROM clause. This is not a join operation. The result is found by following the REF from Students2 row to the corresponding Faculty2 row.

In a nondistributed, nonreplicated environment, the user can choose the primary key as the OID. The selection is specified in the CREATE TABLE command, as in

For references to objects where the object identifier is the primary key, the scope clause is required.

9.7.5 Type Hierarchies in Standard SQL

Object types can participate in type hierarchies, in which subtypes inherit all attributes and operations of their supertypes, but they may have additional attributes and operations of their own. As discussed in Section 9.7.3, when defining a new type, the user can declare it as FINAL, which means that no subtypes can be defined for it, or NOT FINAL, which allows subtypes to be created if a class is defined as NOT INSTANTIABLE, we cannot create instances of that type, but if it has subtypes, instances of the subtypes can be created, provided the subtypes themselves are instantiable. Using our previously defined type, StudentType2 from Figure 9.11, which was NOT FINAL, we can define the subtype UndergraduateType by writing

UndergraduateType objects inherit all the attributes of StudentType2 objects and have an additional attribute, major. They also inherit any methods defined for StudentType2 but have their own constructor, observer, and mutator methods for major, and they can have their own user-defined methods.

We could continue in this fashion, creating a type called FreshmanType under UndergraduateType.

We could continue to create types for sophomores, juniors, and seniors under UndergraduateType.

For graduate students, we could write a definition for GraduateStudentType under StudentType2, similar to the one for UndergraduateType, this time specifying the graduate program that the student is enrolled in as an attribute. We could also create the subtype TeachingAssistantType under GraduateStudentType. In Figure 9.4 we also had TeachingAssistant as a subtype of Faculty. However, multiple inheritance is not allowed in the SQL standard or Oracle, so we would not be able to make TeachingAssistant a subtype of FacultyType as well. Standard SQL allows tables and subtables to be created for supertypes and subtypes, with the condition that no object can be inserted into both a supertable and its subtable.

9.7.6 Type Hierarchies in Oracle

Oracle supports the creation of types and subtypes exactly as in the SQL standard, but it does not allow subtables to be created under existing tables. Therefore, although we can create subtypes under StudentType2 as we did in the previous section, as shown in FIGURE 9.12(A) and FIGURE 9.12(B), we cannot create subtables for the subtypes under the Students2 table. Instead, all the subtypes can be placed in the Students2 table shown in Figure 9.11(A).

In Oracle, subtypes can be substituted for supertypes as object columns or rows, in object tables or views. This property is known as substitutability. We can therefore insert undergraduate records and freshman records in the Students2 table, which is called a substitutable table, by using the appropriate constructor for the subtype, as shown in FIGURE 9.12(C).

Every row in the substitutable table is, of course, a Students2 tuple, but some belong to other subtypes as well. Every tuple in a substitutable table has a most specific type, which is the closest subtype it belongs to—the subtype whose constructor was used when it was created.

To retrieve data from a substitutable table, we can use the VALUE function, which ordinarily returns the attribute values of tuples, including all subtypes in the result, as shown in FIGURE 9.13(A).

This query will show all the data for all the rows, including the reference values. We can improve the format of the results by specifying the attributes to be displayed.

We can limit the query to the supertype and exclude the subtypes by writing the keyword ONLY, as in FIGURE 9.13(B). This query will find only those students who are not undergraduate or freshmen subtypes—or any other subtype of Students2, if we had created others. It selects only those whose most specific subtype is StudentType2.

To limit the query to undergraduate students, we can specify that the row object instances must be of type UndergraduateType, using IS OF (type) as shown in FIGURE 9.13(C).

The results include all UndergraduateType rows that satisfy the condition, including FreshmanType objects. To eliminate the FreshmanType objects, we could write IS OF (ONLY UndergraduateType). The ONLY clause limits the return to tuples whose most specific type is UndergraduateType.

If we wish to retrieve specific columns, we can list them in the SELECT line, as usual. However, for columns that appear only in a subtype, we cannot simply list the column names in the SELECT line, because the column does not appear in the declaration of the type of the table listed in the FROM line. For example, peerMentor does not appear as a column in the Students2 table, so we cannot write SELECT s.peerMentor FROM Students2 s.... The TREAT() function allows us to treat a Students2 tuple as a FreshmanType tuple in order to examine the peerMentor attribute as shown in FIGURE 9.13(D).

If an Oracle type or subtype has a REF attribute, the DEREF operator can be used as described earlier.

9.7.7 Nested Tables in Oracle

A nested table is created using a single built-in type or object type table, which is then placed in another table. FIGURE 9.14 shows a ContactList table nested inside the Students3 table. For this example, we create ContactType and then create ContactListType that is an object table of ContactType to hold names and telephone numbers of a student’s parents, spouse, guardian, or other contacts, as shown in FIGURE 9.15(A).

We nest the table in a new Students3 table. We define StudentType3 by adding as an attribute contactList of contactListType, as shown in FIGURE 9.15(B). When we create the Students3 table, we specify that it has a nested table, as shown in FIGURE 9.15(C).

Note that we must identify a name for a storage table for the nested table, which we called contactListStorageTable in our example. This is a separate table from Students3. We specify the primary key of the nested table to include nested_table_id, which is a hidden column created by Oracle that associates the rows of this contact table with the corresponding Students3 row. All the rows for contacts of a particular student have the same nested_table_id, so we added lastName and firstName to the primary key to guarantee uniqueness. These are the last and first names of the student’s contacts.

To insert data in the subtable, we first create a row for the owner table, Students3, including a parameterless call to the constructor for the subtable, as shown in FIGURE 9.15(D). The constructor creates an empty instance in the subtable, into which we can then insert data using an INSERT INTO TABLE statement, as shown in FIGURE 9.15(E). We can continue to insert as many contacts as we wish for this student or go on to create other Students3 rows and their contact lists.

Nested tables can be unnested using the TABLE keyword to flatten the table, as shown in FIGURE 9.15(F). The result will be a multiset with a flattened list where student names will be repeated for each of their contacts (one contact per line). If a type or subtype has a nested table, the TABLE keyword can also be used to unnest the table. ORDER BY and GROUP BY clauses can be used for queries on subtypes just as they were for other types.

9.7.8 Oracle Object Views

Oracle allows users of strictly relational Oracle databases to view them as OR ones using a mechanism called an object view. An object view is constructed by defining an object type in which each attribute corresponds to a column of an existing relational table. The object view is created using a SELECT statement, in the same manner that we would create a relational view, except that we must specify an OID, saying which attribute(s) will be used to identify each row of the view. This identifier, which is usually the primary key, can be used to create reference attributes if desired. To guarantee safety of updates in the object view, users can write INSTEAD OF triggers. The object view can then be treated as if it were an object table. If we had a purely relational Faculty3 table, defined as shown in FIGURE 9.16(A), we could define a type, FacultyType, as shown in FIGURE 9.16(B).

Note that we include the keywords AS object as we normally do when we create an object type in Oracle. We could create an object view, Faculty3View, specifying the OID, facId, as shown in FIGURE 9.16(C).

Users can then refer to the attributes of the object view using object notation, write methods for the type, and use other OR features as if it were a user-defined structured type. Application programmers can treat the relational database as if its structure corresponded directly to the objects they create in their OO programs. FIGURE 9.16(D) shows a query on the object view.