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.

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

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.

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

create table ANIMAL of ANIMAL_TY;
Table created.

describe ANIMAL

[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.