Home arrow Oracle arrow Page 8 - Developing and Implementing Applications, continued

Using Object Views - Oracle

This article, the second of three parts, focuses on the design and creation of applications that use the database. It is excerpted from chapter five of the book Oracle Database 10g DBA Handbook, written by Kevin Loney and Bob Bryla (McGraw-Hill/Osborne, 2005; ISBN: 0072231459).

  1. Developing and Implementing Applications, continued
  2. Switching Consumer Groups
  3. Editing Stored Outlines
  4. The Golden Rule for Space Calculations
  5. Sizing the Objects
  6. Estimating Space Requirements for Indexes
  7. Sizing Tables That Contain Large Objects (LOBs)
  8. Using Object Views
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 5
February 16, 2006

print this article



The use of abstract datatypes may increase the complexity of your development environment. When you query the attributes of an abstract datatype, you must use a syntax that is not used against tables that do not contain abstract datatypes. If you do not implement abstract datatypes in all your tables, you will need to use one syntax for some of your tables and a separate syntax for other tables—and you will need to know ahead of time which queries use abstract datatypes.

For example, the CUSTOMER table uses the ADDRESS_TY datatype described in the previous section:

create table CUSTOMER
(Name    VARCHAR2(25),
Address  ADDRESS_TY);

The ADDRESS_TY datatype, in turn, has four attributes: Street, City, State, and Zip. If you want to select the Street attribute value from the Address column of the CUSTOMER table, you may write the following query:

select Address.Street from CUSTOMER;

However, this query will not work. When you query the attributes of abstract datatypes, you must use correlation variables for the table names. Otherwise, there may be an ambiguity regarding the object being selected. To query the Street attribute, use a correlation variable (in this case, “C”) for the CUSTOMER table, as shown in the following example:

select C.Address.Street from CUSTOMER  C;

As shown in this example, you need to use correlation variables for queries of abstract datatype attributes even if the query only accesses one table. There are therefore two features of queries against abstract datatype attributes: the notation used to access the attributes and the correlation variables requirement. In order to implement abstract datatypes consistently, you may need to alter your SQL standards to support 100-percent usage of correlation variables. Even if you use correlation variables consistently, the notation required to access attribute values may cause problems as well, because you cannot use a similar notation on tables that do not use abstract datatypes.

Object views provide an effective compromise solution to this inconsistency. The CUSTOMER table created in the previous examples assumes that an ADDRESS_TY datatype already exists. But what if your tables already exist? What if you had previously created a relational database application and are trying to implement object-relational concepts in your application without rebuilding and re-creating the entire application? What you would need is the ability to overlay object-oriented (OO) structures such as abstract datatypes on existing relational tables. Oracle provides object views as a means for defining objects used by existing relational tables.

If the CUSTOMER table already exists, you could create the ADDRESS_TY datatype and use object views to relate it to the CUSTOMER table. In the following listing, the CUSTOMER table is created as a relational table, using only the normally provided datatypes:

create table CUSTOMER
(Name        VARCHAR2(25) primary key, Street       VARCHAR2(50),
City         VARCHAR2(25),
State        CHAR(2),
Zip          NUMBER);

If you want to create another table or application that stores information about people and addresses, you may choose to create the ADDRESS_TY datatype. However, for consistency, that datatype should be applied to the CUSTOMER table as well. The following examples will use the ADDRESS_TY datatype created in the previous section.

You can create an object view based on the CUSTOMER table, using any datatype you have defined. To create an object view, use the create view command. Within the create view command, specify the query that will form the basis of the view. The code for creating the CUSTOMER_OV object view is shown in the following listing:

create view CUSTOMER_OV (Name, Address) as select Name,
       ADDRESS_TY(Street, City, State, Zip)
  from CUSTOMER;

The CUSTOMER_OV view will have two columns: the Name and the Address columns (the latter is defined by the ADDRESS_TY datatype). Note that you cannot specify object as an option within the create view command.

Several important syntax issues are presented in this example. When a table is built on existing abstract datatypes, you select column values from the table by referring to the names of the columns (such as Name) instead of their constructor methods. When creating the object view, however, you refer to the names of the constructor methods (such as ADDRESS_TY) instead. Also, you can use where clauses in the query that forms the basis of the object view. You can therefore limit the rows that are accessible via the object view.

If you use object views, you as the DBA will administer relational tables the same way as you did before. You will still need to manage the privileges for the datatypes (see the following section of this chapter for information on security management of abstract datatypes), but the table and index structures will be the same as they were before the creation of the abstract datatypes. Using the relational structures will simplify your administration tasks while allowing developers to access objects via the object views of the tables.

You can also use object views to simulate the references used by row objects. Row objects are rows within an object table. To create an object view that supports row objects, you need to first create a datatype that has the same structure as the table, as shown here:

create or replace type CUSTOMER_TY as object
(Name        VARCHAR2(25),
 Street       VARCHAR2(50),
 City         VARCHAR2(25),
 State        CHAR(2),
 Zip          NUMBER);

Next, create an object view based on the CUSTOMER_TY type while assigning OID (object identifier) values to the records in CUSTOMER:

  with object identifier (Name) as
select Name, Street, City, State, Zip
  from CUSTOMER;

The first part of this create view command gives the view its name (CUSTOMER_OV) and tells Oracle that the view’s structure is based on the CUSTOMER_TY datatype. An object identifier, also known as an OID,identifies the row object. In this object view, the Name column will be used as the OID.

If you have a second table that references CUSTOMER via a foreign key/primary key relationship, you can set up an object view that contains references to CUSTOMER_OV. For example, the CUSTOMER_CALL table contains a foreign key to the CUSTOMER table, as shown here:

create table CUSTOMER_CALL
(Name           VARCHAR2(25),
 Call_Number    NUMBER,
 Call_Date      DATE,
 constraint CUSTOMER_CALL_PK
primary key (Name, Call_Number), 
 constraint CUSTOMER_CALL_FK foreign key (Name)
    references CUSTOMER(Name));

The Name column of CUSTOMER_CALL references the same column in the CUSTOMER table. Because you have simulated OIDs (called pkOIDs) based on the primary key of CUSTOMER, you need to create references to those OIDs. Oracle provides an operator called MAKE_REF that creates the references (called pkREFs). In the following listing, the MAKE_REF operator is used to create references from the object view of CUSTOMER_CALL to the object view of CUSTOMER:

create view CUSTOMER_CALL_OV as
select MAKE_REF(CUSTOMER_OV, Name) Name,

Within the CUSTOMER_CALL_OV view, you tell Oracle the name of the view to reference and the columns that constitute the pkREF. You could now query CUSTOMER_OV data from within CUSTOMER_CALL_OV by using the DEREF operator on the Customer_ID column:

select DEREF(CCOV.Name)
 where Call_Date = TRUNC(SysDate);

You can thus return CUSTOMER data from your query without directly querying the CUSTOMER table. In this example, the Call_Date column is used as a limiting condition for the rows returned by the query.

Whether you use row objects or column objects, you can use object views to shield your tables from the object relationships. The tables are not modified; you administer them the way you always did. The difference is that the users can now access the rows of CUSTOMER as if they are row objects.

From a DBA perspective, object views allow you to continue creating and supporting standard tables and indexes while the application developers implement the advanced object-relational features as a layer above those tables. 

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: