Donald K. Burleson Oracle Tips

Using abstract data types in Oracle

One of the shortcomings of Oracle7 databases was the inability to model grouped data columns. For example, if we want to select all of the address information for a customer, we are required to select and manipulate street_address, city_address, and zip_code as three separate column statements in our SQL. With abstract data typing, we can create a new datatype called full_mailing_address_type, and manipulate it as if it were an atomic datatype.

Fortunately, Oracle8 allows us to do the same type of grouping with their new create type syntax.

CREATE OR REPLACE TYPE full_mailing_address_type AS OBJECT
( Street       VARCHAR2(80),
  City         VARCHAR2(80),
  State        CHAR(2),
  Zip          VARCHAR2(10) );

Once defined, we can treat full_mailing_address_type as a valid datatype and use it to create tables.

CREATE TABLE
   customer
   (
     full_name                  full_name_type,
     full_address               full_mailing_address_type,
   );

Now that the Oracle table is defined, we can reference full_mailing_address_type in our SQL just as if it were a primitive datatype:

insert into
   customer
values (
    full_name_type('ANDREW','S.','BURLESON’),
    full_mailing_address_type('123 1st st','Minot’,’ND','74635');

Next, let’s select from this table. Below we see a very different output than from an ordinary select statement.

SQL> select * from customer;

FULL_NAME(FIRST_NAME, MI, LAST_NAME)
----------------------------------------
FULL_ADDRESS(STREET, CITY, STATE, ZIP)
--------------------------------------------------------------------------------
FULL_NAME_TYPE(‘Andrew’, ‘S’, ‘Burleson’)
FULL_MAILING_ADDRESS_TYPE('123 1st st', 'Minot', 'ND', '74635')

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm