Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

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

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.