Oracle object types can form a hierarchy, like classes in an object-oriented language. A subtype inherits the attributes and methods of its supertype, adds its own, and can override methods. A variable or collection of the supertype can hold any subtype value, and calling a method runs the version of the actual type, which is called dynamic dispatch.
Code for This Guide
The main examples are in the examples/advanced-plsql folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create type supertype as object (...) not final; create type subtype under supertype ( new_attribute type, overriding member function method_name return type );
A type can only be inherited from if it is declared NOT FINAL; types are FINAL by default. A method marked OVERRIDING replaces the supertype's version for the subtype.
A Supertype and a Subtype
T_STAFF has a name and a DESCRIBE method. T_PILOT_EMP inherits from it, adds a licence, and overrides DESCRIBE. One collection of T_STAFF holds a value of each type.
Example:
create or replace type t_staff as object (
name varchar2(40),
member function describe return varchar2
) not final;
/
create or replace type body t_staff as
member function describe return varchar2 is begin return name; end;
end;
/
create or replace type t_pilot_emp under t_staff (
licence varchar2(12),
overriding member function describe return varchar2
);
/
create or replace type body t_pilot_emp as
overriding member function describe return varchar2 is
begin return 'Captain ' || name || ' (' || licence || ')'; end;
end;
/
declare
type t_list is table of t_staff;
v_people t_list := t_list(t_staff('Emma Williams'),
t_pilot_emp('Kenji Sato', 'ATPL-40771'));
begin
for i in 1 .. v_people.count loop
dbms_output.put_line(v_people(i).describe); -- dynamic dispatch
end loop;
end;
/Output:
Type T_STAFF compiled Type Body T_STAFF compiled Type T_PILOT_EMP compiled Type Body T_PILOT_EMP compiled Emma Williams Captain Kenji Sato (ATPL-40771) PL/SQL procedure successfully completed.
The same call, v_people(i).describe, returns the plain name for the staff member and the captain form for the pilot, because each value runs the method of its own type. The example drops both types afterward.
Test and Narrow the Type with IS OF and TREAT
Through a supertype variable, only the supertype's attributes are visible. IS OF tests the actual type of a value, and TREAT narrows it to the subtype so its own attributes can be read.
Example:
declare
v_person t_staff := t_pilot_emp('Kenji Sato', 'ATPL-40771'); -- a subtype value
v_pilot t_pilot_emp;
begin
if v_person is of (t_pilot_emp) then
v_pilot := treat(v_person as t_pilot_emp); -- narrow to reach LICENCE
dbms_output.put_line('licence: ' || v_pilot.licence);
end if;
dbms_output.put_line('describe: ' || v_person.describe);
end;
/Output:
licence: ATPL-40771 describe: Captain Kenji Sato (ATPL-40771) PL/SQL procedure successfully completed.
The variable is declared as T_STAFF but holds a T_PILOT_EMP. IS OF confirms the type, TREAT gives access to LICENCE, and DESCRIBE still dispatches to the pilot version. Test with IS OF first: in PL/SQL, treating a plain T_STAFF value as T_PILOT_EMP raises ORA-06502.
Things to Know
- Create the supertype first and drop the subtypes first; DROP TYPE ... FORCE drops a type that others depend on.
- Declare a type NOT INSTANTIABLE to use it only as an abstract base for subtypes.
- Columns and tables of a supertype can store subtype values too, which is called substitutability.
Related Guides
Conclusion
Object type inheritance lets a subtype reuse and extend a NOT FINAL supertype, overriding its methods where behavior differs. Supertype variables and collections hold any subtype, methods dispatch to the actual type, and IS OF and TREAT test and narrow a value when you need the subtype's own attributes.
