Appendix EWhats New In Oracle8?

As of this writing, Oracle8 is finally in beta and due to be released any day. Chances are that Oracle8 will have been released by the time you read this text. Hopefully, the information contained in this appendix will give you a step up on understanding the drastic changes accompanying Oracle8. Youve almost certainly heard by now that Oracle8 supports the object concept, but there are other important changes, as well.

Any rumors youve heard about people seeing this information with glazed eyes are entirely true. In some ways, the changes between Oracle7 and Oracle8 are more radical than the changes that occurred between Oracle6 and Oracle7. This appendix outlines some of the more striking changes implemented in Oracle8.

New Datatypes

Oracle8 introduces several new datatypes. Some of these datatypes are used to support the object-relational database model. In this model, data structures are based on object-oriented techniques for representing the real world. Relational operations are still usable in this model, meaning that all Oracle7 databases are forward compatible with Oracle8.

Several other new datatypes serve a variety of purposes, including:

varrays support the use of multivalue columns.

 Large object (LOB) datatypes are used to support the storage of objects up to four gigabytes in size.

 Nested tables are tables that appear as columns in other tables.

Obviously, these are radical new types of constructs for an Oracle database. Well discuss each new type individually. In addition to these new datatype definitions, the concept of a ROWID has been altered, as well.

The object Datatype

In Oracle8, an object is a self-contained construction that holds information about real-world data and the operations that can be performed on the data. Information about the data is stored in attributes, while the operations that can be performed on the data are methods.

An attribute is simply a declaration of an element of data using one of Oracle8s recognized datatypes (include objects, varray s, and large object datatypes). A method is a code module that operates against one or more attributes of the object.

For instance, lets say youre trying to represent a student as an object. There are certain characteristics common to every student. Some of these characteristics might be as follows:

 First, middle, and last names

 Social security number

 Address and phone number

 Application date and acceptance date

 Graduation date

 GPA

 Degree plan or course of study

 Parents

 Amount of tuition and fees owed

 Dormitory assignment

Obviously, this isnt a complete list, but it will do for the sake of this example. All of these elements would be attributes of a student object. Looking at the preceding list, its likely that some attributes can be broken down further, like the address. An address has the following components:

 Street name or route

 Box number

 City

 State

 Zip code

Each individual student object will also have certain operations that have to be performed, such as:

Storing this information in a straight relational model would require the definition of several tables. Now, all thats needed is a single STUDENTS table with the following definition:

CREATE TABLE STUDENTS OF Student_TYPE;

Every row created in the STUDENTS table is an object of the type Student_TYPE and has all of the attributes and methods in Listing E.1. The INSERT statement that creates a new student would look like this:

Every attribute of the defined type can be given constraints (NOT NULL constraints, DEFAULT value constraints, and so forth). These attributes can also be indexed like a column in a table.

The PL/SQL functions Get_SSN(), Calculate_GPA(), Award_Financial_Aid(), and Calculate_Account_Interest() are all methods in this example. Keep in mind that a method isnt restricted to being a PL/SQL procedure or function. Methods can also be calls to a library of objects stored in the database and written in a language like C or C++. The functionality of these methods is defined (or referenced) in the type body.

The varray Datatype

The varray datatype enables you to create structures that are essentially arrays, without having to deal with the crude limitations of PL/SQL tables. Like object datatypes, a varray is a datatype declaration, not a variable declaration. A declaration of a varray might look like this:

CREATE TYPE Name_Array_TYPE AS varray (10) OF varchar2 (60)

You must declare a maximum size for the datatype when it is declared. As with a PL/SQL table, referencing a NULL or nonexistent element of a varray variable causes a NO_DATA_FOUND exception to be raised. Elements in the variable are referenced like the elements of a PL/SQL table. For instance:

Names_array (7)

Using a varray type, you could easily implement a multivalue column in a single row of a table. For example:

This tableEMPLOYEEScontains two columns with a datatype of bfile. These files are not stored within the database but reside on the file system at the OS level. For instance, the resume column might always point to an employees resume that is continually updated in Microsoft Word.

Values of these new datatypes are always populated with a LOB locator, which is comparable to a pointer. Actual data is not stored in the table but at another location inside the database. In the case of data with type bfile, the actual data is a file maintained outside of Oracle by the host operating system.

With the exception of the nclob datatype, you may also include multiples of these datatypes in object types that you create. To manipulate objects of the LOB datatypes, the DBMS_LOB package has been provided.

The Nested Table Datatype

A nested table is actually a table that is a column in another table. Consider again the sample student object presented earlier in this appendix. Instead of defining the datatype Address_TYPE, address information could have been stored in the STUDENTS table like this:

Using this table definition, a single student could have multiple addresses. As with the varray datatypes, this datatype could be used to implement a multivalue column in a row of data. The nested table method has the advantage of not requiring a maximum number of values for the column.

The New ROWID

Oracle8 has a new pseudocolumn called an extended ROWID. This pseudocolumn contains all the elements of the ROWID from Oracle7, plus a data object number that uniquely identifies a particular segment. An Oracle7 ROWID is now referred to as a restricted ROWID.

The DBMS_ROWID Package

Oracle8 introduces the DBMS_ROWID package, which contains functions that operate on the ROWID and extended ROWID pseudocolumns. Among the functions provided in this package are functions that can convert ROWID values between the restricted and extended formats.

Changes To SQL*Plus And PL/SQL

Obviously, such drastic changes to the supported datatypes require changes to SQL*Plus and PL/SQL to support the new datatypes. The following sections take a brief look as some of the changes made to SQL*Plus and PL/SQL.

SQL Changes

In addition to allowing columns of the new datatypes to be defined, Oracle has added several commands to be used with the new datatypes:

ALTER TYPE

CREATE DIRECTORY

CREATE LIBRARY

CREATE TYPE

CREATE TYPE BODY

DROP TYPE

DROP TYPE BODY

In addition to these new commands, Oracle8 offers some new built-in functions and object views. While a complete discussion of the new commands isnt necessary, you may want to know a little more about some new built-in functions and a new type of object called an object view.

New Built-In Functions

Oracle8 has added several new built-in trigonometric functions. These functions are listed in Table E.1.

Table E.1 New trigonometric SQL functions.

Function

Purpose

acos()

Returns the arc cosine of a parameter, n, in radians.

asin()

Returns the arc sine of a parameter, n, in radians.

atan()

Returns the arc tangent of a parameter, n, in radians.

atan2()

Returns the arc tangent of two parameters, x and y, in radians.

Object Views

An object view is a construct implemented in Oracle8 to ease the migration from the relational database to the object-relational database. Object views allow you to query and manipulate relational data as if the data were object data.

PL/SQL Changes

PL/SQL fully supports all the new Oracle8 datatypes. For instance, you may declare object and varray types in your functions, procedures, and packages just like variables of any other datatype.

Summary

This appendix hasnt covered everything you need to know about Oracle8, nor could it do so without taking an enormous amount of space. Instead, this appendix provides an overview of some of the new concepts contained in Oracle8. Just like Oracle7, Oracle8 promises to revolutionize the way databases are created using Oracle.