type enhancements in oracle 9i
User-defined object types were introduced in Oracle 8.0 as part of Oracle's "object-relational" extensions to the RDBMS. Despite many early restrictions, Oracle has continued to develop object-relational technology in the database in addition to using types as part of many internal applications. This article introduces some enhancements to types in Oracle 9i (Releases 1 and 2).
attitudes to object types
Developers have generally been less than enthusiastic about the original implementation of objects in Oracle. On the one hand, many object-oriented features (such as inheritance, polymorphism etc) were excluded. This did little to enthuse object-oriented developers. On the other hand, the syntax, general restrictions and performance associated with using objects for structured storage put off many relational developers from using these new features.
To this day, it is generally considered that user-defined object types are of little benefit when used as a replacement for relational data storage (i.e. they are considered to be "bad" in tables). This author's own experiences of object types in tables certainly support this opinion. For PL/SQL and application development, however, object types can be extremely useful. They are an integral part of the new pipelined functions feature and have been used to great effect in 8i to overcome some limitations in the BULK COLLECT feature. For this reason, it is important that developers have a good understanding of user-defined types and how they can be used to improve the quality of the applications we develop.
new features covered in this article
We will cover the following new features in this article.
- constructor functions;
- type evolution; and
- type inheritance and polymorphism (known as substitutable types).
Note that wherever "type" is used, this is taken to mean "user-defined object type" unless stated otherwise. It is assumed that readers are familiar with object types in Oracle, though an in-depth knowledge is not required.
constructor functions
When we create an instance of a type (such as a variable, for example), we use the type's constructor. Before Oracle 9i Release 2 (9.2), the object's constructor was fixed in definition and required a value for every attribute in the type. This is known as the type's default constructor.
In the following example, we will see how to use a type's default constructor. First, we will create a simple object type named OT with three attributes and no methods.
SQL> CREATE TYPE ot AS OBJECT ( 2 attr1 VARCHAR2(30) 3 , attr2 VARCHAR2(30) 4 , attr3 VARCHAR2(30) 5 ); 6 /
Type created.
A variable of this type must be instantiated using the type's default constructor as follows (even if we wish to set all attributes to NULL).
SQL> DECLARE
2 o ot := ot('X','Y','Z');
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE( 'o.attr1 = [' || o.attr1 || ']' );
5 END;
6 /
o.attr1 = [X] PL/SQL procedure successfully completed.
If we attempt to initialise this variable with anything other than the default constructor, we receive the following error.
SQL> DECLARE 2 o ot := ot(); 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE( 'o.attr1 = [' || o.attr1 || ']' ); 5 END; 6 /
o ot := ot();
*
ERROR at line 2:
ORA-06550: line 2, column 12:
PLS-00306: wrong number or types of arguments in call to 'OT'
ORA-06550: line 2, column 6:
PL/SQL: Item ignored
ORA-06550: line 4, column 43:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored
In Oracle 9.2, we can define one or more constructor functions to overload the way in which we instantiate a type variable. In the following example, we will re-create the OT type, but this time add our own constructor function to be used as an alternative to the type's default constructor.
SQL> CREATE OR REPLACE TYPE ot AS OBJECT ( 2 3 attr1 VARCHAR2(30) 4 , attr2 VARCHAR2(30) 5 , attr3 VARCHAR2(30) 6 7 , CONSTRUCTOR FUNCTION ot 8 RETURN SELF AS RESULT 9 ); 10 /
Type created.
Note that we have defined a constructor function without parameters. A constructor function must have the same name as the type. The syntax RETURN SELF AS RESULT is mandatory and indicates that the function will return an instance of the OT type.
A constructor function is implemented in a type body. The type body for our OT type is as follows.
SQL> CREATE TYPE BODY ot AS 2 3 CONSTRUCTOR FUNCTION ot 4 RETURN SELF AS RESULT IS 5 BEGIN 6 SELF.attr1 := 'X'; 7 SELF.attr2 := 'Y'; 8 SELF.attr3 := 'Z'; 9 RETURN; 10 END; 11 12 END; 13 /
Type body created.
For demonstration purposes, our user-defined constructor will set hard-coded default values for the type's attributes. It is more likely in practice that a non-default constructor function with no parameters (such as our's) will set all attributes to NULL. It is especially useful when initialising or resetting a type with many attributes (it prevents having to supply a long list of NULL parameters otherwise required by the default constructor). It also helps to avoid having to change any dependant application code in the event that additional attributes are added to the type.
Moving on, we now have two methods for instantiating a variable of the OT type, which we can test as follows.
SQL> DECLARE
2 o1 ot := ot('A','B','C');
3 o2 ot := ot();
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE( 'o1.attr1 = [' || o1.attr1 || ']' );
6 DBMS_OUTPUT.PUT_LINE( 'o2.attr1 = [' || o2.attr1 || ']' );
7 END;
8 /
o1.attr1 = [A] o2.attr1 = [X] PL/SQL procedure successfully completed.
We can see that both constructors set an initial state for the type variable (the default constructor sets whatever we pass in and our new constructor initialises with a set of default values coded in the type body).
As stated earlier, types can have one or more constructor functions. We will now re-create our OT type with two user-defined constructor functions. This time, the function with no parameters will initialise an all-NULL instance of OT, while we will have an alternative constructor to initialise with two of the three attributes. First we create our type specification as follows.
SQL> CREATE OR REPLACE TYPE ot AS OBJECT ( 2 3 attr1 VARCHAR2(30) 4 , attr2 VARCHAR2(30) 5 , attr3 VARCHAR2(30) 6 7 , CONSTRUCTOR FUNCTION ot 8 RETURN SELF AS RESULT 9 10 , CONSTRUCTOR FUNCTION ot ( p_attr1 IN VARCHAR2, 11 p_attr2 IN VARCHAR2 ) 12 RETURN SELF AS RESULT 13 ); 14 /
Type created.
We can see that our OT type now has a total of three constructors (one default and two user-defined). We can consider each of these functions an overload. The body for this type is created as follows. As stated above, the version of the function with no parameters will initialise all attributes to NULL. For this, we do not need to assign anything in the body.
SQL> CREATE OR REPLACE TYPE BODY ot AS 2 3 CONSTRUCTOR FUNCTION ot 4 RETURN SELF AS RESULT IS 5 BEGIN 6 RETURN; 7 END; 8 9 CONSTRUCTOR FUNCTION ot ( p_attr1 IN VARCHAR2, 10 p_attr2 IN VARCHAR2 ) 11 RETURN SELF AS RESULT IS 12 BEGIN 13 SELF.attr1 := p_attr1; 14 SELF.attr2 := p_attr2; 15 SELF.attr3 := 'Z'; 16 RETURN; 17 END; 18 19 END; 20 /
Type body created.
Note how we set a default value for ATTR3 in our newest constructor function. In practice, any attributes that are not supplied in the constructor call are likely to be initialised as NULL, but the preceding example shows how we can default them to whatever we wish.
We can test our three constructors as follows.
SQL> DECLARE
2 o1 ot := ot('A','B','C');
3 o2 ot := ot();
4 o3 ot := ot('F','G');
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE( 'o1.attr1 = [' || o1.attr1 || ']' );
7 DBMS_OUTPUT.PUT_LINE( 'o2.attr1 = [' || o2.attr1 || ']' );
8 DBMS_OUTPUT.PUT_LINE( 'o3.attr1 = [' || o3.attr1 || ']' );
9 END;
10 /
o1.attr1 = [A] o2.attr1 = [] o3.attr1 = [F] PL/SQL procedure successfully completed.
type evolution
Prior to Oracle 9i, types were quite inflexible for use in evolving applications. Once a type was added to a table, object view or used in another type definition, it couldn't easily be modified. For example, imagine a large table with an embedded object column. If a new attribute needed to be added to the object type, the type would need to be replaced by a new version, yet the fact that it was used in a table would prohibit such an action. The sequence of events to replace this type were quite onerous.
One of the key enhancements to types in 9i, therefore, is the ability to evolve a type definition. With this feature we can add or remove attributes, constructor functions and static or member functions and procedures. We can also cascade these changes to any underlying objects (such as tables or other types) that use the evolving type. We shall see some simple examples of type evolution below.
evolving attributes
We will begin by adding attributes to an existing type, a new feature of Oracle 9i Release 1 (9.0). For the following examples, we will continue with our OT type. Remember that this type currently has three attributes (ATTR1-3) and two user-defined constructor functions. We will begin by adding two new attributes using the ALTER TYPE syntax as follows.
SQL> ALTER TYPE ot ADD 2 ATTRIBUTE (attr4 NUMBER, 3 attr5 NUMBER) 4 CASCADE;
Type altered.
We use the CASCADE keyword to instruct Oracle to cascade these changes to any objects that currently use this type definition. Note that the default behaviour of CASCADE is to add the attributes to all dependant objects but not modify any table data (i.e. where the type is used as a table or table column). We shall see why this is an important distinction later.
We can test our new type as follows. Note that the default constructor has now changed to require an input for all five attributes and not three as in the previous version of the OT type. Our user-defined constructors have not changed; therefore the new attributes will be initialised to NULL.
SQL> DECLARE
2 o1 ot := ot('A','B','C',1,2);
3 o2 ot := ot();
4 o3 ot := ot('F','G');
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE( 'o1.attr5 = [' || o1.attr5 || ']' );
7 DBMS_OUTPUT.PUT_LINE( 'o2.attr5 = [' || o2.attr5 || ']' );
8 DBMS_OUTPUT.PUT_LINE( 'o3.attr5 = [' || o3.attr5 || ']' );
9 END;
10 /
o1.attr5 = [2] o2.attr5 = [] o3.attr5 = [] PL/SQL procedure successfully completed.
If we wish to set non-NULL default values for our new attributes, we can simply change the OT type body as follows.
SQL> CREATE OR REPLACE TYPE BODY ot AS 2 3 CONSTRUCTOR FUNCTION ot 4 RETURN SELF AS RESULT IS 5 BEGIN 6 RETURN; 7 END; 8 9 CONSTRUCTOR FUNCTION ot ( p_attr1 IN VARCHAR2, 10 p_attr2 IN VARCHAR2 ) 11 RETURN SELF AS RESULT IS 12 BEGIN 13 SELF.attr1 := p_attr1; 14 SELF.attr2 := p_attr2; 15 SELF.attr3 := NULL; 16 SELF.attr4 := 100; 17 SELF.attr5 := 200; 18 RETURN; 19 END; 20 21 END; 22 /
Type body created.
This time, when we invoke the constructor function with two parameters, we also initialise values for our new attributes, as we can see below.
SQL> DECLARE
2 o1 ot := ot();
3 o2 ot := ot('F','G');
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE( 'o1.attr5 = [' || o1.attr5 || ']' );
6 DBMS_OUTPUT.PUT_LINE( 'o2.attr5 = [' || o2.attr5 || ']' );
7 END;
8 /
o1.attr5 = [] o2.attr5 = [200] PL/SQL procedure successfully completed.
evolving constructors
In addition to attributes, in Oracle 9.2 we can also evolve our constructor functions without dropping and re-creating the type specification (dropping the type might not be possible if there are dependencies such as columns or object-tables). If we wish to add a new constructor function to include one or more of our new attributes, we can add the constructor with ALTER TYPE. If we wish to replace an existing non-default constructor such that it includes one of more or our new attributes we must drop and add the function, also with ALTER TYPE.
We will see an example of how to replace a constructor function below. We will replace our second constructor function with a version that accepts a parameter for one of our new attributes. First, we must drop the existing constructor function as follows.
SQL> ALTER TYPE ot DROP 2 CONSTRUCTOR FUNCTION ot( p_attr1 IN VARCHAR2, 3 p_attr2 IN VARCHAR2 ) 4 RETURN SELF AS RESULT 5 CASCADE;
Type altered.
Note how we include the function's full specification in the ALTER TYPE...DROP command. This is only necessary when we have more than one user-defined constructor function, to ensure that we drop the correct one. We can add a new version of this function as follows.
SQL> ALTER TYPE ot ADD 2 CONSTRUCTOR FUNCTION ot( p_attr1 IN VARCHAR2, 3 p_attr2 IN VARCHAR2, 4 p_attr5 IN NUMBER ) 5 RETURN SELF AS RESULT 6 CASCADE;
Type altered.
We now have one of our new attributes, ATTR5, included in one of the non-default constructors. The type body must be re-created to account for this change, as follows.
SQL> CREATE OR REPLACE TYPE BODY ot AS 2 3 CONSTRUCTOR FUNCTION ot 4 RETURN SELF AS RESULT IS 5 BEGIN 6 RETURN; 7 END; 8 9 CONSTRUCTOR FUNCTION ot ( p_attr1 IN VARCHAR2, 10 p_attr2 IN VARCHAR2, 11 p_attr5 IN NUMBER ) 12 RETURN SELF AS RESULT IS 13 BEGIN 14 SELF.attr1 := p_attr1; 15 SELF.attr2 := p_attr2; 16 SELF.attr3 := 'Z'; 17 SELF.attr4 := 100; 18 SELF.attr5 := p_attr5; 19 RETURN; 20 END; 21 22 END; 23 /
Type body created.
We can test our evolved constructor function as follows.
SQL> DECLARE
2 o1 ot := ot();
3 o2 ot := ot('F','G',10000);
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE( 'o1.attr5 = [' || o1.attr5 || ']' );
6 DBMS_OUTPUT.PUT_LINE( 'o2.attr5 = [' || o2.attr5 || ']' );
7 END;
8 /
o1.attr5 = [] o2.attr5 = [10000] PL/SQL procedure successfully completed.
evolving methods
In addition to constructor functions, we can add, remove or replace member or static functions and procedures via type evolution. Note that ALTER TYPE has always supported the addition of methods to types (methods do not store data, so new methods do not need to be physically cascaded to stored type columns in the same way that attributes do). Removing methods is a new feature of Oracle 9i Release 1 (9.0).
In the following example, we will add a "PRINT" member function to our OT type as follows.
SQL> ALTER TYPE ot ADD 2 MEMBER FUNCTION print RETURN VARCHAR2 3 CASCADE;
Type altered.
To implement the PRINT function, we must re-create the type body as follows.
SQL> CREATE OR REPLACE TYPE BODY ot AS 2 3 CONSTRUCTOR FUNCTION ot 4 RETURN SELF AS RESULT IS 5 BEGIN 6 RETURN; 7 END; 8 9 CONSTRUCTOR FUNCTION ot ( p_attr1 IN VARCHAR2, 10 p_attr2 IN VARCHAR2, 11 p_attr5 IN NUMBER ) 12 RETURN SELF AS RESULT IS 13 BEGIN 14 SELF.attr1 := p_attr1; 15 SELF.attr2 := p_attr2; 16 SELF.attr3 := 'Z'; 17 SELF.attr4 := 100; 18 SELF.attr5 := p_attr5; 19 RETURN; 20 END; 21 22 MEMBER FUNCTION print RETURN VARCHAR2 IS 23 BEGIN 24 RETURN SELF.attr1 || '-' || 25 SELF.attr2 || '-' || 26 SELF.attr3 || '-' || 27 TO_CHAR(SELF.attr4) || '-' || 28 TO_CHAR(SELF.attr5); 29 END; 30 31 END; 32 /
Type body created.
Note that the PRINT member function will simply output a delimited string of the current attribute values. We can test our evolved type as follows.
SQL> DECLARE
2 o ot := ot('X','Y','Z',1,2);
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE( o.print() );
5 END;
6 /
X-Y-Z-1-2 PL/SQL procedure successfully completed.
evolved types and tables
We have explicitly avoided any examples that include object types in tables up to this point. This is intentional (experience has led to a rather biased view of using types in tables). However, we will now create a simple table with a column of our OT type and use this to demonstrate cascading attribute evolution. First, we will create table T and populate it with a small number of rows as follows.
SQL> CREATE TABLE t 2 ( number_column NUMBER 3 , object_column ot 4 );
Table created.
SQL> INSERT INTO t 2 SELECT ROWNUM 3 , ot(owner, name, type, line, length(text)) 4 FROM dba_source 5 WHERE ROWNUM <= 5;
5 rows created.
SQL> COMMIT;
Commit complete.
The data in T is as follows.
SQL> SELECT * FROM t;
NUMBER_COLUMN OBJECT_COLUMN(ATTR1, ATTR2, ATTR3, ATTR4, ATTR5)
------------- -----------------------------------------------------------
1 OT('SYS', 'DATAPROVIDERINTERFACE', 'PACKAGE BODY', 1, 4000)
2 OT('SYS', 'DATAPROVIDERINTERFACE', 'PACKAGE BODY', 2, 4000)
3 OT('SYS', 'DATAPROVIDERINTERFACE', 'PACKAGE BODY', 3, 4000)
4 OT('SYS', 'DATAPROVIDERINTERFACE', 'PACKAGE BODY', 4, 4000)
5 OT('SYS', 'DATAPROVIDERINTERFACE', 'PACKAGE BODY', 5, 4000)
5 rows selected.
We can now evolve our OT type to add a sixth attribute. This time, we will use extended syntax to evolve the table data as well as the type signature. Oracle calls this data evolution an "upgrade". We evolve OT and its dependant table data as follows.
SQL> ALTER TYPE ot ADD 2 ATTRIBUTE (attr6 DATE) 3 CASCADE INCLUDING TABLE DATA;
Type altered.
The CASCADE INCLUDING TABLE DATA can be "expensive". This clause updates all type data in tables with column(s) of the OT type. This is done in a single transaction and cannot be broken down into smaller units of work. If we trace this operation we see a recursive SQL statement of the form "UPDATE T SET OBJECT_COLUMN = OBJECT_COLUMN". This is followed by a dictionary update to indicate that the table's data has been "upgraded". We can see this in any of the XXX_TAB_COLUMNS views as follows.
SQL> SELECT data_upgraded 2 FROM user_tab_columns 3 WHERE table_name = 'T' 4 AND column_name = 'OBJECT_COLUMN';
DATA_UPGRADED ------------- YES 1 row selected.
If we attempt to select our upgraded data, we might receive the following error.
SQL> SELECT * FROM t;
ERROR: ORA-22337: the type of accessed object has been evolved no rows selected
In such cases, we simply need to re-connect. We can then view the T data and see that the new attribute (ATTR6) has been added with NULL values, as follows.
SQL> SELECT * FROM t;
NUMBER_COLUMN OBJECT_COLUMN(ATTR1, ATTR2, ATTR3, ATTR4, ATTR5, ATTR6)
------------- -----------------------------------------------------------------
1 OT('SYS', 'DATAPROVIDERINTERFACE', 'PACKAGE BODY', 1, 4000, NULL)
2 OT('SYS', 'DATAPROVIDERINTERFACE', 'PACKAGE BODY', 2, 4000, NULL)
3 OT('SYS', 'DATAPROVIDERINTERFACE', 'PACKAGE BODY', 3, 4000, NULL)
4 OT('SYS', 'DATAPROVIDERINTERFACE', 'PACKAGE BODY', 4, 4000, NULL)
5 OT('SYS', 'DATAPROVIDERINTERFACE', 'PACKAGE BODY', 5, 4000, NULL)
5 rows selected.
Some important points to consider when including evolving types in tables are as follows.
- until we upgrade a table's data, we cannot export the table or select from it over a database link;
- to upgrade data at a later stage to the attribute evolution (for example, when downtime can be planned, particularly for large tables), we can use the ALTER TABLE {table_name} UPGRADE syntax;
- attributes in type columns are stored as hidden "regular" columns by Oracle (we can see these in the XXX_TAB_COLS views). When we evolve a type by adding attributes, the new hidden columns are appended to the end of the current column list. If the type column is not the last column in the table, the new attributes will be stored separately from the original type attributes. A CTAS operation (Create Table As Select) off this table will re-order the hidden columns to write the type attributes together so the original table and CTAS table now have different underlying structures. This might cause problems for techniques that require parity between the two tables, such as partition exchange;
- if a type's evolution does not add or remove any attributes (i.e. it only involves constructor functions or member/static procedures/functions), a table's data does not need to be upgraded. Only attribute evolution has a potential knock-on impact for stored types.
type substitutability
One of the key limitations of Oracle's type implementation was the lack of polymorphism and inheritance between objects. This has been addressed in Oracle 9i Release 1 (9.0) and we can now create "subtypes" using the new support for type substitution (which includes inheritance and polymorphism as we will see). This provides for an extremely rich and flexible development environment that can improve the quality of the PL/SQL applications we produce. We will see how later in this article, but will begin with the mechanics of substitutable types.
Note that when we talk of type substitution or polymorphism, we are describing the ability of subtypes to be used in place of any of their supertypes. Inheritance refers to the automatic inclusion of a supertype's attributes and methods in all of its subtypes (i.e. a subtype inherits from the supertype). A subtype can optionally retain or override the attributes and methods it inherited from its supertype.
creating substitutable types
To demonstrate substitutable types, we will move away from our example OT type and create a new SUPERTYPE_OT as follows. This type has one attribute and one member function (which will be useful in the examples later on).
SQL> CREATE TYPE supertype_ot AS OBJECT 2 ( attr1 NUMBER, 3 MEMBER FUNCTION print RETURN VARCHAR2 4 ) 5 NOT FINAL NOT INSTANTIABLE; 6 /
Type created.
Note that we have introduced two additional elements in the syntax. These are:
- NOT FINAL: we are specifying that our type definition is incomplete and that we will be creating subtypes to inherit from this one; and
- NOT INSTANTIABLE: we will not be able to create any instances of this type directly. This is optional and can be used whenever we decide that all functionality will be implemented by the subtypes. We will not implement any functionality directly through our SUPERTYPE_OT.
Because we are not implementing any functionality through the SUPERTYPE_OT (we have specified NOT INSTANTIABLE), we do not need to create a type body for the PRINT member function. Instead, we can move on to create our first substitutable type, SUBTYPE_OT, as follows.
SQL> CREATE TYPE subtype_ot UNDER supertype_ot 2 ( attr2 DATE, 3 attr3 VARCHAR2(1), 4 OVERRIDING MEMBER FUNCTION print RETURN VARCHAR2 5 ); 6 /
Type created.
There are three important points to note about this type, as follows.
- UNDER: using this we specify that the SUBTYPE_OT object type is a subtype of SUPERTYPE_OT. We can continue to create subtypes under subtypes in this way if necessary, building potentially complex type hierarchies;
- inheritance: subtypes inherit attributes and methods from their supertypes. In our case, SUBTYPE_OT has inherited ATTR1 from SUPERTYPE_OT. The additional attributes, ATTR2 and ATTR3, are specific to SUBTYPE_OT (and any further types we might create under it); and
- OVERRIDING: subtypes inherit methods from the their supertypes. The OVERRIDING keyword instructs Oracle to use the subtype's version of a method rather than the version it inherits. In our example, we have created SUPERTYPE_OT as NOT INSTANTIABLE so we did not bother to create a working PRINT method. In this case we have to create an overriding member in each of the subtypes, else they will have no functionality.
Because we have a member function to implement in the SUBTYPE_OT, we will now create its type body as follows. The PRINT method will simply return a string of the values in an instance of SUBTYPE_OT and also label it with the type name for clarity in later examples.
SQL> CREATE TYPE BODY subtype_ot AS 2 3 OVERRIDING MEMBER FUNCTION print RETURN VARCHAR2 IS 4 BEGIN 5 RETURN '[SUBTYPE_OT] ' || 6 TO_CHAR(SELF.attr1) || ',' || 7 TO_CHAR(SELF.attr2) || ',' || 8 SELF.attr3; 9 END; 10 END; 11 /
Type body created.
We will now create a second subtype, named ANOTHER_SUBTYPE_OT. Again, this will be created under the SUPERTYPE_OT (we will not create any complex hierarchies in this introductory article). This subtype will have an additional three attributes, ATTR2-4.
SQL> CREATE TYPE another_subtype_ot UNDER supertype_ot 2 ( attr2 NUMBER, 3 attr3 NUMBER, 4 attr4 NUMBER, 5 OVERRIDING MEMBER FUNCTION print RETURN VARCHAR2 6 ); 7 /
Type created.
SQL> CREATE TYPE BODY another_subtype_ot AS 2 3 OVERRIDING MEMBER FUNCTION print RETURN VARCHAR2 IS 4 BEGIN 5 RETURN '[ANOTHER_SUBTYPE_OT] ' || 6 TO_CHAR(SELF.attr1) || ',' || 7 TO_CHAR(SELF.attr2) || ',' || 8 TO_CHAR(SELF.attr3) || ',' || 9 TO_CHAR(SELF.attr4); 10 END; 11 END; 12 /
Type body created.
working with substitutable types
We will now see some examples of our substitutable types in use. Note that all examples are in PL/SQL. It was noted earlier that experience has shown that types are excellent for PL/SQL but problematic in tables, so we will continue on this premise.
First, we will attempt to use the SUPERTYPE_OT type directly. Remember that we created this type as NOT INSTANTIABLE.
SQL> DECLARE
2 o supertype_ot;
3 BEGIN
4 o := supertype_ot(1);
5 END;
6 /
o := supertype_ot(1);
*
ERROR at line 4:
ORA-06550: line 4, column 9:
PLS-00713: attempting to instantiate a type that is NOT INSTANTIABLE
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored
This error message is clear and demonstrates that we do not have to worry that the supertype will be incorrectly used. We can now see a simple example of type substitution. In the following example, we will create a variable of SUPERTYPE_OT, but use this to assign an instance of each of our subtypes to it.
SQL> DECLARE 2 o supertype_ot; 3 BEGIN 4 o := subtype_ot(1, DATE '1900-01-01', 'A'); 5 o := another_subtype_ot(1,2,3,4); 6 END; 7 /
PL/SQL procedure successfully completed.
Of course, the second assignment overwrites the first, but the example was designed to simply show how we can assign subtypes to supertypes. In the following example, we will move a stage further by showing how the overriding subtype methods can be invoked from "within" a variable of the supertype.
SQL> DECLARE 2 PROCEDURE output( p IN supertype_ot ) IS 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(p.print); 5 END output; 6 BEGIN 7 output( subtype_ot(1, DATE '1900-01-01', 'A') ); 8 output( another_subtype_ot(1,2,3,4) ); 9 END; 10 /
[SUBTYPE_OT] 1,01-JAN-00,A [ANOTHER_SUBTYPE_OT] 1,2,3,4 PL/SQL procedure successfully completed.
Note how the OUTPUT procedure accepts a parameter of SUPERTYPE_OT, yet we were able to pass in either of the subtypes. Oracle was then able to invoke the correct overriding PRINT method from each substituted type instance we passed in. This example gives us our first hint of how type substitution can be used to build "generic" utilities without using techniques such as overloading or dynamic PL/SQL.
treat function
When we use type substitution, Oracle does not allow us to directly access any attributes or methods that are not available in the supertype. We can see this below when we assign an instance of SUBTYPE_OT into a variable of its supertype and attempt to access a subtype-only attribute.
SQL> DECLARE
2 o supertype_ot := subtype_ot(1, DATE '1900-01-01', 'A');
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('ATTR1='||o.attr1);
5 DBMS_OUTPUT.PUT_LINE('ATTR3='||o.attr3);
6 END;
7 /
DBMS_OUTPUT.PUT_LINE('ATTR3='||o.attr3);
*
ERROR at line 5:
ORA-06550: line 5, column 37:
PLS-00302: component 'ATTR3' must be declared
ORA-06550: line 5, column 4:
PL/SQL: Statement ignored
All is not lost, however, as the TREAT function was introduced in 9i to deal with this problem. Using the TREAT function, we are able to "downcast" an object instance to its associated subtype. Once we do this, we can access the subtype's specific attributes and methods in addition to those it inherited, as we can see in the following example.
SQL> DECLARE
2 o supertype_ot := subtype_ot(1, DATE '1900-01-01', 'A');
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('ATTR3='||TREAT(o AS subtype_ot).attr3);
5 END;
6 /
ATTR3=A PL/SQL procedure successfully completed.
Note that TREAT is not available directly in PL/SQL (as above) until Oracle 9.2. In Oracle 9.0, we would have to use a SELECT INTO construct, fetching into a variable of the relevant subtype.
working with multiple subtypes
So far, we have seen examples of type substitution using a single variable. We will now see some examples involving multiple records with multiple subtypes. We could easily create a table and fill it with some rows to do this, but in keeping with the "types are good for PL/SQL" theme, we will do this using a pipelined function.
If a supertype can be substituted by an instance of one of its subtypes, it follows that a collection of this supertype can contain multiple instances of its subtypes. On this basis, we will create a collection type based on SUPERTYPE_OT as follows.
SQL> CREATE TYPE supertype_ntt AS 2 TABLE OF supertype_ot; 3 /
Type created.
Since Oracle 8.0, it has been possible to select from functions that return collections. In 9i, Oracle improved this dramatically by introducing pipelined functions to stream data back to the consumer, rather than fill an entire collection first. We will use this principle to create a simple pipelined table function to return a hard-coded collection of five substituted supertype instances, as follows.
SQL> CREATE FUNCTION f RETURN supertype_ntt PIPELINED AS 2 BEGIN 3 PIPE ROW (subtype_ot(1, SYSDATE, 'A')); 4 PIPE ROW (another_subtype_ot(1,2,3,4)); 5 PIPE ROW (subtype_ot(2, SYSDATE, 'B')); 6 PIPE ROW (another_subtype_ot(2,3,4,5)); 7 PIPE ROW (subtype_ot(3, SYSDATE, 'C')); 8 RETURN; 9 END f; 10 /
Function created.
As we saw with our single-instance example earlier, Oracle will only give us access to the inherited attributes unless we intervene. This is even true in SQL with a "SELECT *" style query as below.
SQL> SELECT t.* 2 FROM TABLE(f) t;
ATTR1
---------
1
1
2
2
3
5 rows selected.
The pipelined function is simply a datasource for us to select from and in this case is no different from retrieving rows from a table. We have already seen how the TREAT function can cast the type instance to its correct subtype to give us access to its specific attributes. When querying a rowsource such as a pipelined function or a table, we can alternatively use the VALUE function. This function takes an alias for the rowsource and returns the full object instances. This is best described by example, as follows.
SQL> SELECT VALUE(t) AS object_instance 2 FROM TABLE(f) t;
OBJECT_INSTANCE(ATTR1) ---------------------------------------- SUBTYPE_OT(1, '13-DEC-05', 'A') ANOTHER_SUBTYPE_OT(1, 2, 3, 4) SUBTYPE_OT(2, '13-DEC-05', 'B') ANOTHER_SUBTYPE_OT(2, 3, 4, 5) SUBTYPE_OT(3, '13-DEC-05', 'C') 5 rows selected.
Ignoring the fact that sqlplus has behaved strangely with our column alias, we can see now that we have the full subtype instance for each row that we hard-coded into the pipelined function. We now have access to the subtype's attributes and with a little more work, we can be more specific about how we use each row of data. For this we require some type metadata and Oracle provides various functions to provide this, as we will see in the next section below.
Before we continue, however, we will create a view of the SQL statement used in the previous example. This will simply prevent us having to code VALUE(t) everywhere we wish to reference a type column.
SQL> CREATE VIEW v 2 AS 3 SELECT VALUE(t) AS o 4 FROM TABLE(f) t;
View created.
type metadata
As stated, Oracle 9i provides some additional functions and conditions to help us recognise the subtypes we are retrieving. These are:
- IS OF TYPE condition;
- IS OF (ONLY) condition; and
- SYS_TYPEID function.
Note that these are are all available in SQL in Oracle 9.0. The IS OF conditions are available in "direct PL/SQL" (i.e. IF/CASE statements) in Oracle 9.2. To use any of these in PL/SQL in 9.0 (or the SYS_TYPEID function in 9.2 PL/SQL), we need a "SELECT INTO FROM DUAL" construct.
Starting at the top of this list, we can see how we can use the IS OF conditions to filter the data we return by requesting rows of a specific type.
SQL> SELECT * 2 FROM v 3 WHERE o IS OF TYPE (subtype_ot);
O(ATTR1) ---------------------------------------- SUBTYPE_OT(1, '13-DEC-05', 'A') SUBTYPE_OT(2, '13-DEC-05', 'B') SUBTYPE_OT(3, '13-DEC-05', 'C') 3 rows selected.
Note that the TYPE keyword is optional and we can specify more than one type in the parentheses (separated by comma). If we asked for SUPERTYPE_OT, we would return all data, as every subtype is also semantically an instance of its supertype. If we wish to restrict to a specific type only and filter out any of its subtypes, we use the IS OF (ONLY) condition as follows.
SQL> SELECT * 2 FROM v 3 WHERE o IS OF (ONLY another_subtype_ot);
O(ATTR1) ---------------------------------------- ANOTHER_SUBTYPE_OT(1, 2, 3, 4) ANOTHER_SUBTYPE_OT(2, 3, 4, 5) 2 rows selected.
Of course, ANOTHER_SUBTYPE_OT doesn't have any subtypes, so IS OF and IS OF (ONLY) are semantically the same in this case. If we filter on only SUPERTYPE_OT, however, we will see no rows returned as we know that there are no instances of this supertype. We can see this below.
SQL> SELECT * 2 FROM v 3 WHERE o IS OF (ONLY supertype_ot);
no rows selected
Moving on, we also have a SYS_TYPEID function to return an identifier within a type hierarchy. This seems to be assigned in the order in which the types are created, with the supertype being the first, which we can see as follows.
SQL> SELECT SYS_TYPEID(o) AS type_id 2 , o AS object_instance 3 FROM v;
TYPE_ID OBJECT_INSTANCE(ATTR1) ------- ---------------------------------------- 02 SUBTYPE_OT(1, '13-DEC-05', 'A') 03 ANOTHER_SUBTYPE_OT(1, 2, 3, 4) 02 SUBTYPE_OT(2, '13-DEC-05', 'B') 03 ANOTHER_SUBTYPE_OT(2, 3, 4, 5) 02 SUBTYPE_OT(3, '13-DEC-05', 'C') 5 rows selected.
Oracle suggests that this function is useful for creating function-based indexes on type columns, and outside of this it is probably of little use (unless we specifically know the ID of each type).
recognising subtypes
Because we can limit records from a rowsource to a specific subtype, we can therefore access its attributes with a degree of confidence (i.e. we know which attributes exist in a specific subtype). For this we return to the TREAT function as follows.
SQL> SELECT TREAT(o AS subtype_ot) AS treated_subtype 2 FROM v 3 WHERE o IS OF (ONLY subtype_ot);
TREATED_SUBTYPE(ATTR1, ATTR2, ATTR3) ---------------------------------------- SUBTYPE_OT(1, '13-DEC-05', 'A') SUBTYPE_OT(2, '13-DEC-05', 'B') SUBTYPE_OT(3, '13-DEC-05', 'C') 3 rows selected.
Once a supertype instance has been cast to its actual subtype, we can then access its attributes, as follows.
SQL> SELECT TREAT(o AS subtype_ot).attr1 AS attr1 2 , TREAT(o AS subtype_ot).attr2 AS attr2 3 , TREAT(o AS subtype_ot).attr3 AS attr3 4 FROM v 5 WHERE o IS OF TYPE (subtype_ot);
ATTR1 ATTR2 ATTR3
--------- --------- -----
1 13-DEC-05 A
2 13-DEC-05 B
3 13-DEC-05 C
3 rows selected.
Taking this a stage further, we can start to combine the IS OF and TREAT functions to understand our subtype data more dynamically . In the following example, we know we have two subtypes coming out of our rowsource (i.e. SUBTYPE_OT and ANOTHER_SUBTYPE_OT). Based on this knowledge, we can determine which of the two types we have on any given row and deal with it appropriately, as follows.
SQL> SELECT CASE 2 WHEN o IS OF TYPE (subtype_ot) 3 THEN 'SUBTYPE_OT' 4 ELSE 'ANOTHER_SUBTYPE_OT' 5 END AS record_type 6 , TREAT(o AS subtype_ot) AS subtype_record 7 , TREAT(o AS another_subtype_ot) AS another_subtype_record 8 FROM v;
RECORD_TYPE SUBTYPE_RECORD(ATTR1, ATTR2, ATTR3) ANOTHER_SUBTYPE_RECORD(ATTR1, ATT ------------------ ------------------------------------ --------------------------------- SUBTYPE_OT SUBTYPE_OT(1, '13-DEC-05', 'A') ANOTHER_SUBTYPE_OT ANOTHER_SUBTYPE_OT(1, 2, 3, 4) SUBTYPE_OT SUBTYPE_OT(2, '13-DEC-05', 'B') ANOTHER_SUBTYPE_OT ANOTHER_SUBTYPE_OT(2, 3, 4, 5) SUBTYPE_OT SUBTYPE_OT(3, '13-DEC-05', 'C') 5 rows selected.
Note how the TREAT function returns NULL for those type instances it cannot downcast correctly. We can take advantage of this in a final example before we move on. The following example is a hint at what we can achieve with pipelined functions and multiple object subtypes. This will be the subject of a future article.
SQL> SELECT ilv.record_type AS record_type 2 , ilv.subtype_record.attr1 AS st_attr1 3 , ilv.subtype_record.attr2 AS st_attr2 4 , ilv.subtype_record.attr3 AS st_attr3 5 , ilv.another_subtype_record.attr1 AS ast_attr1 6 , ilv.another_subtype_record.attr2 AS ast_attr2 7 , ilv.another_subtype_record.attr3 AS ast_attr3 8 , ilv.another_subtype_record.attr4 AS ast_attr4 9 FROM ( 10 SELECT CASE 11 WHEN o IS OF TYPE (subtype_ot) 12 THEN 'SUBTYPE' 13 ELSE 'ANOTHER SUBTYPE' 14 END AS record_type 15 , TREAT(o AS subtype_ot) AS subtype_record 16 , TREAT(o AS another_subtype_ot) AS another_subtype_record 17 FROM v 18 ) ilv;
RECORD_TYPE ST_ATTR1 ST_ATTR2 ST_ATTR3 AST_ATTR1 AST_ATTR2 AST_ATTR3 AST_ATTR4 ------------------ --------- --------- --------- --------- --------- --------- --------- SUBTYPE 1 13-DEC-05 A ANOTHER SUBTYPE 1 2 3 4 SUBTYPE 2 13-DEC-05 B ANOTHER SUBTYPE 2 3 4 5 SUBTYPE 3 13-DEC-05 C 5 rows selected.
This example highlights the flexibility of type substitution. We are able to retrieve more than one "record type" from a rowsource and access different attributes accordingly.
determining a type's name
With the release of 9i, Oracle also introduced some generic self-describing types, including one called ANYDATA. Using ANYDATA, we can determine the name of our subtype programmatically without relying on IS OF functions. In the following example we will create a function that converts an instance of our supertype into an instance of ANYDATA. Once converted to ANYDATA, it is possible to determine the original type's name using a "GETTYPENAME" static method as follows.
SQL> CREATE FUNCTION name_that_subtype( 2 p_supertype IN supertype_ot 3 ) RETURN VARCHAR2 IS 4 v_supertype supertype_ot := p_supertype; 5 BEGIN 6 RETURN ANYDATA.convertObject(v_supertype).getTypeName(); 7 END name_that_subtype; 8 /
Function created.
We will now use this function for our pipelined rowsource of subtype instances as follows.
SQL> SELECT name_that_subtype(o) AS subtype_name 2 , o AS subtype_instance 3 FROM v;
SUBTYPE_NAME SUBTYPE_INSTANCE(ATTR1) ------------------------------ --------------------------------- SCOTT.SUBTYPE_OT SUBTYPE_OT(1, '13-DEC-05', 'A') SCOTT.ANOTHER_SUBTYPE_OT ANOTHER_SUBTYPE_OT(1, 2, 3, 4) SCOTT.SUBTYPE_OT SUBTYPE_OT(2, '13-DEC-05', 'B') SCOTT.ANOTHER_SUBTYPE_OT ANOTHER_SUBTYPE_OT(2, 3, 4, 5) SCOTT.SUBTYPE_OT SUBTYPE_OT(3, '13-DEC-05', 'C') 5 rows selected.
practical uses for substitutable types
We have concentrated on the mechanics of inheritance and polymorphism in this section of the article. In addition, we have largely ignored tables as a rowsource for object type data. This is intentional for the reasons previously stated. The use of a pipelined function example introduces one of two practical uses for type substitution, which are as follows.
- returning multiple record types from pipelined functions. Many ETL processes are written in PL/SQL loops which typically fetch a row of data from a staging table, transform it, assign it to multiple target records and load multiple tables one-by-one, record-by-record. These solutions are common, simple to code but very inefficient. These solutions are also extremely simple to convert to make use of newer, faster ETL features including pipelined functions, multi-table inserts and type polymorphism. For example, we can turn the PL/SQL loop into a parallel pipelined function that returns a collection of a supertype. Each record for each table loaded in the original loop is represented by a subtype of the supertype. Each insert statement in the loop is changed to a PIPE ROW command in the new pipelined function. Finally, a single multi-table insert can load all tables from a single, bulk select from the pipelined function for dramatic performance improvements. See this article for an example;
- building generic utilities. Once we are comfortable with the principle of type substitution, it is quite simple to conceive of ways in which we can take advantage of the ability to pass multiple data structures into a single procedure or function. A good example is a generic error-handler for the new FORALL SAVE EXCEPTIONS clause (a 9i new feature described in this oracle-developer.net article). To post-process the loading errors saved by Oracle when we use the SAVE EXCEPTIONS clause requires a lot of code. To avoid repeating this across every procedure that uses SAVE EXCEPTIONS, we can easily encapsulate the error-handling in a utility that accepts a single collection of a supertype. We can represent all target tables with an associated subtype. We can also include a single method in each subtype to return the information we would wish to log in the event of an error (for example, the primary key columns of a record). On hitting the SAVE EXCEPTIONS event, we can pass the subtype collection to a single handler for logging or processing. See this article for an example.
further reading
For further information on object-relational features of Oracle 9i, see the Application Developer's Guide. New features such as type evolution, constructors, polymorphism and inheritance can be found in the What's New? chapter of the same book. For more information on the TREAT function and IS OF conditions, read the new features summary in the SQL Reference. Finally, for full details of the ALTER TYPE statement, see the syntax diagram in the online SQL Reference.
acknowledgements
Thanks to James Padfield for his assistance with substitutable types. Credit goes to Zlatco Sirotic for the original idea to use ANYDATA to determine a type's name.
source code
The source code for the examples in this article can be downloaded from here.
Adrian Billington, December 2005 (updated June 2007)
Back to Top