Implementing Object Techniques

Object Technology:
• Object Technology is another step forward in the software enginnering life cycle that allows us to:
- Model real Thing
- Represent real thing as objects
• Object Technology is a method of managing complexity so that we can develop solutions that simulate real-world problems.
• Minimizing cost through reusibility

Oracle provides full support for all three different implementation – Relational, Object- Relational, Object- Oriented Relational.

Why Should Objects Be Used?

Objects reduce complexity by giving an intuitive way of representing complex data and its relations.

Besides simplifying the interaction with data, objects may help in other ways. Examples of benefits that come from using OO features are:

Object reuse: - If OO code is written; the chance of reusing previously written code modules is increased. Similarly, if OO database objects are created the chances that these database objects will be reused in increased.

Standard adherence:- If database objects are built using standards, then the chances they will be used increase exponentially. If multiple applications or tables use the same set of database objects, then a de facto standard for application of tables is being created.


Oracle supports many different types of objects. In the following sections, major object types are described.

Abstract Data type

Abstract Data type is a datatype that consists of one or more subtypes. Rather that being constrained to the standard Oracle data type of NUMBER, DATE, and VARCHAR2, the abstract data type can more accurately describe data.

Creation of an Object

(Street varchar2(50), city varchar2(25), state vachar2(25), zip number) ;

(name vachar2(250), address address_ty) ;

An abstract data type can be used to create an Object Table. In an object table, the columns of the table map to the columns of an abstract datatype.

(Street varchar2(50), city varchar2(25), state varchar2(25), zip number) ;

(name vachar2(250), address address_ty) ;

(customer_id number, person person_ty)

Retrieving Data using Objects

SELECT client.Person.Name Client.Person.Address.City FROM customer client
WHERE like ‘M%’ ;

Updating Data using Objects

UPDATE customer client
SET’Mumbai’ where Client.Person.Address.City=’Chennai’ ;

Deleting Data using Objects

DELETE from customer client where’Mumbai’ ;

Nested Tables

A nested table is a table within a table. A nested is a collection of rows, represented as a column within the main table. For each record within the main table, the nested table as a column. In one sense, it’s a way of storing a one-to-many relationship within one table.

Varying Arrays

A varying array is a set if objects, each with the same data type. The size of the array is limited when it is created.

When a table is created with a varying array, the array is a nested table a limited set of rows

Varying Arrays, also known as VARRAYS, allows storing repeating attributes in tables.


Nested tables and Varying Arrays are embedded objects. They are physically embedded within another object. Another type of object, called referenced objects, are Physically separated from the objects that refer to them. References (also known as REFs) are essentially pointers to row objects. A row object is different from a column object. An example of a column object would be a varying array, it is an object that is treated as a column in a table. A row object, on the other hand, always represents a row.

References are typically among the last OO features implemented when migrating a relational database to an object-relational or pure OO one.

Object views

Object views allow adding OO concepts on top of existing relational tables. For example, an abstract data type can be created based on an existing table definition. Thus, object views give the benefits of relational table storage and OO structures. Object views allow the development of OO features within a relational database, a kind of bridge between the relational and OO worlds.

(Street varchar2(50), city varchar2(25), state varchar2(25), zip number) ;

(name varchar2(25), address address_ty) ;

(customer_id number, person person_ty) ;

create view cust_obj of address_ty with object oid
(customer_id) as
select customer_id,person_ty(name,address_ty(Street,city,state,zip)) from customer ;


A varying array allows the storing of repeated attributes of a record in a single row. Varying arrays are collectors that allow repetition of only those column values that change, potentially saving storage space. Varying arrays can be used to accurately represent one –to –many relationships where the maximum number of elements on the many side of the relationship is known and where the order of these elements is important.


This statement creates a varray type called company_address_typ, which can hold a maximum of 3 elements of data type varchar2(1000), i.e. 3 entries per record, each storing address information for the company.
Now that the varying array company_address_ty is created, this can be used as a part of the creation of either a table or an abstract datatype

Create table company_info
(company_name varchar2(50), address company_address_ty);
this SQL statement creates a table called company_info, which contains an embedded object called address that is a varray of type company_address_ty.

ORCLE stores the varying array data internally using the RAW datatype.

Reference object

The referencing object (REFs data type) is something that is new to ORACLE 8i. This data type acts as a pointer to an object. A REF can also be used in a manner similar to a foreign key in a RDBMS. A REF is used primarily to store an object identifier, and to allow the user to select that object.

REF’s establish relationship between two-object tables, much the same way as a primary-key/foreign-key relationship in relational tables. Relational tables however, have difficulty, if more than one table is needed in a primary-key/foreign-key relationship related to a single table. For example, an address table, that stores addresses from several entities. The use of REFs eliminates this problem, because an unscoped REF can refer to any accessible object table.

A scope clause in a definition forces a set of REFs for a given column to be confined to a single object table. There can be only one REF clause for a given REF column. REF scope can be set at either the column or table level.

REF values can be stored with or without a ROWID. Storing a REF with a ROWID seeds de-referencing operation, but takes more space. If with ROWID is not specified with the REF clause, the default is to not store ROWIDs with the REF values. SCOPE clauses prevent dangling references, as they will not allow REF values unless the corresponding entries in the SCOPE table is present.

REF columns can be added to nested tables with the ALTER TABLE command.

A call to a REF returns the OID of the object instance. An OID is a 128 bytes base –64 number, which is not very useful except as handle to the object instance. To get the value stored in the instance that is referred to by a REF, the DEREF routine is used. DEREF returns values in the object instance referenced by a specific REF value.