Oracle Abstract Data Types Donald K. Burleson

After nearly five years of development, Oracle9i has completed their support for object-orientation at the database level.  In Oracle9i we see full support for many advanced OO features:

• Inheritance

• Type evolution

• Dynamic method dispatch

• Multilevel collection support

This builds upon OO features that were introduced starting in 1988 with the advent of Oracle8.  The early OO features included:

• nested tables

• abstract data types

• 0NF tables (tables with repeating groups)

• Methods (procedures tightly-coupled with objects)

• Pointer navigation between objects)

This topic is so complex, entire books have been written on the subject. For details on OO for relational databases, see my book “Inside the Database Object Model” by CRC Press.

To get the flavor or OO for Oracle, let’s examine the creation of an abstract data type.  Abstract data types are the most commonly-used OO features within Oracle because they allow uniform data definitions within a schema.

Please note the use of the CREATE TYPE syntax, and the new SQL constructs that are used to support abstract data types:

create or replace type

   full_address_type

as object

(

   street  varchar2(80),

   city    varchar2(80),

   state    char(2),

   zip   varchar2(10)

);

/

create table

customer

(

full_name   varchar2(50),

full_address   full_address_type

);

insert into

customer

values

(

'Don Burleson',

full_address_type('123 1st st','Minoy','ND','74635'));

If you like Oracle tuning, check out my latest book "Oracle Tuning: The Definitive Reference".  It's 980 pages of hard-core tuning insights, tips and scripts, and you can buy it direct from the publisher for 30%-off. Best of all, you get instant access to the code depot of Oracle tuning scripts.