Inheritance in Oracle9i Donald K. Burleson

Oracle9i has finally completed their object-relational data model by allowing inheritance in Oracle class definitions.  Prior to Oracle9i, Oracle implemented abstract data typing, varray (non first-normal form) tables, nested tables, and pointer navigation, but true inheritance was not possible since sub-category definitions were not possible.

For those who are not familiar with inheritance, Oracle9i allows sub-typed objects to inherit data items at object creation time, and inherit methods at runtime, much like C++ code.

To Illustrate, here is an Oracle9i class hierarchy definition.  Note the new NOT INSTANTIABLE NOT FINAL syntax for base classes.

Also note the new UNDER syntax that allows you to define sub-classes within a base class.

create type category_typ as object

( category_name varchar2(50)

, category_description varchar2(1000)

, category_id number(2)

)

NOT INSTANTIABLE NOT FINAL;

create type subcategory_ref_list_typ as table of ref

category_typ;

create type product_ref_list_typ as table of number(6);

/

create type corporate_customer_typ under customer_typ

( account_mgr_id number(6)

);

create type leaf_category_typ under category_typ

(

product_ref_list product_ref_list_typ

);

create type composite_category_typ under category_typ

(

subcategory_ref_list subcategory_ref_list_typ

)

NOT FINAL;

create type catalog_typ under composite_category_typ

(

member function getCatalogName return varchar2

);
 

If you like Oracle tuning, check out my latest book "Oracle Tuning: The Definitive Reference".  It's 980 pages of hard-core tuning insights, tips and scripts, and you can buy it direct from the publisher for 30%-off. Best of all, you get instant access to the code depot of Oracle tuning scripts.