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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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:

SELECT DISTINCT full_address FROM CUSTOMER;

This SQL would produce a listing like this:

STREET_ADDRESS               CITY_NAME STATE_ABBR            ZIP_CODE 

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
 WHERE
 full_address.zip_code LIKE '144%';

This would produce the following listing:

STREET_ADDRESS

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:

UPDATE CUSTOMER
      (full_address
         (
         VALUES (
                        '444 North avenue',
                        'West Lake'
                        'NJ'
                        83733
                        )
          )
      );

 

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

customer_stuff.customer_name.zip_code

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.