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.

Here's a concise explanation and example queries for both types of object tables in Oracle:
Column Object Table Definition: A table that contains one or more columns defined as object types.
Querying: Use a standard SELECT statement. Access attributes of the object using dot notation.
Example Query:

SELECT customer_id,
       address_obj.street,
       address_obj.city
FROM customers 
WHERE address_obj.state = 'CA';

In this example, address_obj is a column of type ADDRESS_TYPE, and we're selecting attributes street and city from it.
Row Object Table Definition: A table where each row itself is an instance of an object type.
Querying: Use standard SQL SELECT statements since each row is an object.
Example Query:

SELECT OBJECT_VALUE.street,
       OBJECT_VALUE.city
FROM address_table;

Here, address_table is an object table where each row is an ADDRESS_TYPE object, and we're selecting street and city attributes from each object.
Key Points:

• Dot Notation: Used to access attributes of objects within columns or rows.
• Object Tables: Can be either column object tables (multiple object types per row) or row object tables (each row is one object).

Remember, these queries assume the ADDRESS_TYPE object type has attributes like street, city, and state. If the structure of your object type differs, adjust the attribute names accordingly in your queries.

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

Name	Null?	Type
BREED		VARCHAR2(25)
NAME		VARCHAR2(25)
BIRTHDATE		DATE

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.