Defining Object Relational Features

Defining Object Relational Features
An object relational database contains the major aspects of a relational database, and provides for those of an object oriented(OO) database. Relational aspects are structures, operations and integrity rules. OO databases provide for user-defined objects that contain both structures (attributes) and methods.
Oracle Object concepts include abstract data types, object tables, collections such as nested tables and varying arrays (VARRAYS), large objects (LOB’s), references (REFs), and object views.

Define a Basic Object
CREATE TYPE address_obj AS OBJECT
(street VARCHAR2(60),
city VARCHAR2(35),
state CHAR(2),
zip NUMBER);
CREATE TYPE customer_obj AS OBJECT
(name VARCHAR2(50),
Address ADDRESS_OBJ);
CREATE TABLE customer
(cust_id NUMBER,
customer CUSTOMER_OBJ);

Create a Collection Object- VARRAY
CREATE OR REPLACE TYPE flavors_varray AS VARRAY(31) OF VARCHAR2(20);
CREATE TABLE ice_cream
(manufacturer VARCHAR2(40),
flavors FLAVORS_VARRAY,
CONSTRAINT pk_ice_cream PRIMARY KEY (manufacturer));
Each manufacturer in the ice_cream table is uniquely identified by the manufacturer column and can make up 31 different flavors of ice cream.

Create a Collection Object-NESTED TABLE
CREATE OR REPLACE TYPE address_obj AS OBJECT
(type VARCHAR2(10),
street VARCHAR2(60),
city VARCHAR2(35),
state CHAR(2),
zip NUMBER);
note: address_obj contains one record for each address. Record indicates the type of address it is such as billing, shipping, etc.
CREATE TYPE addresses_nt AS TABLE OF address_obj;
note: addresses_nt is an object of type nested table.
CREATE TABLE customer
(name VARCHAR2(50),
addresses ADDRESSES_NT)
NESTED TABLE addresses STORE AS addresses_nt_tab;
Note: create customer table containing the addresses_nt nested table. Out-of-line nested table data stored in addresses_nt_tab table.

Create and use an Object View
Use object views to implement OO structures on-top of an existing relational dB.
Can update the underlying relational table via the object view or via inserts to the relational table itself.
Can use the INSTEAD OF trigger on an object view.
The general steps to create an OBJECT VIEW are:
identify or create the relational table(s)
create the objects ==> CREATE TYPE.....AS OBJECT
create the object view
Based upon this relational table and these objects:
CREATE TABLE customer
(id NUMBER PRIMARY KEY,
name VARCHAR2(40),
street VARCHAR2(60),
city VARCHAR2(35),
state CHAR(2),
zip NUMBER);
CREATE TYPE address_obj AS OBJECT
(street VARCHAR2(60),
city VARCHAR2(35),
state CHAR(2),
zip NUMBER);
CREATE TYPE customer_obj AS OBJECT
(name VARCHAR2(50),
Address ADDRESS_OBJ);
The syntax to create an object view would be:
CREATE VIEW customer_object_view
(id, customer)
AS
SELECT id,
customer_obj(name,
address_obj(street, city, state, zip))
FROM customer ;