Object Tables   «Prev  Next»
Lesson 8Querying an object table in Oracle
ObjectiveWrite 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's look at the following MouseOver 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 Tooltip illustrates an example of querying an object table where one object is associated with another object:

  1. The SELECT clause for selecting attributes from the object table
  2. The FROM clause for specifying the name of the object table
  3. 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 Statement


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