Comparison Methods for Object Types, Part 5

There are special member methods — map or order methods — that we use to tell Oracle Database how to compare two objects of the same datatype. This capability is critical when we want to perform an equality test in PL/SQL or when sorting objects in SQL.

You may also like:  Object-Oriented JavaScript

There is no default way to do this. In other words, if I create a simple object type, add it as a column to a table, and try to compare or sort, all I get are errors. Let's take a look. First I will create a table that has an object type as a column and add a couple of rows.

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

CREATE TABLE meals
(
   served_on     DATE,
   main_course   food_ot
);
/

BEGIN
   INSERT INTO meals (served_on, main_course)
        VALUES (SYSDATE, food_ot ('Shrimp cocktail', 'PROTEIN', 'Ocean'));

   INSERT INTO meals (served_on, main_course)
        VALUES (SYSDATE + 1, food_ot ('House Salad', 'VEGETABLE', 'Farm'));

   COMMIT;
END;
/

Next, I will query the contents of this table. Notice that I can order by an attribute within the type column's value. I can also perform an equality comparison between instances of the object type.

  SELECT m.main_course.name name
    FROM meals m
ORDER BY m.main_course.name
/

NAME
---------------
House salad
Shrimp Cocktail

SELECT m.main_course.name name
  FROM meals m, meals m2
 WHERE m.main_course = m2.main_course
 ORDER BY m.main_course.name
/

NAME
---------------
House salad
Shrimp Cocktail

That default equality comparison does an attribute-by-attribute comparison - and that only works if you do not have LOB or user-defined type columns. In those cases, you will see an error:

CREATE TYPE food_with_clob_ot AS OBJECT
(
   name VARCHAR2 (100),
   grown_in CLOB
)
   NOT FINAL
/

CREATE TABLE meals_with_clobs
(
   served_on     DATE,
   main_course   food_with_clob_ot
);
/

SELECT m.main_course.name name
  FROM meals_with_clobs m, meals_with_clobs m2
 WHERE m.main_course = m2.main_course
 ORDER BY m.main_course.name
/

ORA-22901: cannot compare VARRAY or LOB attributes of an object type

Now let's try to (a) sort the rows with ORDER BY and (b) do a non-equality comparison. It's a "no go." The default behavior is no longer available to satisfy these queries.

  SELECT m.main_course.name name
    FROM meals m
ORDER BY m.main_course
/

ORA-22950: cannot ORDER objects without MAP or ORDER method

  SELECT m.main_course.name name
    FROM meals m, meals m2
   WHERE m.main_course > m2.main_course
/

ORA-22950: cannot ORDER objects without MAP or ORDER method

And as far as equality comparisons go, that only works by default in SQL, not in PL/SQL.

DECLARE
   m1   food_ot := food_ot ('Shrimp cocktail', 'PROTEIN', 'Ocean');
   m2   food_ot := food_ot ('House Salad', 'VEGETABLE', 'Farm');
BEGIN
   IF m1 = m1
   THEN
      DBMS_OUTPUT.put_line ('Equal');
   END IF;

   IF m1 <> m2
   THEN
      DBMS_OUTPUT.put_line ('Unequal');
   END IF;
END;
/

PLS-00526: A MAP or ORDER function is required for comparing objects in PL/SQL.

So what's a developer to do? Read those error messages and get to work!

If you "cannot ORDER objects without MAP or ORDER method," perhaps you should create a MAP or ORDER method. :)

A MAP method is a member method (attached to an instance of the type) that returns a "mapping" of the object value onto a datatype that Oracle Database already knows how to compare, such as a number or string.

An ORDER member method compares two different instances of a type and returns a flag value that indicates their relative ordering.

You can only have one MAP or ORDER method in an object type definition. They cannot co-exist.

The MAP Method

A MAP method performs calculations on the attributes of the object to produce a return value of ny Oracle built-in data types (except LOBs and BFILEs) and ANSI SQL types such as CHARACTER or REAL.

This method is called automatically by Oracle Database to evaluate such comparisons as obj_1 > obj_2 and comparisons that are implied by the DISTINCT, GROUP BY, UNION, and ORDER BY clauses - since these all require sorting by rows in the table.

"Automatic" means:

1. You never invoke a map method directly in your code.

2. Assuming the type has a map method called "mapme", then when you write a comparison like this

obj_1 > obj_2

it is automatically translated (invisibly to you) to:

obj_1.mapme() > obj_2.mapme()

Let's add a map method to the food type. I'll keep it simple and silly. Proteins rate higher than liquids, which rate higher than carbs, which rate higher than vegetables. Take that number and add to the length of the food name. Then return that number for mapping. Finally, add some rows.

CREATE TYPE food_t AS OBJECT
    (name VARCHAR2 (100)
  , food_group VARCHAR2 (100)
  , grown_in VARCHAR2 (100)
  , MAP MEMBER FUNCTION food_mapping
         RETURN NUMBER
    )
    NOT FINAL;
/

CREATE OR REPLACE TYPE BODY food_t
IS
    MAP MEMBER FUNCTION food_mapping
        RETURN NUMBER
    IS
    BEGIN
        RETURN (CASE self.food_group
                      WHEN 'PROTEIN' THEN 30000
                      WHEN 'LIQUID' THEN 20000
                      WHEN 'CARBOHYDRATE' THEN 15000
                      WHEN 'VEGETABLE' THEN 10000
                  END
                  + LENGTH (self.name));
    END;
END;
/

BEGIN
   -- Populate the meal table
   INSERT INTO meals
        VALUES (SYSDATE, food_ot ('Shrimp cocktail', 'PROTEIN', 'Ocean'));

   INSERT INTO meals
        VALUES (SYSDATE + 1, food_ot ('Stir fry tofu', 'PROTEIN', 'Wok'));

   INSERT INTO meals
           VALUES (SYSDATE + 1,
                   food_ot ('Peanut Butter Sandwich',
                              'CARBOHYDRATE',
                              'Kitchen'));

   INSERT INTO meals
           VALUES (SYSDATE + 1,
                   food_ot ('Brussels Sprouts', 'VEGETABLE', 'Backyard'));

   COMMIT;
END;
/

Now I perform an ORDER BY on the object type column, check for inequality among rows, and also perform comparisons inside PL/SQL.

  SELECT m.main_course.name name
  FROM meals m
ORDER BY main_course
/

NAME
----------------------
Brussels Sprouts
Peanut Butter Sandwich
Stir fry tofu
Shrimp cocktail

  SELECT m1.main_course.name name
    FROM (SELECT *
            FROM meals m
           WHERE m.main_course.name LIKE 'S%') m1,
         (SELECT *
            FROM meals m
           WHERE m.main_course.name NOT LIKE 'S%') m2
   WHERE m1 > m2
ORDER BY m1.main_course
/

NAME
---------------
Stir fry tofu
Stir fry tofu
Shrimp cocktail
Shrimp cocktail

DECLARE
   ot1   food_ot := food_ot ('Eggs benedict', 'PROTEIN', 'Farm');
   ot2   food_ot := food_ot ('Brussels Sprouts', 'VEGETABLE', 'Backyard');
   ot3   food_ot := food_ot ('Brussels Sprouts', 'VEGETABLE', 'Backyard');
BEGIN
   IF ot1 = ot2
   THEN
      DBMS_OUTPUT.put_line ('equal - incorrect');
   ELSE
      DBMS_OUTPUT.put_line ('not equal - correct');
   END IF;

   IF ot2 <> ot3
   THEN
      DBMS_OUTPUT.put_line ('not equal - incorrect');
   ELSE
      DBMS_OUTPUT.put_line ('equal - correct');
   END IF;
END;
/

not equal - correct
equal - correct

Notice that in the query that joins m1 and m2, "Stir fry tofu" comes before "Shrimp cocktail" because it has fewer characters and thus a small number returned by the map function.

The ORDER Method

Unlike map methods, order methods cannot determine the order of a number of objects. They simply tell you that the current object is less than, equal to, or greater than the object that it is being compared to, based on the criterion used.

An order method is a function for an object (SELF), with one declared parameter that is an object of the same type. The method must return either a negative number, zero, or a positive number. This value signifies that the object (the implicit undeclared SELF parameter) is less than, equal to, or greater than the declared parameter object.

As with map methods, an order method, if one is defined, is called automatically whenever two objects of that type need to be compared.

Order methods are useful where comparison semantics may be too complex to use a map method.

Let's build an order method for the food type. Let's start with the specification for the food type, and also create the type hierarchy:

CREATE TYPE food_ot AS OBJECT
(
   name VARCHAR2 (100),
   food_group VARCHAR2 (100),
   ORDER MEMBER FUNCTION food_ordering (other_food_in IN food_ot)
      RETURN INTEGER
)
   NOT FINAL;
/

CREATE TYPE dessert_ot UNDER food_ot (
   contains_chocolate   CHAR (1)
 , year_created         NUMBER (4)
)
NOT FINAL;
/

CREATE TYPE cake_ot UNDER dessert_ot (
   diameter      NUMBER
 , inscription   VARCHAR2 (200)
);
/

Notice that I use the ORDER keyword, pass in an instance of the type, against which SELF will be compared. I return an integer: either -1, 0 or 1. And now the implementation. Here are some notes, given its complexity:

• Since an instance could be of food, dessert or cake, the first rule is that a supertype is always greater than a subtype. I use self IS OF (ONLY my_type) syntax to determine the type of the instance.
• I use a string-indexed collection, l_order_by_food_group to establish the hierarchy of ordering by food group. (I really like string-indexed collections!)
• If after checking for supertype/subtype ordering, I know that other_food_in is of the same type as SELF, then I set return value according to food group.

CREATE OR REPLACE TYPE BODY food_ot
IS
   ORDER MEMBER FUNCTION food_ordering (other_food_in IN food_ot)
      RETURN INTEGER
   /*
   Subtypes are always less. Food > Dessert > Cake

   If of the same type, same rule AS for MAP:
      Vegetable < Carbohydrate < Liquid < Protein
   */
   IS
      TYPE order_by_food_group_t IS TABLE OF PLS_INTEGER
         INDEX BY VARCHAR2 (100);

      l_order_by_food_group   order_by_food_group_t;
      c_self_eq_of   CONSTANT PLS_INTEGER := 0;
      c_self_gt_of   CONSTANT PLS_INTEGER := 1;
      c_of_gt_self   CONSTANT PLS_INTEGER := -1;
      l_ordering              PLS_INTEGER := c_self_eq_of;

      PROCEDURE initialize
      IS
      BEGIN
         l_order_by_food_group ('PROTEIN') := 1000;
         l_order_by_food_group ('LIQUID') := 100;
         l_order_by_food_group ('CARBOHYDRATE') := 10;
         l_order_by_food_group ('VEGETABLE') := 1;
      END initialize;
   BEGIN
      initialize;

      IF self IS OF (ONLY food_ot)
      THEN
         l_ordering :=
            CASE
               WHEN other_food_in IS OF (ONLY food_ot) THEN c_self_eq_of
               ELSE c_self_gt_of
            END;
      ELSIF self IS OF (ONLY dessert_t)
      THEN
         l_ordering :=
            CASE
               WHEN other_food_in IS OF (ONLY dessert_t) THEN c_self_eq_of
               WHEN other_food_in IS OF (ONLY food_ot) THEN c_of_gt_self
               ELSE c_self_gt_of
            END;
      ELSE
         /* It is cake. */
         l_ordering :=
            CASE
               WHEN other_food_in IS OF (ONLY cake_t) THEN c_self_eq_of
               ELSE c_of_gt_self
            END;
      END IF;

      IF l_ordering = c_self_eq_of
      THEN
         /*
         Further analysis is needed.
         */
         l_ordering :=
            CASE
               WHEN l_order_by_food_group (self.food_group) =
                       l_order_by_food_group (other_food_in.food_group)
               THEN
                  c_self_eq_of
               WHEN l_order_by_food_group (self.food_group) >
                       l_order_by_food_group (other_food_in.food_group)
               THEN
                  c_self_gt_of
               WHEN l_order_by_food_group (self.food_group) <
                       l_order_by_food_group (other_food_in.food_group)
               THEN
                  c_of_gt_self
            END;
      END IF;

      RETURN l_ordering;
   END;
END;
/

Now I will add rows of various types.

BEGIN
   -- Populate the meal table
   INSERT INTO meals
        VALUES (SYSDATE, food_ot ('Shrimp cocktail', 'PROTEIN'));

   INSERT INTO meals
        VALUES (SYSDATE + 1, food_ot ('Stir fry tofu', 'PROTEIN'));

   INSERT INTO meals
        VALUES (SYSDATE + 1,
                dessert_ot ('Peanut Butter Sandwich',
                            'CARBOHYDRATE',
                            'N',
                            1700));

   INSERT INTO meals
        VALUES (SYSDATE + 1, food_ot ('Brussels Sprouts', 'VEGETABLE'));

   INSERT INTO meals
        VALUES (SYSDATE + 1,
                cake_ot ('Carrot Cake',
                         'VEGETABLE',
                         'N',
                         1550,
                         12,
                         'Happy Birthday!'));

   COMMIT;
END;
/

All right, then, let's have some fun! Ordering rows works. SQL comparisons work.

  SELECT m.main_course.name name
  FROM meals m
ORDER BY main_course
/

NAME
----------------------
Carrot Cake
Peanut Butter Sandwich
Brussels Sprouts
Shrimp cocktail
Stir fry tofu

  SELECT m1.main_course.name name
    FROM (SELECT *
            FROM meals m
           WHERE m.main_course.name LIKE 'S%') m1,
         (SELECT *
            FROM meals m
           WHERE m.main_course.name NOT LIKE 'S%') m2
   WHERE m1.main_course > m2.main_course
ORDER BY m1.main_course
/

NAME
---------------
Shrimp cocktail
Shrimp cocktail
Shrimp cocktail
Stir fry tofu
Stir fry tofu
Stir fry tofu

And how about in PL/SQL?

DECLARE
   ot1   food_ot := food_ot ('Eggs benedict', 'PROTEIN');
   ot2   food_ot := food_ot ('Brussels Sprouts', 'VEGETABLE');
   ot3   food_ot := dessert_ot ('Brownie', 'SUGAR', 'Y', 1943);
   ot4   food_ot := cake_ot (
      'Carrot Cake', 'VEGETABLE', 'N', 1550, 12, 'Happy Birthday!');
BEGIN
   IF ot1 = ot1
   THEN
      DBMS_OUTPUT.put_line ('equal - correct');
   ELSE
      DBMS_OUTPUT.put_line ('not equal - incorrect');
   END IF;

   IF ot1 = ot2
   THEN
      DBMS_OUTPUT.put_line ('equal - incorrect');
   ELSE
      DBMS_OUTPUT.put_line ('not equal - correct');
   END IF;

   IF ot2 <> ot3
   THEN
      DBMS_OUTPUT.put_line ('not equal - correct');
   ELSE
      DBMS_OUTPUT.put_line ('equal - incorrect');
   END IF;

   IF ot2 > ot3
   THEN
      DBMS_OUTPUT.put_line ('food > dessert - correct');
   ELSE
      DBMS_OUTPUT.put_line ('food < dessert - incorrect');
   END IF;

   IF ot3 > ot4
   THEN
      DBMS_OUTPUT.put_line ('dessert > cake - correct');
   ELSE
      DBMS_OUTPUT.put_line ('dessert < cake - incorrect');
   END IF;

   IF ot3 < ot4
   THEN
      DBMS_OUTPUT.put_line ('dessert < cake - incorrect');
   ELSE
      DBMS_OUTPUT.put_line ('dessert > cake - correct');
   END IF;
END;
/

equal - correct
not equal - correct
not equal - correct
food > dessert - correct
dessert > cake - correct
dessert > cake - correct

All good!

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.

The Series:

• Using Object Types in Relational Tables, Part 4
• Comparison Methods for Object Types, Part 5