Oracle Objects

1
Oracle Objects

• Object Oriented
• Database Approach

2
Overview

• Oracle supported concepts - features are
  implemented as extensions on relational engine
• Defining Oracle objects
• Using Oracle objects
• Object views

3
Oracle Supported OO Concepts

• An object has a name, a standard representation,
  and a standard collection of operations that
  affect it (methods)
• Abstract data types model classes of data within
  the database
• Abstract data types inherit the representation of
  their parents
• hierarchies of abstract data types
• implementation inheritance - inherit behavior of
  parents

4
Oracle Supported OO Concepts cont

• Approximate encapsulation
• encapsulation - data structure can only be
  accessed via a defined set of methods
• relational system cannot have means of accessing
  data limited
• No polymorphism
• ability of same instruction to be interpreted
  different ways by different objects

5
Abstract Data types

• Data types that consist of one or more subtypes
• Can be nested
• Can reference other abstract datatypes
• Example
• CREATE TYPE ADDR_TY AS OBJECT
• (STREET VARCHAR2(50),
• CITY VARCHAR2(25),
• STATE CHAR(2),
• ZIP VARCHAR(9)) /

6
Embedded Objects - Collectors

• Nested table -
• table within a table
• collection of rows represented as a column within
  main table
• stores 1M relationships
• Varying arrays
• set of objects each with the same data type
• nested table with a limited set of rows (size
  limited when created)
• Store repeating attributes in table
• Cannot be indexed

7
Large Objects - LOB

• BLOB - binary data that can be extended to 4 GB
• CLOB - character data up to 4 GB
• NCLOB - stores CLOB data for multibyte character
  sets
• Stored inside database, can be single row
• BFILE - pointer to external file.
• File exists on OS

8
Example of Simple Object
CREATE TYPE PERSON_TY AS OBJECT (NAME
VARCHAR2(25), ADDRESS ADDR_TY) / Note data
described not stored, cannot store data in
types CREATE TABLE STUDENT (STUDENT_ID VARCHAR2(
9), PERSON PERSON_TY) /
Must own data type or be granted access. Need
execute access for methods including constructor
methods. Avoid synonyms
9
Inserting Records

• Constructor methods - program named after the
  data type, parameters names of attributes defined
  for datatype
• insert into student values
• (100,
• person_ty(Mary Ann Robbert,
• addr_ty(122 North St., Watham,MA,
  02579)))
• constructor methods

10
Querying

• Select student_id, person.name ...
• COLUMN.ATTRIBUTE
• Select person.addr.street
• COLUMN.COLUMN.ATTRIBUTE
• SELECT STUDENT_ID,
• S.PERSON.NAME, S.PERSON.ADDRESS.STATE
• FROM STUDENT S

11
Object Views

• Bridge between relational and object
• Allows adding OO concepts on top of relational
  tables
• Gives benefit of relational storage with OO
  structures
• Benefits
• create abstract data types within tables that
  already exist
• flexibility to treat base table as relational
  table or object table

12
Creating Object View based on Existing Table

• Assume person, and address types plus a
  relational student table
• CREATE VIEW STUDENT_OV (STUDENT_ID, PERSON) AS
  SELECT STUDENT_ID,PERSON_TY(NAME,ADDR_TY(STREET,
  CITY,STATE,ZIP))FROM STUDENT

13
Updating Through Object View

• Instead of Triggers
• use on object views or relational views
• change values through views
• Use with PL/SQL code
• create trigger xyzinstead of update on viewfor
  each row .

14
Methods

• CREATE TYPE PERSON_TY3 AS OBJECT
• (NAME VARCHAR2(25),
• ADDRESS ADDR_TY,
• BIRTHDATE DATE,
• MEMBER FUNCTION AGE(BIRTHDATE IN DATE) RETURN
  NUMBER)

15
Methods

• CREATE TYPE PERSON_TY4 AS OBJECT
• (NAME VARCHAR2(25),
• ADDRESS ADDR_TY,
• BIRTHDATE DATE,
• MEMBER FUNCTION AGE(BIRTHDATE IN DATE) RETURN
  NUMBER,
• PRAGMA RESTRICT_REFERENCES(AGE,WNDS))
• WNDS Write No Database State RNDS - Read (no
  queries) WNPS No packaged variables changed
  RNPS no packg var referenced

16
Methods

• create or replace type body person_ty4 as
  member function AGE(birthdate date) return number
  is
• BEGIN
• RETURN ROUND(SYSDATE - BIRTHDATE)
• END
• END

17
Example

• CREATE TABLE STUDENT4
• (SID NUMBER,
• PERSON PERSON_TY4)
• SELECT
• S.AGE(STUDENT4.BIRTHDATE) FROM STUDENT4 S

18
Managing Methods

• Cannot drop or recreate type that is in use by a
  table
• Use ALTER TYPE to add new methods
• Grant execute on type gives priviledges to
  methods

19
OO ANALYSIS AND DESIGN

• Goes beyond normalization (relating each
  attribute to primary key), seeks groups of
  columns that define a common object
  representation
• Uses types that are
• reused
• will always behave in the same manner