Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Oracle Table Design with object ID's

Oracle Tips by Burleson 

Chapter 6 Oracle Table Design

CREATE TABLE CUSTOMER (customer_data   customer_stuff);

Nesting abstract data types in this way allows the duplication of the object-oriented concept of encapsulation. In other words, groups of related data types are placed into a container that is completely self-contained while retaining the full capability of the innate relational data types, such as INT and CHAR.

Nested abstract data types are displayed in the same fashion as the sample queries seen earlier, except that the target data types require several dots to delimit the levels of nesting within the data structure. For example, the query below  displays the street_address for a customer:

   customer_stuff.customer_name.zip_code like ‘144%’;

The above reference to zip_code must be preceded with customer_name since it is nested in this data type.In turn, the customer_name data type is nested within the customer_stuff data type. The proper SQL reference to zip_code is therefore expressed as follows:


With this general understanding of how abstract data types function and operate within Oracle9i, we are ready to see how “pointers” are used within Oracle to establish relationships between table rows.

Design with Oracle Object IDs

It is necessary to understand exactly what object IDs represent in Oracle 9i and how they are implemented before we discuss navigating the database with pointers. Each record in a database had a unique database address in pre-relational databases. The addresses corresponded to a physical database block, similar to Oracle ROWIDS. The offset or displacement of the target record into the block was included in the address. For example, an address of 552:3 referred to the third record in  block number 552 of the database. These addresses could be stored inside other records once defined. This method allowed one record to point to another record. In pre-relational databases, these pointers were the foundation of relationships between entities.

Modern object databases store objects in an object table with an assigned object ID, commonly called an OID. An OID is guaranteed to be globally unique. Each OID consists of a 128-byte hexadecimal value. An OID cannot be used to locate an object instance by itself. Only a REF (or reference, discussed later in this chapter) containing location data can be used to locate an object instance.

The concept of the ROWID, introduced in Oracle7, is also used in Oracle 9i to uniquely identify each row in each table in a database. The ROWID in Oracle7 is a VARCHAR2 representation of a binary value shown in hexadecimal format. It is displayed as


*              bbbbbbbb is the block ID
*              ssss is the sequence in the block
*              ffff is the file ID

The ROWID can be used to detect and eliminate duplicate rows in instances where a primary key for a table allows duplicate values. ROWIDs for duplicate customers are displayed in the following SQL:

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks