Lesson 8	Querying an object table in Oracle
Objective	Write SQL to query object tables (technique 2)

Querying an Oracle Object Table

If an object is defined within another object, you can query such an object by using standard SQL and dot notation.
You can drill down to the simple data type with dot notation. Let us look at the following diagram that queries CUSTOMER_OBJ_TABLE, which is associated with the ADDRESS_TYPE object. To query based on a particular state within the address, you have to drill down to the STATE attribute within the ADDRESS_TYPE object by using dot notation, as described in the following MouseOver:
The following diagram containing SQL illustrates an example of querying an object table where one object is associated with another object.

Oracle Query Object — The SELECT clause for selecting attributes from the object table

The FROM clause for specifying the name of the object table

The WHERE clause, which drills down to the attribute of the associated object

SELECT column_name_1, <column_name_2> FROM table_name alias WHERE alias.object_name.attribute_name= variable;

Alternate Query Example

Build SQL Statement

The following link describes how to build an sql statement in Oracle.

Build SQL Statements to Detect Dangling References

Apply two techniques for querying an object.
After completing the required entry fields to connect to the database, the following steps must be completed:
Build the SQL statement with the following code:

First SELECT Statement

SELECT product_name, store_cost, sale_price
FROM product_obj_table;

Apply an alternative technique for querying an object, building the following code:

Second SELECT Statement

SELECT first_name, last_name
FROM customer_obj_table cot
WHERE cot.full_address.state = 'NE';

After this process is completed, SQL*Plus displays the result of the compilation of your query.

You will need to be able to remove data without worrying that there are dangling references that will invalidate referential integrity along the way. By using reference partitions, you are unifying the ways in which the data is divided across multiple tables, and that significantly eases the maintenance burden

Assigning Values to Variables with the SELECT INTO Statement

A simple form of the SELECT INTO statement is:

SELECT select_item [, select_item ]...
INTO variable_name [, variable_name ]...
FROM table_name;

For each select_item, there must be a corresponding, type-compatible variable_name. Because SQL does not have a BOOLEAN type, variable_name cannot be a BOOLEAN variable.

Example 2-8 Assigning Value to Variable with SELECT INTO Statement

This example uses a SELECT INTO statement to assign to the variable bonus the value that is 10% of the salary of the employee whose employee_id is 100.

DECLARE
bonus NUMBER(8,2);
BEGIN
SELECT salary * 0.10 INTO bonus
FROM employees
WHERE employee_id = 100;
END;
DBMS_OUTPUT.PUT_LINE('bonus = ' || TO_CHAR(bonus));
/

Result:
bonus = 2400
In the next lesson, this module will be concluded
Object Oriented Databases