Comment (0)

PL/SQL is a procedural language — mostly. But it can also be used to implement object-oriented features in Oracle Database. In "from the ground up" object-oriented languages like Java, everything is defined in classes. In Oracle Database, we have object types.

In this post, I introduce you to the basics of object types and kick off a series exploring many of the features of these programming elements.

You do not need to be an expert in object orientation to understand the basics of object types. I know this for a fact because I have no formal training in object orientation and was able to sort out at least the basics.

Before proceeding though, you might wonder when you would want to use object types. Use cases include:

• You’d like to apply object-oriented principles in your PL/SQL-based development. This makes sense if you are an experienced O-O developer and new to PL/SQL.
• You want to use the many features of Oracle Database that rely on object types for their implementation, such as Oracle Spatial.
• You are building a pipelined table function that returns more than one column per row, and you are not yet running an Oracle Database 18c or higher database. In this case, you must use a nested table of object type instances (explained below); you cannot use record types.

A great example of an O-O implementation using object types is utPLSQL v3. This is one of the most active and popular open source PL/SQL projects. It helps you automate testing of all kinds of PL/SQL program units. Check it out!

Here’s a quick summary of some terminology relevant to object orientation:

• Attributes: tables have columns, records have fields. Object types have attributes (and there’s no such thing as a private attribute).
• Methods: packages have procedures and function. Object types have methods (which are procedures or functions).
• Inheritance: you can create object types that are subtypes of other types. A subtype inherits attributes and methods from the parent(s).
• Constructors: functions that return a new instantiation of a type. Oracle provides a pre-defined constructor; you can also "roll your own."

I could add more complex terminology like dynamic polymorphism, but then you might just decide to read a different post. So we explore more interesting and complex topics like that one later.

The best way to learn new elements of a programming language is to look at and write code, so let’s dive right in with some examples.

I don’t know about you, but I like to eat. And even if you don’t like eating as much as I do, you need to eat. So I am going to build a little hierarchy of types and subtypes related to food.

I start with the "root" type. Not surprisingly, I will call it "food" and add a suffix of "ot" to indicate it’s an object type. I suppose it’s my "relational roots," but I like to keep un-suffixed names reserved for relational tables, so I might have a food table and a food_ot object type. You, of course, are welcome and encouraged to come up with and follow whatever standards suit you and your team.

Here is the food object type:

CREATE TYPE food_ot AS OBJECT (    name        VARCHAR2(100),    food_group  VARCHAR2 (50),    grown_in    VARCHAR2 (100)    )    NOT FINAL;

It looks a lot like a table creation statement, doesn’t it? For example:

CREATE TABLE food_ot (    name        VARCHAR2(100),    food_group  VARCHAR2 (50),    grown_in    VARCHAR2 (100)    )

Very similar. Of course, a table does not have "NOT FINAL" clauses, but before I address that, let’s talk about the differences between a table and an object type.

A table is a container for rows of data. So the table creation statement lists the columns and their datatypes. Of course, it can include much more, such as specifications of primary and foreign keys (although usually these are added using their own separate DDL statements).

The bottom line, though, is that you create a table so you can stuff it full of data with insert statements, modify that data with update statements, remove rows with delete statements, and query data with select statements.

An object type, on the other hand, is a description of a type of data (I suppose that’s why it is called a type). It does not contain anything. You could, in fact, use an object type as the type of a column in a relational table!

So an object type is a type just like a record type is a type. Which means you can declare variables based on the type. A variable declared from a record type is a record. A variable declared or instantiated from an object type is called….wait for it….an object type instance.

Yes, I suppose it should be called an object. But we use the term "object" or "database object" to refer to a wide variety of things stored in the database. So: object type instance.

OK, so I’ve created my type. What can I do with it? Let’s declare an instance and try to use it. But first…since I mentioned that an object type is like a record type, let’s take a look at the kind of code I would write for a record type.

DECLARE    TYPE food_rt IS RECORD (       name         VARCHAR2 (100),       food_group   VARCHAR2 (50),       grown_in     VARCHAR2 (100)    );     my_favorite_vegetable   food_rt; BEGIN    my_favorite_vegetable.name := 'Brussels Sprouts';    DBMS_OUTPUT.put_line (       my_favorite_vegetable.name || ' are yummy!'); END; /  Brussels Sprouts are yummy!

And now I will simply use the object type instead of record type.

DECLARE    my_favorite_vegetable   food_ot; BEGIN    my_favorite_vegetable.name := 'Brussels Sprouts';    DBMS_OUTPUT.put_line (       my_favorite_vegetable.name || ' are yummy!'); END; /  ORA-06530: Reference to uninitialized composite

Uh oh. And here you see your first glimpse into how object types are handled differently than non-object-oriented elements of PL/SQL.

With object type instances, just like with nested tables and varrays (all interested as part of the evolution to a object-relational database in Oracle8), you must initialize the instance before you can work with it. You initialize it by calling a constructor function:

DECLARE    my_favorite_vegetable   food_ot       := food_ot ('Brussels Sprouts', 'Vegetables', 'Dirt'); BEGIN    DBMS_OUTPUT.put_line (       my_favorite_vegetable.name || ' are yummy!'); END; /  Brussels Sprouts are yummy!

And in case you are aware of named notation and wondering if you can use it in a constructor…you bet!

DECLARE    my_favorite_vegetable   food_ot       := food_ot (name => 'Brussels Sprouts',                    food_group => 'Vegetables',                    grown_in => 'Dirt'); BEGIN    DBMS_OUTPUT.put_line (       my_favorite_vegetable.name || ' are yummy!'); END; /  Brussels Sprouts are yummy!

What if, however, you wanted to be able initialize a new food instance with just the name? In that case you will want to build your own constructor, by adding code to the object type. This means you will also need an object type body (and now you will see how object types also resemble packages).

So I will re-create the type (another way that an object type is different from a table: you can use CREATE OR REPLACE — as long as other database objects do not depend on that type), and add two constructor functions: one that has no parameters and one that requires only the name to be provided.

CREATE OR REPLACE TYPE food_ot AS OBJECT (    name VARCHAR2 (100),    food_group VARCHAR2 (50),    grown_in VARCHAR2 (100),    CONSTRUCTOR FUNCTION food_ot       RETURN SELF AS RESULT,    CONSTRUCTOR FUNCTION food_ot (NAME_IN IN VARCHAR2)       RETURN SELF AS RESULT )    NOT FINAL; /  CREATE OR REPLACE TYPE BODY food_ot IS    CONSTRUCTOR FUNCTION food_ot       RETURN SELF AS RESULT    IS    BEGIN       RETURN;    END;     CONSTRUCTOR FUNCTION food_ot (NAME_IN IN VARCHAR2)       RETURN SELF AS RESULT    IS    BEGIN       SELF.name := NAME_IN;       RETURN;    END; END; /

"SELF" is a keyword that indicates you are working with attributes and methods of the instance that is being "constructed" by the function.

Now I can initialize my instance in either of these two "styles", plus the default constructor (in which a value for each attribute must be specified — thanks to Richard Martens for pointing this out):

DECLARE    my_favorite_vegetable food_ot       := food_ot ('Brussels Sprouts'); BEGIN    DBMS_OUTPUT.put_line (       my_favorite_vegetable.name || ' are yummy!'); END; /  DECLARE    my_favorite_vegetable food_ot := food_ot (); BEGIN    my_favorite_vegetable.name := 'Brussels Sprouts';    DBMS_OUTPUT.put_line (       my_favorite_vegetable.name || ' are yummy!'); END; /  DECLARE    my_favorite_vegetable food_ot        := food_ot ('Brussels Sprouts', 'Vegetables', 'Dirt'); BEGIN    DBMS_OUTPUT.put_line (       my_favorite_vegetable.name || ' are yummy!'); END; /

OK, so that gets us up and running with declaring and assigning values to an object type instance.

So about that NOT FINAL clause in the food type definition?

You include that clause when you want to indicate that you plan to create subtypes of that supertype (in this case, also the root type): when you want to build an object type hierarchy. That’s where object-oriented features get really interesting and I will cover that in my next blog post.

All the code shown in this post is available to view and execute on LiveSQL.

And Now for Some Arcane Explorations…

A comment on this post by Vasily Suvorov points out that "You can also overwrite default constructor. It’s rather funny :)" I never even thought of doing that so I decided I’d check it out.

Below is a summary of what I found. This code is also in the LiveSQL script and shown below.

My findings:

• You can create user-defined constructors with parameter lists that match that of the default (same number and type).
• You will probably run into "PLS-00307: too many declarations of ‘type_name‘ match this call" errors.
• You can avoid these errors by using named notation – unless your parameter names match the attribute names.
• In this case, you will effectively override the default constructor and only yours will be called.
• The only valid reason I can see for doing this is you need to execute special initialization logic for your object type instances.
• Otherwise, make sure your parameter list is markedly different from that of the default constructor.

First, I re-create the type including a constructor that has three parameters with the same datatypes as the object type attributes. Notice that I have swapped the values for food group and grown in. That way it will be easy to tell which constructor was invoked.

CREATE OR REPLACE TYPE food_ot     AS OBJECT  (     name VARCHAR2 (100),     food_group VARCHAR2 (50),     grown_in VARCHAR2 (100),     CONSTRUCTOR FUNCTION food_ot (name_in         IN VARCHAR2,                                   food_group_in   IN VARCHAR2,                                   grown_in_in     IN VARCHAR2)        RETURN SELF AS RESULT  )     NOT FINAL;  /  CREATE OR REPLACE TYPE BODY food_ot   IS      CONSTRUCTOR FUNCTION food_ot (name_in         IN VARCHAR2,                                    food_group_in   IN VARCHAR2,                                    grown_in_in     IN VARCHAR2)         RETURN SELF AS RESULT      IS      BEGIN         self.name := name_in;         self.food_group := grown_in_in;         self.grown_in := food_group_in;         RETURN;      END;   END; /

When I try to use a constructor in the "normal" style, it doesn’t work. The PL/SQL ending cannot distinguish between the default constructor and mine.

DECLARE     my_favorite_vegetable   food_ot        := food_ot ('Brussels Sprouts', 'Vegetables', 'Dirt');  BEGIN     DBMS_OUTPUT.put_line (           my_favorite_vegetable.name        || ' are grown in '        || my_favorite_vegetable.grown_in);  END;  /  ORA-06550: line 3, column 10: PLS-00307: too many declarations of 'FOOD_OT' match this call

But if I use named notation, it works — since the names of the parameters are different from the attribute names.

CREATE TABLE food_ot (    name        VARCHAR2(100),    food_group  VARCHAR2 (50),    grown_in    VARCHAR2 (100)    )

0

But I use the attribute names in my named notation syntax, the default constructor is called.

CREATE TABLE food_ot (    name        VARCHAR2(100),    food_group  VARCHAR2 (50),    grown_in    VARCHAR2 (100)    )

1

Now I will re-create the type with a constructor whose parameter names match the attribute names.

CREATE TABLE food_ot (    name        VARCHAR2(100),    food_group  VARCHAR2 (50),    grown_in    VARCHAR2 (100)    )

2

With named notation, it calls my constructor (notice the value for grown-in).

CREATE TABLE food_ot (    name        VARCHAR2(100),    food_group  VARCHAR2 (50),    grown_in    VARCHAR2 (100)    )

3

And, for the grand finale, even without named notation, it calls my constructor.

CREATE TABLE food_ot (    name        VARCHAR2(100),    food_group  VARCHAR2 (50),    grown_in    VARCHAR2 (100)    )

4

Stay tuned for the rest of the series!

Further Reading

Comment (0)