MAP and ORDER Methods for Object Type Comparison

View All Scripts Login to Run Script

Script Name MAP and ORDER Methods for Object Type Comparison
Description These 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. This script accompanies my blog post on the same topic: https://stevenfeuersteinonplsql.blogspot.com/2019/10/comparison-methods-for-object-types.html
Area PL/SQL General
Contributor Steven Feuerstein
Created Monday October 14, 2019

Statement 1

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

Type created.

Statement 2

CREATE TABLE meals
(
   served_on     DATE,
   main_course   food_ot
)

Table created.

Statement 3

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;

Statement processed.

Statement 4

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


MAIN_COURSE.NAME
House Salad
Shrimp cocktail

2 rows selected.

MAIN_COURSE.NAME
House Salad
Shrimp cocktail

Statement 5

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

2 rows selected.

NAME
House Salad
Shrimp cocktail

Statement 6

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

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

More Details: https://docs.oracle.com/error-help/db/ora-22950

Statement 7

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

More Details: https://docs.oracle.com/error-help/db/ora-22950

Statement 8

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;

ORA-06550: line 5, column 10:
PLS-00526: A MAP or ORDER function is required for comparing objects in PL/SQL.

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 9

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

Type created.

Statement 10

CREATE TABLE meals_with_clobs
(
   served_on     DATE,
   main_course   food_with_clob_ot
)

Table created.

Statement 11

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

More Details: https://docs.oracle.com/error-help/db/ora-22901

Statement 12
```
DROP TABLE meals
```
```
Table dropped.
```
Statement 13
```
DROP TYPE food_ot FORCE
```
```
Type dropped.
```

Statement 14

DROP TYPE food_with_clob_ot FORCE

Type dropped.

Statement 15

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

Type created.

Statement 16

CREATE OR REPLACE TYPE BODY food_ot 
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;

Type created.

Statement 17

CREATE TABLE meals
(
	served_on	  DATE
 , main_course   food_ot
)

Table created.

Statement 18

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;

Statement processed.

Statement 19

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


NAME
Brussels Sprouts
Peanut Butter Sandwich
Stir fry tofu
Shrimp cocktail

4 rows selected.

NAME
Brussels Sprouts
Peanut Butter Sandwich
Stir fry tofu
Shrimp cocktail

Statement 20

  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 
ORDER BY m1.main_course


NAME
Stir fry tofu
Stir fry tofu
Shrimp cocktail
Shrimp cocktail

4 rows selected.

NAME
Stir fry tofu
Stir fry tofu
Shrimp cocktail
Shrimp cocktail

Statement 21

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;

Statement processed.
not equal - correct
equal - correct

Statement 22
```
DROP TABLE meals
```
```
Table dropped.
```
Statement 23
```
DROP TYPE food_ot FORCE
```
```
Type dropped.
```

Statement 24

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;

Type created.

Statement 25

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

Type created.

Statement 26

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

Type created.

Statement 27

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_ot)
      THEN
         l_ordering :=
            CASE
               WHEN other_food_in IS OF (ONLY dessert_ot) 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_ot) 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;

Type created.

Statement 28

CREATE TABLE meals
(
	served_on	  DATE
 , main_course   food_ot
)

Table created.

Statement 29

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;

Statement processed.

Statement 30

  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

5 rows selected.

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

Statement 31

  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

6 rows selected.

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

Statement 32

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;

Statement processed.
equal - correct
not equal - correct
not equal - correct
food > dessert - correct
dessert > cake - correct
dessert > cake - correct

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database