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 







SQL and Encapsulation Violation

Oracle Database Tips by Donald Burleson

Encapsulation is used in non-database object-oriented applications to insure that  all operations are performed through the programmer-defined interface, and that data will never be modified outside of the application shell.  But what about ad-hoc query and update?  It appears that any declarative database language such as SQL, which allows "external" retrieval and update, does not follow the dictates of encapsulation, and is therefore inconsistent with object-oriented database management.

For example, a relational database could be defined to have a behavior called ADD_LINE_ITEM which serves to check inventory levels for an item, and add an item to an order only if sufficient stock is available.  This behavior insures that orders are not entered for out-of-stock items.  With a language such as SQL, the object-oriented behavior could be bypassed, and LINE_ITEM records could be added without any regard for inventory levels. 

Because encapsulation and SQL are incompatible, the only conclusion that can be reached is that encapsulation does not apply to object-oriented databases because declarative languages violate the principle.  In addition we might also conclude that declarative languages cannot be used within a true object-oriented database because all objects must be their methods to gain access.

The most important feature of the relational database is the ability to isolate the data from the data relationships, and to eliminate the "pointers" which were used by hierarchical and network databases to establish relationships.  In a relational database, two tables which have a relationship are defined with a primary key and a foreign key.  This key can be used at run-time to dynamically join the tables. 

SQL was a tremendous benefit to programmers because it removed the requirements of the database that the use of the system "navigate" the data structures.  As we know, SQL is called "declarative" in the sense that the user need only specify the desired data, and the database engine will take care of the navigation.  Hierarchical, Network and object-oriented databases require the programmer to navigate the data  structures to get their selected data.  With SQL the age of end-user access became a reality and users began to use SQL to access their information without programmer intervention.  The term "Declarative", in this context, means that the actual navigation path to the data is hidden from the user, and the user "declares" a solution set which meets their selection criteria.  The SQL optimizer would determine the proper access for the data and handle all of the database navigation. 

Problems with SQL and Objects

There are several constructs within the SQL language which conflict with object-oriented databases.  The most obvious is the requirement that SQL serves as an ad-hoc query facility.

These problems fit into the categories of abstract data typing, encapsulation and methods, and the realm of pointers.  All of these constructs are very foreign to SQL and special extensions have been created to allow for the implementation of these constructs.

SQL and abstract data types

Another problem relates to the SQL "CREATE TABLE" statement.  Object-oriented systems allow the concept of abstract data typing, and the programmer may create their own data types which become indistinguishable  from the system-defined data types.  For example, an object-oriented programmer could define a data type of "BOOLEAN", which would be treated by the system just a CHAR or INTEGER data type.  Relational technology does not have a facility for self-defining new data types, but the new object/relational databases do allow for data items to be created and used with the CREATE TYPE constructs which were discussed in earlier chapters.

Of course, there are also SQL extensions to allow for the creation and implementation of these abstract data types.  The following SQL is used to create a customer table.  Note that two of the components, full_name and full_address are abstract data types, and the entire customer table has been encapsulated into an ADT called customer_stuff;


   full_address (
        street_address    varchar(20),
        city_name         varchar(20),
        state_name        char(2),
        zip_code          number(9));


    full_name (
        first_name        varchar(20),
        MI                char(1),
        last_name         varchar(30));


     customer_stuff (
        customer_ID       number(6),
        cust_full_name    full_name,
        cust_full_address full_address));

CREATE TABLE CUSTOMER of  customer_stuff;

Once the ADTs have been defined, SQL has been extended to allow for the use of sub-typing to address those data components that are nested within larger data types.  For example, we could use the following SQL to select the zip code for a particular customer:

customer_ID = 764645;

While dealing with ADTs is a relatively trivial extension to SQL there are many other new constructs that need to be addressed.




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.