Skip to main content

Dynamic Polymorphism - Why, What, How

Dynamic means "run-time."

Polymorphism means "multiple shapes."

Synonyms for dynamic polymorphism include "runtime polymorphism" and "dynamic method dispatch."

If you are a "traditional" relational database developer, these terms might sound unfamiliar. But how about overloading? Are you familiar with that?

Overloading occurs when you have more than one subprogram (procedure and/or function) with the same name in the declaration section of a block, package specification or package body. These subprograms need to differ by parameter list or type (procedure vs function) in a way that is sufficient for the compiler to distinguish.

Well, guess what? Another name for overloading is "static polymorphism."

Static means "compile-time."

Polymorphism means "multiple shapes."

Why, you might be wondering, does the Oracle Database need to wait till runtime to determine which method in which type in the hierarchy should be called?

After all, it doesn't have any troubling sorting that out with overloading of subprograms in packages!

The answer to that question lies in one word: substitutability.

It's a topic I've touched on both directly and indirectly in my previous posts in this series. The best way to think about substitutability is that if I have the following type hierarchy....
CREATE TYPE food_ot AS OBJECT (
   name        VARCHAR2(100),
   food_group  VARCHAR2 (50),
   grown_in    VARCHAR2 (100)
   )
   NOT FINAL
   ;

CREATE TYPE dessert_t UNDER food_ot (
   contains_chocolate  VARCHAR2(1),
   year_created        NUMBER(4)
   )
   NOT FINAL
   ;
....then this follows:
Every dessert is a food, but not every item of food is a dessert.
And now with the S word:
Where I have an instance of food, I can substitute it with an instance of dessert.
This ability to substitute is precisely what drives the need for dynamic polymorphism in object-oriented languages. Let's find out why.

Since polymorphism has to do with choosing the right method, let's enhance my food-related hierarchy to include a member method in each type.
CREATE TYPE food_ot AS OBJECT (
   name VARCHAR2 (100),
   food_group VARCHAR2 (100),
   grown_in VARCHAR2 (100),
   MEMBER FUNCTION price
      RETURN NUMBER
)
   NOT FINAL;
/

CREATE OR REPLACE TYPE BODY food_ot
IS
   MEMBER FUNCTION price
      RETURN NUMBER
   IS
   BEGIN
      RETURN (CASE self.food_group
                 WHEN 'PROTEIN' THEN 3
                 WHEN 'FRUIT' THEN 2
                 WHEN 'VEGETABLE' THEN 1
              END);
   END;
END;
/

CREATE TYPE dessert_ot
   UNDER food_ot (
      contains_chocolate VARCHAR2 (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
   BEGIN
      RETURN 100;
   END;
END;
/
I'm keeping the price formula really simple for desserts. :-)

And now consider the following block:
DECLARE
   TYPE foodstuffs_nt IS TABLE OF food_ot;

   fridge_contents   foodstuffs_nt
      := foodstuffs_nt (food_ot ('Brussels Sprouts', 'VEGETABLE', 'Farm'),
                        dessert_ot ('Strawberries',
                                   'FRUIT',
                                   'Backyard',
                                   'N',
                                   2001));
BEGIN
   FOR indx IN fridge_contents.FIRST .. fridge_contents.LAST
   LOOP
      DBMS_OUTPUT.put (
            CASE
               WHEN fridge_contents (indx) IS OF (ONLY food_ot)
               THEN
                  'Food'
               WHEN fridge_contents (indx) IS OF (ONLY dessert_ot)
               THEN
                  'Dessert'
            END
         || ' price:');

      DBMS_OUTPUT.put_line (fridge_contents (indx).price ());
   END LOOP;
END;
/
We can see from this code why overloading or static polymorphism is not sufficient when it comes to executing the right method.

When the block is compiled, the PL/SQL engine knows that the fridge_contents nested table is filled with instances of type food_t. It could even, I suppose, notice that the nested table contains instances of food_t and dessert_t.

But it sure is hard to see how at compile time. the PL/SQL engine would know which price method should be used in the call to DBMS_OUTPUT.PUT_LINE. After all, fridge_contents (indx) at compile time is of type food_t (and, of course, because of substitutability, it could also be any subtype of food_t, but what compiler could sort that out?).

It is only when the block is executed that PL/SQL can check to see the actual type of the instance in that element of the collection and invoke the appropriate method.

And as you can see from my use of the IS OF syntax, it is possible for both us and the PL/SQL engine to get that type.

You can run this code for yourself on LiveSQL.

Check Out the Entire Series

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


Comments

  1. Hi Steven! Recently I "found" some cool use for polymorphism: to pass function as parameter. Looks like inventing bycicle :) We make parent object type with one function-method without parameters and use this type as parameter type where we need function as parameter. Then we make child object types where overwrite single method and use object type fields as parameters, make constructor for them. Now we can instantiate child objects and pass them as parameters and call their single method.
    But why we need to pass function as parameter? :) I often need to pass selects with same structure, now I use pipelined table function-methof for it.

    ReplyDelete
    Replies
    1. Sounds really interesting, Vasily. I wonder if you might be able to publish a script on LiveSQL.oracle.com that demonstrates the technique?

      Delete
  2. Hello Steven Sir,
    Namaskar.
    Outstanding (all the articles in the series).

    ReplyDelete

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…

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today:
In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer

Quick Answer
Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption.Test to see if that's fast enough (likely will be for many cases).If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory. Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification.Don't put anything in the collection you don't need. [from Giulio Dottorini]Remember: each session that runs this code will use that amount of memory.Background

When you use BULK COLLECT, you retrieve more than row with each fetch, reducing context switchi…

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…