Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Benefits of User-Defined Data Types

Oracle Database Tips by Donald Burleson

It is interesting to note that the ability to represent user-defined data types was commonly used within pre-relational database, and was lost when the relational model was introduced.  In pre-relational databases, there were only a small number of allowable data types (numeric and character), but these databases allowed for the atomic values to be grouped into larger units.  These larger units could then be easily moved around within the database.  For example, a full_address construct could be defined and copied into numerous record definitions, where it could be manipulated as if it were a single unit.

There are several reasons why ADT's are useful within an object database:

  1. Encapsulation - Because each user-defined data type exist as a complete entity, including the data definitions, default values, and value constraints, this entity insures uniformity and consistency.  Once defined, a user-defined data type may participate in many other user-defined data types, such that the same logical data type, always has the same definition, default values and value constraints, regardless of where it appears in the database.

  2. Reusability - As a hierarchy of common data structures are assembled, these can be re-used within many definitions, saving coding time and insuring uniformity.

  3. Flexibility - The ability to create real-world data representations of data allows the database object designer to model the real world as it exists.

As you can see there are many compelling reasons to have user-defined data typing, provided that the data types are properly analyzed and incorporated into the database object model.  Let's take a look at some of the implementation issues that relate to the object model and data typing.

Nesting of user-defined data types

Now lets take this concept one step further and consider how  user-defined data types can be nested within other data types.  A basic example, would be to create a data type that would encapsulate  all of the data in a table:

 CREATE TYPE    customer_stuff (
    full_name           customer_name,
    home_address     customer_address
    business_address customer_address);

 With the customer_stuff type defined, table definition becomes simple:

 CREATE TABLE CUSTOMER (customer_data           customer_stuff);

Using this type of user-defined data type we are essentially duplicating the object-oriented concept of encapsulation.  That is, we are placing groups of related data types into a container that is completely self-contained and has the full authority of the innate relational data types such as int and char.

Select customer_stuff.customer_name.zip_code
from customer
where customer_stuff.customer_name.zip_code like '144%';

Manipulating ADT's

In many object databases it is possible to directly retrieve and pass an abstract data type.  While this is a matter of convenience for most programmers, there are some very useful applications of this technique, especially for the object/relational databases.  In object/relational databases, ADT's can be directly retrieved and used inside other tables.  In the following Oracle example, a new table called good_customer is created by retrieving the entire ADT from the customer table.

create table  good_customer as
(select value(a) from customer a where type = 'good')

In this example, the good_customer table will inherit the same ADT as the customer table.

Another feature of abstract data typing is the simplification of the transfer of data structures between the database and the front-ends.  In a traditional relational database, each column has to be moved, one at a time, from the database into the map field for the display screen.  In the following example, we see the C syntax to move data onto a display screen:



When using an ADT, a single statement can be used to transfer the data from the database onto the display screen:

strcpy(db_customer_struct, screen_customer_struct);


Now that we have a general understanding of the function and operation of abstract data types within the database object model, we can now move to on to more advanced topics.  In the following chapters we will introduce pointers to objects, the creation of aggregate objects, and the use of inheritance within the database object model.  It is the synergy of all of these features that makes the object-oriented and object/relational database a powerful new addition to the arsenal of systems development tools.




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.