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 







Displaying and Updating Abstract Data Types with SQL

Oracle Database Tips by Donald Burleson

Displaying abstract data types with SQL

But there is to abstract data types than the ability to define them within a relational table.  Another useful feature of these data types is to be able to reference then from within an SQL query.  Since data types are normally comprised of sub-parts, the SQL must be extended to allow for the entire data type to be referenced as a single unit, while the SQL will automatically format all of the sub-components.  For example, the following SQL would display full_address data without the need to select each sub-type:


This SQL would produce a listing like this:


123 first street                           Minot               ND                              77363
44 west avenue                         Albuquerque    NM                              87112
8337 glenwood drive                Fairport            NY                              14450
3 wedgewood avenue               Denver             CO                              63533

Note that we would also have to alter our SQL if we wanted to select a component of full_address.  This is generally done by specifying the sub-component by inserting a "dot" between the higher-level data type and the data item that we wish to display:

 SELECT full_address.street_address
 full_address.zip_code LIKE '144%';

This would produce the following listing:


8337 glenwood drive

Now that we understand how ANSI standard SQL has been extended for selecting rows that contain abstract data types, let's move on to look at how abstract data types are updated with SQL.

Updating abstract data types with SQL

Since user-defined data types contain sub-entities, special extensions will need to be added to relational SQL to allow for these aggregate data types to be updated.  This would involve partitioning the SQL UPDATE statement to allow for the sub-types to be specified.  For example, the SQL to update a customer address might look like this:

         VALUES (
                        '444 North avenue',
                        'West Lake'


As we see from this example, the update statement is referencing only the full_address data type, but since it consists of sub-types, we must specify each of the sub-types separately in the update statement.

Nesting of abstract data types

Now lets take this concept one step further and consider now  abstract data types can be nested within other data types.  Remember, the primary reason for the introduction of user-defined data types is the ability to reuse these components in a consistent fashion across the entire database domain.  Since data types are created to encapsulate other data types we should be able to "nest" or embed user-defined data types within other user-defined data types.  For example, we could create a data type that would encapsulate  all of the data in a table, and then use this data type in a table definition:

 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);

By using this type of user-defined data typing 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.

Displaying nested user-defined data types would be performed in the same fashion as the earlier sample queries with the exception that the target data types would require several "dots" to delimit the levels of nesting within the data structure.  For example, the following query will display the street_address for a customer.

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

Here we see that a reference to zip_code must be prefaced with customer_name since it participates in this data types.  Further, the customer_name data type is nested within the customer_stuff data types.  Hence, the proper SQL reference to zip_code is expressed as


Now, let's move on to take a look at how data type usage is managed.




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.