appendix-d

Table of Contents

Appendix E
What 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:

   Calculating a GPA

   Calculating the financial aid for which a student qualifies

   Calculating interest on a student s unpaid tuition and housing costs

This list could go on forever. But, at this point, we have enough information to present Listing E.1, which shows what a complex object declaration for a student might look like in Oracle8.

Listing E.1 A complex object declaration in Oracle8.

 CREATE TYPE Address_TYPE AS object (street_name_or_route   varchar2 (40),  house_or_box_number    integer,  city_name              varchar2 (30),  state_code             varchar2  (2),  zip_code               integer); CREATE TYPE Degree_TYPE AS object (department_name        varchar2 (30),  degree_level           integer,  degree_name            varchar2 (30),  degree_field           varchar2 (40)); CREATE TYPE Housing_TYPE AS object (dormitory_name         varchar2 (30),  occupancy_code         varchar2  (1),  room_cost              number (6,2)); CREATE TYPE Student_TYPE AS object (first_name             varchar2 (20),  middle_name            varchar2 (20),  last_name              varchar2 (30),  ssn                    varchar2  (9),  address                Address_TYPE,  application_date       date,  acceptance_date        date,  gpa                    number (3,2),  degree_plan            Degree_TYPE,  father_name            varchar2 (60),  mother_name            varchar2 (60),  mother_maiden_name     varchar2 (20),  account_number         integer,  housing_assignment     Housing_TYPE,  MEMBER FUNCTION Get_SSN                    RETURN varchar2,  MEMBER FUNCTION Calculate_GPA              RETURN number,  MEMBER FUNCTION Award_Financial_Aid        RETURN number,  MEMBER FUNCTION Calculate_Account_Interest RETURN number); 

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:

 INSERT INTO   STUDENTS VALUES ('John',         'Joseph',         'Doe',         '999999999',         address('North Main Street',                 23,                 'Philadelphia',                 'PA',                 '45032'),         SYSDATE,         SYSDATE,         NULL,         degree_plan(),         'William Robinson Doe',         'Jane Elizabeth Doe',         'Martin',         ACCOUNT_SEQ.NEXTVAL,         housing_assignment('Elam Arms',                            'S',                            795.43)); 

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:

 CREATE TYPE Courses_Array_TYPE as varray (9) OF number; CREATE TABLE STUDENTS (first_name             varchar2 (20),  middle_name            varchar2 (20),  last_name              varchar2 (20),  ssn                    varchar2  (9),  address                Address_TYPE,  application_date       date,  acceptance_date        date,  gpa                    number (3,2),  degree_plan            Degree_TYPE,  father_name            varchar2 (60),  mother_name            varchar2 (60),  mother_maiden_name     varchar2 (20),  account_number         integer,  housing_assignment     Housing_TYPE,  courses                Courses_Array_TYPE); 

This defines a row in the STUDENTS table that stores course information. Each student could have as many as nine courses, all contained within a single row in the STUDENTS table.

The Large Object Datatypes

Oracle8 implements four new large object datatypes:

    bfile ”Used to point to a binary file stored in the host operating system s file system.

    blob ”Used to store unstructured binary data.

    clob ”Used to store single-byte character data.

    nclob ”Used to store multi-byte character data.

Each of these datatypes is intended for a specific use, but there are certain characteristics that these datatypes all have in common:

   Columns of these types may be up to four gigabytes in length.

   Tables may contain one or more columns of each of these types.

For instance, the following is a valid table definition in Oracle8:

 employee_num      number first_name        varchar2 (20) middle_name       varchar2 (20) last_name         varchar2 (20) photo             bfile resume            bfile 

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:

 CREATE TABLE STUDENTS (student           Student_TYPE,  address_list      ADDRESSES_TABLE) 

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.

Table of Contents