RelationalDBDesignRelationalDBDesign


Object Tables   «Prev  Next»
Lesson 4 Oracle Object tables
Objective Query the two types of object tables.

Querying Object Tables in Oracle

Querying an object table is similar to using a relational query. This is because the objects stored within the table are persistent in nature. An object type can be defined as a data type in one or more tables. For example, the object type ADDRESS_TYPE can be associated with the customer, where it stores the customer's home address. It can also be associated with a sale, where the billing and shipping address is maintained.
There are two types of object tables:
  1. column object table
  2. and row object table.

Column object table

If a table is based on multiple objects or a combination of relational columns and objects, the column is said to contain column objects. Different columns can be of different object types. To query such a table, you can retrieve the data by using a standard SELECT statement.
To retrieve the value of the object's attributes, use dot notation[1].
The following query shows an example of retrieving data from an object table using dot notation:

SELECT column_name, 
alias.object_name.object_column_name 
FROM table_name alias
WHERE  
alias.object_name.object_column_name = <variable>;

Row object table

A table based on a single object serves as a data type of each of the rows within that table. Such a table is referred to as an object table, and it contains row objects. In other words, each row is an object instance.
Row object tables can be queried by using standard SQL. In the next lesson, collections and variable sized arrays will be discussed.

PL/SQL Programming

Object Tables and OIDs

In an object table, each row is a row object. An object table differs from a normal relational table in several ways. First, each row within the object table has an OID, which is an object identifier value assigned by Oracle when the row is created. Second, the rows of an object table can be referenced by other objects within the database. You can create an object table via the create table command. Consider the ANIMAL_TY datatype shown in previous chapters. To avoid conflicts with objects created during earlier chapters, create this type in a new schema:

create or replace type ANIMAL_TY as object
(Breed VARCHAR2(25),
Name VARCHAR2(25),
BirthDate DATE);

Note: To keep this example simple, the ANIMAL_TY datatype is created without any member functions.
To create an object table of the ANIMAL_TY datatype, issue the following create table command:
create table ANIMAL of ANIMAL_TY;
Table created.
Note that the command has an unusual syntax which is creating the table of an object type. The resulting table may first appear to be a normal relational table:
describe ANIMAL

ANIMAL table

The ANIMAL table's columns map to the attributes of the ANIMAL_TY object type. However, there are significant differences in how you can use the table, since it is an object table.
Each row within the object table will have an OID value, and the rows can be referenced as objects.
[1] Dot notation: To reference individual fields within the record, use the dot notation. A dot (.) serves as the selector for the individual field name.