Skip to main content

Introduction to Object Types, a.k.a., Classes, Part 1

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 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 blog 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, then it works - since the names of the parameters are different from the attribute names.
DECLARE 
   my_favorite_vegetable   food_ot 
      := food_ot (name_in         => 'Brussels Sprouts', 
                  food_group_in   => 'Vegetables', 
                  grown_in_in     => 'Dirt'); 
BEGIN 
   DBMS_OUTPUT.put_line ( 
         my_favorite_vegetable.name 
      || ' are grown in ' 
      || my_favorite_vegetable.grown_in); 
END; 
/

Brussels Sprouts are grown in Vegetables
But I use the attribute names in my named notation syntax, the default constructor is called.
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 grown in ' 
      || my_favorite_vegetable.grown_in); 
END; 
/

Brussels Sprouts are grown in Dirt
Now I will re-create the type with a constructor whose parameter names match the attribute names.
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 VARCHAR2, 
                                 food_group   IN VARCHAR2, 
                                 grown_in     IN VARCHAR2) 
      RETURN SELF AS RESULT 
) 
   NOT FINAL; 
/

CREATE OR REPLACE TYPE BODY food_ot 
IS 
   CONSTRUCTOR FUNCTION food_ot (name         IN VARCHAR2, 
                                 food_group   IN VARCHAR2, 
                                 grown_in     IN VARCHAR2) 
      RETURN SELF AS RESULT 
   IS 
   BEGIN 
      self.name := name; 
      self.food_group := grown_in; 
      self.grown_in := food_group; 
      RETURN; 
   END; 
END;
/
With named notation, it calls my constructor (notice the value for grown-in).
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 grown in ' 
      || my_favorite_vegetable.grown_in); 
END; 
/

Brussels Sprouts are grown in Vegetables
And, for the grand finale, even without named notation, it calls my constructor.
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; 
/

Brussels Sprouts are grown in Vegetables

Check Out the Series

Visit this post that gives you quick access to all the articles in the series.

Comments

  1. Thanks Steven for taking me to a new level of programming from procedural to object oriented through your blogspot. Just a minor typographical error where I observed..

    Wrongly initialized with food_rt instead of food_ot;

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

    ReplyDelete
  2. Easy, understandable fun reading as usual. Thanks. Always nice to quickly refresh some things in my memory. :-)

    ReplyDelete
  3. Steven,

    You say after creating the two constructors that you can initialize in two "styles", however the default constructor does not get overwritten and is still there: So in you example you could use:

    declare
    my_veggie food_ot;
    begin
    -- empty constructor (in type body)
    my_veggie := new food_ot;
    my_veggie.name := 'Haricot Verts';
    dbms_output.put_line( my_veggie.name );

    -- constructor with one parameter (in type body)
    my_veggie := new food_ot(name_in => 'Brussels Sprouts');
    dbms_output.put_line( my_veggie.name );

    -- default constructor referring type-attributes directly
    my_veggie := new food_ot(name => 'Broccoli'
    ,food_group => 'Veggies'
    ,grown_in => 'Dirt');
    dbms_output.put_line( my_veggie.name );

    end;

    ReplyDelete
    Replies
    1. You are absolutely right, Richard. I did not mean to imply that you could no longer use the default constructor. I will apply your fix to the post. Many thanks for taking the time to right!

      Delete
  4. You can also overwrite default constructor. It's rather funny :)

    ReplyDelete
    Replies
    1. Ha! Never thought of trying that. I will do so and add to the post. Thanks!

      Delete
  5. Cool trick with named parameters Steven!
    I thought may be we can use it to make any constructors with same parameters number and type which differ only be names and we can, here is LiveSQL script:
    https://livesql.oracle.com/apex/livesql/s/iutkz2yairteawe09rxd4cner
    It looks like dark magic and dirty trick but it's still cool :)

    ReplyDelete
    Replies
    1. Hmmm. I believe what you are pointing out is similar to what I demonstrate in my ambiguous overloading script:

      https://livesql.oracle.com/apex/livesql/file/content_CG2S5I331EGFBTZET198MO4DC.html

      Basically, as long as you have different parameter names, you can FORCE PL/SQL to distinguish between otherwise indistinguishable overloadings.

      Is this what you are also showing here, or something else I might be missing?

      What I found most curious about the overloading of constructors is that you can completely override the default constructor. Hadn't realized that was possible before.

      Delete
  6. Yes Steven I mean exactly this but for constructors. We have only one constructor so can't change function name so distinguising by parameter name can be useful. I needed it before when parameters for different initialization scenarios were of same type but I did not know about this possibility.
    Looks like it added to documentation since version 11 here: https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#LNPLS00807
    And looks like since version 11 we can use named notation in SQL as described here:
    https://logicalread.com/oracle-11g-named-parameters-in-sql-function-calls-mc02/#.XV_TmOgzaUk

    Overloading also used with inheritance during dynamic method dispatch so your next blog post going to be hard :)

    ReplyDelete
    Replies
    1. I would never have predicted when I wrote this first introductory post that it would result in such a vibrant discussion and expansion of my knowledge!

      Thanks, Vasily!

      Delete
  7. I checked documentation and description of default constructor overloading is there since version 9. Looks like we have to treat what we thought was dark magic as well documented technology and confess we skip reading documentation :)

    ReplyDelete
    Replies
    1. Right, you are!

      "User-defined constructors are not inherited, so a user-defined constructor defined in a supertype cannot be hidden in a subtype. However, a user-defined constructor does hide, and thus supersede, the attribute-value constructor for its type if the signature of the user-defined constructor exactly matches the signature of the attribute-value constructor. "

      https://docs.oracle.com/en/database/oracle/oracle-database/19/adobj/system-defined-and-user-defined-constructors.html#GUID-50C1ADD6-F597-4C6F-BC08-F5DC193F2257

      Delete
    2. But I am WAAAAAY happier to find it in the doc (and accept my incomplete lack of knowledge about this feature) than to discover that it is undocumented, and therefore unsupported, behavior.

      Delete
  8. Simple question: When to use object types and what is their performance against classicall "old fashion way" of PLSQL programming?

    ReplyDelete
    Replies
    1. I suggest a few use cases at the top of this post.

      Regarding performance, it's kind of like an "apples and oranges" thing. Hard to answer. I would say in terms of PL/SQL code, you will not see a significant difference.

      As for using object types in relational tables, I encourage you to check out this AskTOM thread:

      https://asktom.oracle.com/pls/apex/asktom.search?tag=user-defined-types-performance

      Bottom line is that you should not be overly worried about the impact of choosing this path. Performance should NOT be the criteria by which you decide to use object types or not.

      Delete

Post a Comment

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.
How to Get a Mutating Table ErrorI need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…

Table Functions, Part 1: Introduction and Exploration

Please do feel encouraged to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latter part of this seri…