Object Oriented Features in Oracle

Data Dictionary Entries

• types are described in USER_TYPES
• methods are described in USER_TYPE_METHODS

• each collection type describes a data item made up of a number of elements of the same data type
• two types: varray and nested tables
• have a constructor method; returns the new collection as its value
• individual items in the collection can be referenced (in PL/SQL) with standard subscript notation: collection-name(subscript)
• nested tables are stored in a separate table, varrays are not

• ordered set of data elements
• can't delete individual items
• variable size, specify a maximum size when declaring a varray

Nested Tables

• like a single column database table; no limit on size; not ordered
• can delete individual entries

•  the following PL/SQL code creates a nested table containing supplier numbers:
create or replace type supplier as table of char(3);
/

• the product table created below contains the supplier table as a nested table:
create table product
    (prodno char(5) primary key,
     price number(5,2),
     supplierno supplier)
     nested table supplierno store as supplier_tbl;

• the "nested table" clause specifies a table name for the supplier data; unlike a VARRAY, a nested table is stored in a separate table
• the SUPPLIER constructor function is used when a row is added to the PRODUCT table:
 insert into product values ('XYZ01', 23.45, supplier('101','224', '456'));
 insert into product values ('XYZ02', 42.56, supplier('224','572'));

• the supplier data is stored in a separate table, as sketched below:

•  rows can be retrieved from the table using a standard SELECT statement; the nested table entries are displayed as a group:
select * from product;

PRODNO PRICE
----- ---------
SUPPLIERNO
------------------------------
XYZ01 23.45
SUPPLIER('101', '224', '456')
XYZ02 42.56
SUPPLIER('224', '572')

• individual elements of a nested table can be referenced in PL/SQL using standard subscript notation
• the following example lists all the suppliers for a product number passed as a parameter
• the program retrieves the required nested table into a variable temp_supplier of type supplier
• a for loop is used to step through the suppliers
• note that count is a "collection method" - a built-in function that operates on collections
set serveroutput on size 20000
declare
    temp_prodno product.prodno%type;
    temp_supplier supplier;
    i integer;
begin
    temp_prodno := upper('&1');
    select supplierno into temp_supplier
        from product
        where prodno = temp_prodno;
    dbms_output.put_line('Suppliers for product # ' || temp_prodno);
    for i in 1..temp_supplier.count loop
        dbms_output.put_line(temp_supplier(i));
    end loop;
end;
/

• object specification has two parts: a specification and a body
• the specification declares the attributes and the methods
• the body contains the code for the methods
•  in the following example, an object containing course information will be defined
• one of the attributes of the object will be a nested table containing information on course sections
• the course object will have a method called total_enrolment that will return the total enrolment in the course for all sections
•  first, the section object type will be defined:
create type section_obj as object
    (section_no char(3),
    professor char(20),
    current_enrol number(3));
/

• now a nested table containing the section_obj objects is defined:
create type course_sections as table of section_obj;
/

• the course object is defined as follows:
create or replace type course_obj as object
    (course_no char(8),
    course_title char(30),
    sections course_sections,
    member function total_enrolment return integer,
    pragma restrict_references(total_enrolment, WNDS));
/

• the PRAGMA (a PRAGMA is a compiler directive) restrict_references specifies that the function does not modify any database tables; a function called from an SQL statement is not allowed to modify a database table

• the following statement will give the code for the total_enrolment method
create or replace type body course_obj as
    member function total_enrolment return integer is
    totenrol number;
    i integer;
    begin
        totenrol := 0;
        for i in 1..sections.count loop
            totenrol := totenrol + sections(i).current_enrol;
        end loop;
        return totenrol;
    end total_enrolment;
end;
/

• an object table must be declared to contain the course objects
• each row will contain an object instance
• each instance is assigned a unique object identifier (OID) when a row is inserted
create table course_tbl of course_obj
    (primary key (course_no))
    nested table sections store as sections_tbl;

• course data can now be loaded; in the insert statements, the COURSE_SECTIONS and SECTION_OBJ constructor functions are used:
insert into course_tbl values ('DAT2210','Computer Fundamentals',
                                            course_sections(section_obj('010','Brown',15),
                                                                     section_obj('020','White',20)));
insert into course_tbl values ('DAT2219','Computer Programming I',
                                             course_sections(section_obj('010','Black',30),
                                                                      section_obj('020','Green',25),
                                                                      section_obj('030','Grey',33)));

• note that the course section data will be stored in a separate table, as sketched below:

• the following select statement will demonstrate using the total_enrolment method; the table alias is required in the syntax
select course_no, c.total_enrolment() from course_tbl c;

COURSE_NO       C.TOTAL_ENROLMENT()
DAT2210                     35
DAT2219                     88
 

• objects that appear in object tables are called row objects
• Oracle gives each row object a unique identifier called an object identifier
• allows the row object to be referenced from other objects or tables
• built-in data type called REF is used:
create type home as object
(address varchar2(50), owner char(30), .....);
 

create type occupant as object
(occupant_id char(9), occupant_name char(25), .....
home_address REF home,.....);

• home_address will provide a direct link to the associated row in home
• Oracle provides an operator called DEREF to locate data in the table pointed to by the REF column (similar to dereferencing a pointer in C/C++)