Skip to main content

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.

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!

Check Out the Entire 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

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…