Skip to main content

Object Type Methods, Part 3

Packages have subprograms (procedures and functions). Object types have methods.

Object type methods are, still, procedures and functions. But there are also different types and characteristics of methods that only make sense in an object type, which supports inheritance and dynamic polymorphism.

In this post, 3rd in my series on object types, I explore
  • Static methods 
  • Member methods 
  • Non-instantiable methods 
  • Invoking methods of super types
All the code you see below can be run in Oracle LiveSQL through this script.

Member Methods

Member methods are methods applied to an instance of the type. Almost all the methods you ever write for an object type will be a member method. Assuming you are already familiar with writing PL/SQL functions and procedures, the most important thing to come up to speed on is the SELF value.

Member methods have a built-in (implicit) parameter named SELF that denotes the object instance currently invoking the method.

We'll explore member methods, including a reminder about overriding, with a to_string function. it's quite common for a class to have a method that returns a string representation. That's what the to_string method will do in our object types.
CREATE OR REPLACE TYPE food_ot AS OBJECT (
   NAME         VARCHAR2 (100),
   food_group   VARCHAR2 (100),
   grown_in     VARCHAR2 (100),
   MEMBER FUNCTION to_string RETURN VARCHAR2
)
NOT FINAL;
/

CREATE OR REPLACE TYPE BODY food_ot
IS
   MEMBER FUNCTION to_string RETURN VARCHAR2
   IS
   BEGIN
      RETURN    'FOOD! '
             || SELF.NAME
             || ' - '
             || SELF.food_group
             || ' - '
             || SELF.grown_in;
   END;
END;
/

DECLARE
   squirrels_love_them  food_ot := 
      food_ot ('Acorn', 'Protein', 'Tree');
BEGIN
   DBMS_OUTPUT.put_line (squirrels_love_them.to_string());
END;
/

FOOD! Acorn - Protein - Tree
I could declare SELF explicitly as well, and it works exactly the same:
CREATE OR REPLACE TYPE food_ot AS OBJECT (
   NAME         VARCHAR2 (100),
   food_group   VARCHAR2 (100),
   grown_in     VARCHAR2 (100),
   MEMBER FUNCTION to_string (SELF IN food_ot) RETURN VARCHAR2
)
NOT FINAL;
/

CREATE OR REPLACE TYPE BODY food_ot
IS
   MEMBER FUNCTION to_string (SELF IN food_ot) RETURN VARCHAR2
   IS
   BEGIN
      RETURN    'FOOD! '
             || SELF.NAME
             || ' - '
             || SELF.food_group
             || ' - '
             || SELF.grown_in;
   END;
END;
/

DECLARE
   squirrels_love_them  food_ot := 
      food_ot ('Acorn', 'Protein', 'Tree');
BEGIN
   DBMS_OUTPUT.put_line (squirrels_love_them.to_string());
END;
/

FOOD! Acorn - Protein - Tree
What if I want to change the value of an instance's attribute inside my member method? In that case, if it is a function, I must include SELF as a parameter and make it IN OUT. Suppose for example that I want to enforce upper case on all attribute values:
CREATE OR REPLACE TYPE food_ot AS OBJECT (
   NAME         VARCHAR2 (100),
   food_group   VARCHAR2 (100),
   grown_in     VARCHAR2 (100),
   MEMBER FUNCTION to_string (SELF IN OUT food_ot) RETURN VARCHAR2
)
NOT FINAL;
/

CREATE OR REPLACE TYPE BODY food_ot
IS
   MEMBER FUNCTION to_string (SELF IN OUT food_ot) RETURN VARCHAR2
   IS
   BEGIN
      /* Enforce upper case for all values */
      SELF.name := UPPER (SELF.name);
      SELF.food_group := UPPER (SELF.food_group);
      SELF.grown_in := UPPER (SELF.grown_in);
      
      RETURN    'FOOD! '
             || SELF.NAME
             || ' - '
             || SELF.food_group
             || ' - '
             || SELF.grown_in;
   END;
END;
/

DECLARE
   squirrels_love_them  food_ot := 
      food_ot ('Acorn', 'Protein', 'Tree');
BEGIN
   DBMS_OUTPUT.put_line (squirrels_love_them.to_string());
   DBMS_OUTPUT.put_line ('Still upper case? ' || squirrels_love_them.name);
END;
/

FOOD! ACORN - PROTEIN - TREE
Still upper case? ACORN
But as Vasily Suvorov so kindly pointed out in the comments for this post, that is not necessary if your member method is a procedure. Thanks, Vasily!

Static Methods

A static method is a method that is the same (unchanging, static) for all instances of the type. Another way to think about it is that it is a type-level method.

Suppose, for example, that I want to keep track of a version number for my object type. It wouldn't change per instance of the type. It is a characteristic of the type itself. I would define and use that function as follows.
CREATE OR REPLACE TYPE food_ot AS OBJECT
(
   name VARCHAR2 (100),
   food_group VARCHAR2 (50),
   grown_in VARCHAR2 (100),
   STATIC FUNCTION version RETURN VARCHAR2
)
   NOT FINAL;
/

CREATE OR REPLACE TYPE BODY food_ot
IS
   STATIC FUNCTION version RETURN VARCHAR2
   IS
   BEGIN
      /* 
      Version history
      2018-09-14 1.0.1 Type deployed to production
      2019-03-22 1.0.2 Added grown_in attribute      
      */
      RETURN '1.0.2';
   END;
END;
/

BEGIN
   DBMS_OUTPUT.put_line ('Version = ' || food_ot.version);
END;
/

Version = 1.0.2
Note that the SELF value isn’t available inside static method bodies; static methods have no “current object.” If you try, you will get this error:
PLS-00201: identifier 'SELF.attribute_name' must be declared
Static methods are inherited.
CREATE OR REPLACE TYPE dessert_ot UNDER food_ot (
    contains_chocolate CHAR (1)
  , year_created NUMBER (4) 
);
/

BEGIN
   DBMS_OUTPUT.put_line (dessert_ot.version);
END;
/

1.0.2
They cannot be over-ridden using the OVERRIDING syntax. Instead, when you create a static method with the same name as a supertype, well, that takes precedence.
CREATE OR REPLACE TYPE dessert_ot UNDER food_ot (
    contains_chocolate CHAR (1)
  , year_created NUMBER (4) 
  , STATIC FUNCTION version RETURN VARCHAR2
);
/

CREATE OR REPLACE TYPE BODY dessert_ot
IS
   STATIC FUNCTION version RETURN VARCHAR2
   IS
   BEGIN
      RETURN 'v10.4.5';
   END;
END;
/

BEGIN
   DBMS_OUTPUT.put_line (dessert_ot.version);
END;
/

10.4.5
What if a supertype has a static function and a subtype has a static procedure with same name? No problem!
CREATE OR REPLACE TYPE dessert_ot UNDER food_ot (
    contains_chocolate CHAR (1)
  , year_created NUMBER (4) 
  , STATIC PROCEDURE version 
);
/

CREATE OR REPLACE TYPE BODY dessert_ot
IS
   STATIC PROCEDURE version 
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE ('v10.4.5');
   END;
END;
/

BEGIN
   DBMS_OUTPUT.put_line (dessert_ot.version);
   dessert_ot.version;
END;
/

1.0.2
v10.4.5
That was fun! OK, let's move on to a very interesting nuance when it comes to methods.

Non-instantiable Methods

This is not a third type of method. It is a way that you can define a member method. Use the NOT INSTANTIABLE clause to define requirements for the API you are building with your type hierarchy and methods.

When you declare a method to be instantiable, you are define the header of the method, but not its implementation. This also means that an instance of that type cannot invoke the member method (after all, it's not implemented).

Which also means that a subtype must implement an overriding method of the same signature for it to be instantiable (invoked as a method on an instance of the type).

Let's take a look. I will re-create the food type and specify that any subtype must implement a price function for it to be instantiable (the logic here is that "food" all by itself is simply too generic to be able to calculate a price for it - but all subtypes must do so).
CREATE OR REPLACE TYPE food_ot AS OBJECT (
   name VARCHAR2 (100),
   food_group VARCHAR2 (50),
   grown_in VARCHAR2 (100),
   NOT INSTANTIABLE MEMBER FUNCTION price
      RETURN NUMBER
)
NOT FINAL NOT INSTANTIABLE;
/

DECLARE
   l_food food_ot := food_ot ('a', 'b', 'c');
BEGIN
   DBMS_OUTPUT.PUT_LINE (l_food.name);
END;
/

PLS-00713: attempting to instantiate a type that is NOT INSTANTIABLE  
I can no longer use the food_ot constructor to initialize a variable based on the food_ot type.

It is still possible, however, to declare a variable using the food_ot and then instantiate and use it, via a subtype constructor, as Vasily Suvorov shows in his LiveSQL script (thanks, Vasily!) and I offer in simplified format below:
CREATE OR REPLACE TYPE food_ot AS OBJECT ( 
   name VARCHAR2 (100), 
   NOT INSTANTIABLE MEMBER FUNCTION price 
      RETURN NUMBER 
) 
NOT FINAL NOT INSTANTIABLE; 
/

CREATE OR REPLACE TYPE dessert_ot UNDER food_ot ( 
   contains_chocolate   CHAR (1)
 , OVERRIDING MEMBER FUNCTION price 
      RETURN NUMBER 
) 
NOT FINAL;
/

CREATE OR REPLACE TYPE BODY dessert_ot 
IS 
   OVERRIDING MEMBER FUNCTION price 
      RETURN NUMBER 
   IS 
   BEGIN 
      RETURN 1; 
   END; 
END; 
/

DECLARE 
   l_food food_ot ; 
BEGIN 
   l_food := dessert_ot ('Apple', 'N'); 
   DBMS_OUTPUT.PUT_LINE (l_food.name); 
END; 
/

Apple
Notice that when I declare at least one member method to be NOT INSTANTIABLE, I must also do the same for the type as a whole.

Now I will declare the dessert subtype, and implement a price method. I can then instantiate variables of this type.
CREATE OR REPLACE TYPE dessert_ot UNDER food_ot (
   contains_chocolate   CHAR (1)
 , year_created         NUMBER (4)
 , OVERRIDING MEMBER FUNCTION price
      RETURN NUMBER
)
NOT FINAL;
/

CREATE OR REPLACE TYPE BODY dessert_ot
IS
   OVERRIDING MEMBER FUNCTION price
      RETURN NUMBER
   IS
      multiplier   NUMBER := 1;
   BEGIN
      DBMS_OUTPUT.put_line ('Dessert price!');

      IF SELF.contains_chocolate = 'Y'
      THEN
         multiplier := 2;
      END IF;

      IF SELF.year_created < 1900
      THEN
         multiplier := multiplier + 0.5;
      END IF;

      RETURN (10.00 * multiplier);
   END;
END;
/

DECLARE
   l_apple dessert_ot := dessert_ot ('Apple', 'Fruit', 'Tree', 'N', -5000);
BEGIN
   DBMS_OUTPUT.PUT_LINE (l_apple.name);
END;
/

Apple
A subtype doesn't have to provide an implementation for the price function. It can "pass along" that responsibility to a subtype of its own. But then that type will have to declared NOT INSTANTIABLE.

Invoking Supertype Methods

What if you don't want to completely override a supertype method? What if you want to use it, but also modify its behavior? In that case, it will come in very handy to be able to explicitly invoke a supertype's method.

In the example below, I override the to_string method of the food_ot type to include dessert-specific information in the string returned. But I want to still take advantage of the parent's to_string function. To achieve this effect I use the SELF as parent_type syntax.
CREATE TYPE food_ot AS OBJECT (
   name VARCHAR2 (100),
   food_group VARCHAR2 (50),
   grown_in VARCHAR2 (100),
   MEMBER FUNCTION to_string RETURN VARCHAR2
)
NOT FINAL;
/

CREATE OR REPLACE TYPE BODY food_ot
IS
   MEMBER FUNCTION to_string RETURN VARCHAR2
   IS
   BEGIN
      RETURN    'FOOD! '
             || SELF.NAME
             || ' - '
             || SELF.food_group
             || ' - '
             || SELF.grown_in;
   END;
END;
/CREATE OR REPLACE TYPE dessert_t UNDER food_ot (
    contains_chocolate CHAR (1)
  , year_created NUMBER (4) 
  , OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2
);
/

CREATE OR REPLACE TYPE BODY dessert_ot
IS
   OVERRIDING MEMBER FUNCTION to_string  RETURN VARCHAR2
   IS
   BEGIN
      /* Add the supertype (food) string to the subtype string.... */
      RETURN    'DESSERT! With Chocolate? '
             || contains_chocolate
             || ' created in '
             || SELF.year_created
             || chr(10)
             || '...which is a...'
             || (SELF as food_ot).to_string;
   END;
END;
/
And as you can see, that nested invocation of the supertype method works just fine:
DECLARE
   TYPE foodstuffs_nt IS TABLE OF food_ot;

   fridge_contents foodstuffs_nt
         := foodstuffs_nt (
               food_ot ('Eggs benedict', 'PROTEIN', 'Farm')
             , dessert_ot ('Strawberries and cream'
                        , 'FRUIT', 'Backyard', 'N', 2001)
            );
BEGIN
   FOR indx in 1 .. fridge_contents.COUNT
   LOOP
      DBMS_OUTPUT.put_line (RPAD ('=', 60, '='));
      DBMS_OUTPUT.put_line (fridge_contents (indx).to_string);
   END LOOP;
END;
/

============================================================
FOOD! Eggs benedict - PROTEIN - Farm
============================================================
DESSERT! With Chocolate? N created in 2001
...which is a...FOOD! Strawberries and cream - FRUIT - Backyard

Method Chaining

Method chaining occurs when you connect up a sequence of methods (all of them functions at least until you get to the very end, when you could have a procedure) with dot notation. 

It's not something you can do with packaged subprograms, and it can look awfully strange to procedural developers. Here's an example.

Suppose I create this object type:
CREATE OR REPLACE TYPE chaindemo_ot AS OBJECT (
    x NUMBER, y VARCHAR2(10), z DATE,
    MEMBER FUNCTION setx (x IN NUMBER) RETURN chaindemo_ot,
    MEMBER FUNCTION sety (y IN VARCHAR2) RETURN chaindemo_ot,
    MEMBER FUNCTION setz (z IN DATE) RETURN chaindemo_ot);
I can then
DECLARE
    c chaindemo_ot := chaindemo_ot (NULL, NULL, NULL);
BEGIN
    c := c.setx(1).sety('foo').setz(sysdate); 
END;
The executable statement above really just acts as the equivalent of:
c := c.setx(1);
c := c.sety('foo');
c := c.setz(sysdate);
Each function returns a typed object as the input to the next function in the chain. The implementation of one of the methods appears in the following code (the others are similar):
MEMBER FUNCTION setx (x IN NUMBER) RETURN chaindemo_t IS
   l_self chaindemo_t := SELF;
BEGIN
   l_self.x := x;
   RETURN l_self;
END;
Here are some rules about chaining :
  • You cannot use a function’s return value as an IN OUT parameter to the next function in the chain. Functions return read-only values.
  • Methods are invoked in order from left to right.
  • The return value of a chained method must be of the object type expected by the method to its right. 
  • A chained call can include at most a single procedure.
  • If your chained call includes a procedure, it must be the rightmost method in the chain.

Comparing Two Object Type Instances

Can you compare two instances of the same object type for equality? What if you want to implement the concept of an order to an object type, so you can answer the question:
Is Instance A > Instance B?
For that, you need to build a comparison method (MAP or ORDER). I will cover these methods later in this series. So make sure to subscribe to my blog and my Twitter feed so you will see the announcement when I finish it.

But before showing you comparison methods, it will be important to show you how object types can be used inside relational tables. So that's up next!

Check Out the Series

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

P.S. Don't forget that if you'd like to try out all this code for yourself, all you have to do is run this LiveSQL script.

Comments

  1. Hi Steven!

    What if I want to change the value of an instance's attribute inside my member method? In that case, I must include SELF as a parameter and make it IN OUT

    It's only true for function method but not for procedure method. Here is working script: https://livesql.oracle.com/apex/livesql/s/ixvcuajiw0t0qhyy1i29p0qk6

    ReplyDelete
    Replies
    1. Many thanks for pointing that out, Vasily - and PROVING it with a LiveSQL script. :-)

      I have incorporated your feedback into the post and given you credit.

      Delete
  2. I have another notice :)

    I can no longer declare variables based on the food_ot type

    You can! I can proove it! :) Here is working script: https://livesql.oracle.com/apex/livesql/s/ixvo0an9nj0wbb61rsti4mjr9

    We just can't instantiate it with it's constructor.
    Variable even can be assigned and returned as function result. We use it for polimorphism. By the way you wrote about it in year 2002 :).

    ReplyDelete
    Replies
    1. All right, that's it. Enough is enough. You are having too much fun fact-checking my posts. Don't you know this is the Age of Fake News? Why do you insist on total accuracy?

      :-)

      Thanks so much, Vasily, for your close reading, taking the time to build a script to verify and for responding on my blog. I will update the text based on your comments and code, and give you credit, of course.

      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…