Column Substitutability
Object Types and Column Substitutability
This article shows you how to use extend parent (or superclass) objects. You extend parent classes when you implement specialized behaviors (or methods) in subtypes. That’s because SQL statements can’t work with specialized methods when a table’s column stores subclasses in a superclass column type.
Substitutability is the process of storing subtypes in a super type column. It is a powerful feature of the Oracle database. The “type evolution” feature of the Oracle Database 12c release makes it more important because it makes it more flexible. The flexibility occurs because Oracle lets you evolve parent classes.
You evolve parent classes when you implement MEMBER functions or procedures, and you want to access them for all substitutable column values. That’s necessary because you need to define the MEMBER function or procedure in the column’s base object type. Prior to Oracle Database 12c, you couldn’t change (evolve) a base type. If you’re new to the idea of object types and subtypes, you may want to check out my earlier “Object Types and Subtypes” article.
Before discussing the complexity of creating and evolving object types to support column substitutability, let’s create a base_t object type. The base_t object type will become our root node object type. A root node object type is our most general object type. A root node is also the topmost node of an inverted tree of object types. All subtypes of the root node become child nodes, and child nodes without their own children are at the bottom of the tree and they’re leaf nodes.
The following creates the base_t object type. It is similar to object types that I use in related articles to keep ideas consistent and simple across the articles. This version of the base_t object doesn’t try to maintain an internal unique identifier because the table maintains it as a surrogate key.
SQL> CREATE OR REPLACE 2 TYPE base_t IS OBJECT 3 ( oname VARCHAR2(30) 4 , CONSTRUCTOR FUNCTION base_t 5 RETURN SELF AS RESULT 6 , MEMBER FUNCTION get_oname RETURN VARCHAR2 7 , MEMBER PROCEDURE set_oname (oname VARCHAR2) 8 , MEMBER FUNCTION to_string RETURN VARCHAR2) 9 INSTANTIABLE NOT FINAL; 10 / |
The oname attribute on line two holds the name of the object type. Lines 4 and 5 define the default constructor, which has no formal parameters. Line 6 defines an accessor method, or getter, and line 7 defines a mutator, or setter. Line 8 defines a traditional to_string method that lets you print the contents of the object type.
Next, let’s implement the base_t object type’s body:
SQL> CREATE OR REPLACE 2 TYPE BODY base_t IS 3 /* A default constructor w/o formal parameters. */ 4 CONSTRUCTOR FUNCTION base_t 5 RETURN SELF AS RESULT IS 6 BEGIN 7 self.oname := 'BASE_T'; 8 RETURN; 9 END; 10 /* An accessor, or getter, method. */ 11 MEMBER FUNCTION get_oname RETURN VARCHAR2 IS 12 BEGIN 13 RETURN self.oname; 14 END get_oname; 15 /* A mutator, or setter, method. */ 16 MEMBER PROCEDURE set_oname 17 ( oname VARCHAR2 ) IS 18 BEGIN 19 self.oname := oname; 20 END set_oname; 21 /* A to_string conversion method. */ 22 MEMBER FUNCTION to_string RETURN VARCHAR2 IS 23 BEGIN 24 RETURN '['||self.oname||']'; 25 END to_string; 26 END; 27 / |
Line 7 assigns a literal value to the oname attribute. Line 24 returns the value of the oname attribute for the instance. The remainder of the object type is generic. You can read about the generic features in my “Object Types and Bodies Basics” and about accessor and mutator methods in my “Object Types with Getters and Setters” articles.
Let’s define and implement a hobbit_t subtype of our base_t object type. The hobbit_t object type is:
SQL> CREATE OR REPLACE TYPE hobbit_t UNDER base_t 2 ( genus VARCHAR2(20) 3 , name VARCHAR2(20) 4 , CONSTRUCTOR FUNCTION hobbit_t 5 ( genus VARCHAR2 6 , name VARCHAR2) RETURN SELF AS RESULT 7 , MEMBER FUNCTION get_genus RETURN VARCHAR2 8 , MEMBER FUNCTION get_name RETURN VARCHAR2 9 , MEMBER PROCEDURE set_genus (genus VARCHAR2) 10 , MEMBER PROCEDURE set_name (name VARCHAR2) 11 , OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2) 12 INSTANTIABLE NOT FINAL; 13 / |
Lines 2 and 3 add two new genus and name attributes to the hobbit_t subtype. The hobbit_t subtype also inherits the oname attribute from its parent base_t type. Lines 7 and 8 define two getters and lines 9 and 10 define two setters, which support the genus and name attributes of the hobbit_t subtype. The hobbit_t object type’s getter and setters are unique to the subtype. They are also a specialization of the base_t object type. As such, these getters and setters are inaccessible to instances of the base_t object type. Line 11 defines an overriding to_string function for the base_t type’s to_string function.
The following implements the hobbit_t object body:
SQL> CREATE OR REPLACE TYPE BODY hobbit_t IS 2 /* A default constructor with two formal parameters. */ 3 CONSTRUCTOR FUNCTION hobbit_t 4 ( genus VARCHAR2 5 , name VARCHAR2 ) 6 RETURN SELF AS RESULT IS 7 BEGIN 8 self.oname := 'HOBBIT_T'; 9 self.name := name; 10 self.genus := genus; 11 RETURN; 12 END; 13 /* An accessor, or getter, method. */ 14 MEMBER FUNCTION get_genus RETURN VARCHAR2 IS 15 BEGIN 16 RETURN self.genus; 17 END get_genus; 18 /* An accessor, or getter, method. */ 19 MEMBER FUNCTION get_name RETURN VARCHAR2 IS 20 BEGIN 21 RETURN self.name; 22 END get_name; 23 /* A mutator, or setter, method. */ 24 MEMBER PROCEDURE set_genus 25 ( genus VARCHAR2 ) IS 26 BEGIN 27 self.genus := genus; 28 END set_genus; 29 /* A mutator, or setter, method. */ 30 MEMBER PROCEDURE set_name 31 ( name VARCHAR2 ) IS 32 BEGIN 33 self.name := name; 34 END set_name; 35 /* A to_string conversion method. */ 36 OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS 37 BEGIN 38 /* Uses general invocation on parent to_string 39 function. */ 40 RETURN (self AS base_t).to_string 41 || '['||self.genus||']['||self.name||']'; 42 END to_string; 43 END; 44 / |
Lines 4 and 5 list the parameters for the hobbit_t constructor. Line 8 assigns a literal value to the oname attribute of the base_t object type. Lines 9 and 10 assign the formal parameters to the genus and name attributes of the hobbit_t subtype. Line 40 uses a general invocation statement to call the base_t’s to_string function.
You can now create a table that has a substitutable column that uses the base_t parent object type. The Oracle database assumes object type columns are substitutable at all levels, unless you turn off a column’s substitutability.
The following creates a tolkien table, and it has only two columns. One column has a NUMBER data type and the other has a user-defined object type. The base_t object type column is substitutable at all levels:
SQL> CREATE TABLE tolkien 2 ( tolkien_id NUMBER 3 , character BASE_T ); |
You create a tolkien_s sequence for the unique tolkien_id column with the following:
SQL> CREATE SEQUENCE tolkien_s START WITH 1001; |
You can insert one base_t and two hobbit_t object types with the following INSERT statements:
SQL> INSERT INTO tolkien VALUES 2 ( tolkien_s.NEXTVAL, base_t() ); SQL> INSERT INTO tolkien VALUES 2 ( tolkien_s.NEXTVAL, hobbit_t('HOBBIT','Bilbo') ); SQL> INSERT INTO tolkien VALUES 2 ( tolkien_s.NEXTVAL, hobbit_t('HOBBIT','Frodo') ); |
The following simple query shows you the unique identifier in the tolkien_id column and collapsed object types in the character column of the tolkien table:
SQL> COLUMN character FORMAT A40 SQL> SELECT tolkien_id 2 , character 3 FROM tolkien; |
It should display the following:
TOLKIEN_ID CHARACTER(ONAME)
---------- ----------------------------------------
1001 BASE_T('BASE_T')
1002 HOBBIT_T('HOBBIT_T', 'HOBBIT', 'Bilbo')
1003 HOBBIT_T('HOBBIT_T', 'HOBBIT', 'Frodo') |
Oracle always stores object instances as collapsed object instances in tables. You need to use the TREAT function in SQL to read instances of an object type.
The TREAT function lets you place in memory an instance of an object type. The TREAT function requires that you designate the type of object instance. If you want the TREAT function to work with all rows of the table, you designate the column’s object type as the base (or superclass) type. Designating a subtype to work like a parent, grandparent, or any antecedent type is a form of casting. Though casting in this case is actually dynamic dispatch.
Dynamic dispatch lets you pass any subtype as a parent or antecedent type. Dynamic dispatch inspects the object and treats it as a unique object.
The following query uses the TREAT function to read the parent and any subtype of the parent object type:
SQL> COLUMN to_string FORMAT A40 SQL> SELECT tolkien_id 2 , TREAT(character AS BASE_T).to_string() AS to_string 3 FROM tolkien; |
It prints the oname attribute for base_t instances and the oname, genus, and name attributes for hobbit_t instances, like
TOLKIEN_ID TO_STRING ---------- --------------------------- 1001 [BASE_T] 1002 [BASE_T] 1003 [HOBBIT_T][HOBBIT][Bilbo] 1004 [HOBBIT_T][HOBBIT][Frodo] |
The TREAT function manages dynamic dispatch but requires any specialized method of a subtype to exist in the parent or antecedent type to which it is cast. Any query can cast to the root or an intermediate parent subtype. The TREAT function raises an exception when you don’t have an equivalent method stub (definition) in the parent or antecedent type.
For example, let’s modify the previous query and change the method call on line 2 from the to_string function to the get_name function. The new query is:
SQL> COLUMN to_string FORMAT A40 SQL> SELECT tolkien_id 2 , TREAT(character AS BASE_T).get_name() AS get_name 3 FROM tolkien; |
It fails with the following error:
, TREAT(character AS BASE_T).get_name() AS get_name * ERROR at line 2: ORA-00904: "STUDENT"."BASE_T"."GET_NAME": invalid identifier |
The reason for the failure is interesting. It occurs because the get_name function is only part of the hobbit_t subtype and can’t be found as an identifier inside the base_t object type. PL/SQL identifiers are: reserved or key words; predefined identifiers; quoted identifiers; user-identifiers; and user-defined variables, subroutine, and data or object type names.
You can access the MEMBER functions or procedures (method) of a subtype when you cast to a parent type provided you meet two conditions. First, you must implement the MEMBER method in the subtype. Second, you must define the same MEMBER method in the parent type.
Accessing a subtype MEMBER method differs from general invocation. General invocation occurs when you call a MEMBER method from a parent or antecedent type from a subtype’s OVERRIDING MEMBER method. Oracle doesn’t explain how you call a subtype’s method from a parent or antecedent type but there is a close corollary – packages.
For example, you can only call a package function or procedure from another PL/SQL block when you’ve defined it in the package specification. This means you need to implement a stub for the get_name function inside the base_t object type because it acts as the specification.
You add a get_name function to the base_t object type in the next example:
SQL> CREATE OR REPLACE 2 TYPE base_t IS OBJECT 3 ( oname VARCHAR2(30) 4 , CONSTRUCTOR FUNCTION base_t 5 RETURN SELF AS RESULT 6 , MEMBER FUNCTION get_name RETURN VARCHAR2 7 , MEMBER FUNCTION get_oname RETURN VARCHAR2 8 , MEMBER PROCEDURE set_oname (oname VARCHAR2) 9 , MEMBER FUNCTION to_string RETURN VARCHAR2) 10 INSTANTIABLE NOT FINAL; 11 / |
Line 6 adds the get_name function to the base_t object type. The following shows you how to implement get_name function stub in the object type body:
SQL> CREATE OR REPLACE 2 TYPE BODY base_t IS 3 CONSTRUCTOR FUNCTION base_t 4 RETURN SELF AS RESULT IS 5 BEGIN 6 self.oname := 'BASE_T'; 7 RETURN; 8 END; 9 MEMBER FUNCTION get_name RETURN VARCHAR2 IS 10 BEGIN 11 RETURN NULL; 12 END get_name; 13 MEMBER FUNCTION get_oname RETURN VARCHAR2 IS 14 BEGIN 15 RETURN self.oname; 16 END get_oname; 17 MEMBER PROCEDURE set_oname 18 ( oname VARCHAR2 ) IS 19 BEGIN 20 self.oname := oname; 21 END set_oname; 22 MEMBER FUNCTION to_string RETURN VARCHAR2 IS 23 BEGIN 24 RETURN '['||self.oname||']'; 25 END to_string; 26 END; 27 / |
Lines 9 through 12 implement the get_name function stub. You should note that it returns a null value because the name attribute doesn’t exist in the root node (base_t) object type.
The change to the hobbit_t object type is simpler. All you need to do is add the OVERRIDING keyword before the get_name member function in the hobbit_t object type and body. With that change, you can successfully run the following query:
SQL> COLUMN get_name FORMAT A20 SQL> SELECT tolkien_id 2 , TREAT(character AS BASE_T).get_name() AS get_name 3 FROM tolkien; |
It now works and prints:
TOLKIEN_ID GET_NAME ---------- -------------------- 1001 1002 Bilbo 1003 Frodo |
This article showed you how to extend parent object types. It also showed you how to modify parent types to support generalized calls with the TREAT function. Together these principles show you how to leverage substitutability on columns.
Subscribe via RSS