During the Q&A at the end Oracle's Sue Harper asked a pertinent question. When she had worked on Oracle Designer they had introduced the ability to work with Types but there had been no real take-up amongst the Designer users. Was this because the Oracle Type implementation was not very good or was it that nobody really knew what to do with Types? In my opinion the answer is both.
This article is an extended (two part) response to Sue's question. In this part I will briefly describe Oracle SQL Types; I am not trying to provide a substitute for the documentation, I outline what I think are the shortcomings with the existing Oracle Implementation which may deter people from using them. In the second part I will I have addressed Sue's request for a simple yet non-trivial example of the advantages to programming with Types.
An old COBOL hack explains OO programming in less than 200 words
In procedural programming we have data in tables and executable code in packages. In Object-Oriented programs we have objects which combine data (attributes) with procedural code (methods) to manipulate that data. OO programming has three principles:Encapsulation. By gathering code and related data into a single object we shield the application developer from the gnarly details of our implementation. We all understand encapsulation: PL/SQL packages do this very well.
Inheritance. We can define a hierarchy of objects starting with the general and getting more specific. For instance we might have a type called Customer to represent generic behaviours and extend that to two sub-types CompanyCustomer and IndividualCustomer to model more specialised behaviours. We can add new attributes and methods in the sub-types and also override methods defined higher up the hierarchy.
Polymorphism. This is the ability to treat an object as different types according to our needs. For instance we can instantiate an IndividualCustomer object and pass it to a program which takes a Customer object. In my opinion it is polymorphism and substitutability which make programming with types an interesting proposition.
A quick note on terminology. The Object-Oriented vocabulary is ripe for confusion. I will use Type to as shorthand for Oracle SQL TYPE (also known as User Defined Types or Complex Data Types). A Type is a definition of something (other OO languages use Class to signify the same thing). When I use object I am talking about an instance of a Type, that is, an actual thing populated with data. Unless of course I am talking about objects in the regular Oracle meaning of tables, sequences, etc. :D
A brief history of TYPE
Oracle introduced Types in 8.0, branding the product as an Object-Relational database. You will no doubt be shocked to learn that the whole ORDBMS thang was largely marketing. The initial Type offering was very incomplete, lacking inheritance and polymorphism. So experienced OO programmers looking at Oracle 8.0 found so much missing that they could not work in PL/SQL and experienced PL/SQL programmers couldn't see what all the fuss was about. Oracle 8i added Java as an option for writing the code behind the methods but otherwise didn't extend the OO functionality.In Oracle 9i we were given the inheritance and polymorphism. We also gained the ability to evolve our type definitions with the ALTER TYPE syntax. In Oracle 8 the only option at our disposal was the CREATE OR REPLACE TYPE syntax, which hurled with types that had dependents (other types and tables). The constant need to drop dependent objects was a major impediment to building APIs with types. The other major impediment was relieved in 9iR2 when Oracle gave us the ability to define our own constructor functions. The constructor is the method called when we instantiate an object. This is crucial because the constructor is the place to set default values and apply business rules. So really it only was with Oracle 9iR2 that Types became a viable programming option.
No additional object-relational features were added in Oracle 10g. Does this mean we now have a complete OO implementation? Heck no!
What remains to be done
We cannot create types that consist of methods only...SQL> CREATE OR REPLACE TYPE abstract_type AS OBJECT
2 ( MEMBER PROCEDURE do_this
3 , MEMBER PROCEDURE do_that
4 , MEMBER FUNCTION get_whatever RETURN varchar2)
5 NOT FINAL NOT INSTANTIABLE;
6 /
Warning: Type created with compilation errors.
SQL> Show errors
Errors for TYPE ABSTRACT_TYPE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1 PLS-00589: no attributes found in object type "ABSTRACT_TYPE"
SQL>
So we cannot create abstract types or interfaces. Furthermore we cannot have private variables in our type bodies ...
SQL> CREATE OR REPLACE TYPE simple_type AS OBJECT
2 ( attr number
3 , MEMBER PROCEDURE keep_count)
4 NOT FINAL;
5 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY simple_type
2 AS
3 private_attr number ;
4
5 MEMBER PROCEDURE keep_count
6 IS
7 BEGIN
8 private_attr := private_attr+1;
9 END keep_count;
10 END:
11 /
Warning: Type Body created with compilation errors.
SQL> Show errors
Errors for TYPE BODY SIMPLE_TYPE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/5 PLS-00103: Encountered the symbol "PRIVATE_ATTR" when expecting
one of the following:
not final instantiable order overriding static member
constructor map
SQL>
Nor can we have private functions ...
SQL> CREATE OR REPLACE TYPE BODY simple_type
2 AS
3 MEMBER FUNCTION cntr
4 RETURN number
5 IS
6 private_attr number ;
7 BEGIN
8 private_attr := private_attr+1;
9 RETURN private_attr;
10 END;
11 MEMBER PROCEDURE keep_count
12 IS
13 BEGIN
14 SELF.attr := SELF.cntr();
15 END keep_count;
16 END;
17 /
Warning: Type Body created with compilation errors.
SQL> Show errors
Errors for TYPE BODY SIMPLE_TYPE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/21 PLS-00539: subprogram 'CNTR' is declared in an object type body
and must be defined in the object type specification
SQL>
There is also a minor problem with OVERRIDING methods in subtypes. We lack a mechanism for inheriting behaviour from the parent's implementation (equivalent to Java's super() command)1. The upshot is, we cannot properly encapsulate our business logic inside our types. If we want to have private attributes or private methods we have to use tables and PL/SQL packages, which rather defeats the purpose.
So I hope this answers the first part of Sue's question. It has taken a long time for Oracle to provide a viable Type implementation and even now it is incomplete. So that is a possible explanantion for the low take-up of programming with Types. But are programmers missing a trick? This is the question I will attempt to answer in Part 2.
2 comments:
excellent.
I hope I had read it long time along before I played with the ugly Oracle OO features myself and found those restrictions finaly came to the conclusion do not use Oralce OO in my project.
The super() keyword unfortunately never made it in. But you can do the same thing since 11.1 with Generalized Invocation. For example, (SELF AS super_typ).show
Post a Comment