If you have 2 instances of an object, A and B, you might want to compare them: is A > B or B > A? By default, two instances can’t be compared for greater or lesser value, only for equality. Where equality is determined by checking each attribute of each instance in order and comparing if they are equal or not.
If you try to check the ordering you’ll get
ORA-22950: cannot ORDER objects without MAP or ORDER method
For example, using the MY_TRIANGLE object defined in my previous post we can attempt the various comparisons, but only equality comparisons are valid.
SQL> select * from dual where my_triangle(2,3,4) = my_triangle(2,3,4);
D
-
X
SQL> select * from dual where my_triangle(2,3,4) = my_triangle(3,4,5);
no rows selected
SQL> select * from dual where my_triangle(2,3,4) > my_triangle(3,4,5);
select * from dual where my_triangle(2,3,4) > my_triangle(3,4,5)
*
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method
SQL> select * from dual where my_triangle(2,3,4) < my_triangle(2,3,4);
select * from dual where my_triangle(2,3,4) < my_triangle(2,3,4)
*
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method
In this article, we'll look at MAP methods.
Mapping translates your object into simple scalar value such as a number, date, or varchar2. For example, I might add a MAP method to my triangle object to yield the sum of the 3 sides, or the area of the triangle. If I had an object of U.S. states, I might map them to a number by population in most recent census, or square miles of area. Maybe map it to date of statehood when officially joining the union, or possibly just map them to a text field of the state name for alphabetic sorting. The MAP method is a pl/sql function so the value returned may be the result of a calculation, a lookup value, or simply returning the value of attribute already known within the object.
Let's add a mapping function to my_triangle based on the area of the triangle. The code is the same except for the newly added MAP functionality in bold.
CREATE OR REPLACE TYPE my_triangle AS OBJECT
(
x_side NUMBER,
y_side NUMBER,
z_side NUMBER,
x_angle NUMBER,
y_angle NUMBER,
z_angle NUMBER,
CONSTRUCTOR FUNCTION my_triangle(self IN OUT my_triangle,
p_aside IN NUMBER,
p_bside IN NUMBER,
p_cside IN NUMBER)
RETURN SELF AS RESULT,
MAP MEMBER FUNCTION area RETURN NUMBER
);
/
Then, our object includes attributes for the three sides, so we'll use Heron's Formula to calculate the area from those values.
CREATE OR REPLACE TYPE BODY my_triangle
IS
CONSTRUCTOR FUNCTION my_triangle(self IN OUT my_triangle,
p_aside IN NUMBER,
p_bside IN NUMBER,
p_cside IN NUMBER)
RETURN SELF AS RESULT
IS
-- sides
a NUMBER;
b NUMBER;
c NUMBER;
-- angles
aa NUMBER;
bb NUMBER;
cc NUMBER;
BEGIN
-- All sides of a triangle must have positive length
IF p_aside <= 0 OR p_bside <= 0 OR p_cside <= 0
THEN
RAISE VALUE_ERROR;
END IF;
-- Assign a,b,c in descending order of parameter values
-- Using Cosine rule. solve for angle AA, opposite side a.
-- Then using Sine rule, solve for angle BB, opposite side b.
-- Once we know AA and BB, CC is calculated simply with 180 - AA - BB
-- The Oracle "acos" function return values in radians,
-- so convert all angles to degrees before assigning to object attributes
CASE GREATEST(p_aside, p_bside, p_cside)
WHEN p_aside
THEN
a := p_aside;
b := GREATEST(p_bside, p_cside);
c := LEAST(p_bside, p_cside);
WHEN p_bside
THEN
a := p_bside;
b := GREATEST(p_aside, p_cside);
c := LEAST(p_aside, p_cside);
WHEN p_cside
THEN
a := p_cside;
b := GREATEST(p_aside, p_bside);
c := LEAST(p_aside, p_bside);
END CASE;
-- Check Triangle Inequality Theorem
-- That is, the sum of the lengths of any two sides must be greater than the length of the third side.
-- Since we have ordered sides such that a >= b >= c, it is sufficient to test b+c> a
IF b + c <= a
THEN
RAISE VALUE_ERROR;
END IF;
aa := ACOS((b * b + c * c - a * a) / (2 * b * c)); -- cosine rule
bb := ASIN(b * SIN(aa) / a); -- sine rule
aa := aa * 180 / ACOS(-1); -- convert radians to degrees
bb := bb * 180 / ACOS(-1); -- convert radians to degrees
cc := 180 - aa - bb;
self.x_side := a;
self.y_side := b;
self.z_side := c;
self.x_angle := aa;
self.y_angle := bb;
self.z_angle := cc;
RETURN;
END;
MAP MEMBER FUNCTION area
RETURN NUMBER
IS
a NUMBER := x_side;
b NUMBER := y_side;
c NUMBER := z_side;
s NUMBER := (a + b + c) / 2;
BEGIN
-- We know the lengths of the 3 sides
-- so use Heron's Formula to calculate the area
RETURN SQRT(s * (s - a) * (s - b) * (s - c));
END;
END;
/
Now we can compare our triangles
SQL> select * from dual where my_triangle(2,3,4) > my_triangle(3,4,5); no rows selected SQL> select * from dual where my_triangle(2,3,4) < my_triangle(3,4,5); D - X
Another nice feature of MAP methods is they can still be invoked as other methods. So, if you simply want to know the area of a given triangle and not necessarily compare it to anything else, the method is still viable.
SQL> select my_triangle(2,3,4).area() from dual;
MY_TRIANGLE(2,3,4).AREA()
-------------------------
2.90473751
SQL> select my_triangle(3,4,5).area() from dual;
MY_TRIANGLE(3,4,5).AREA()
-------------------------
6
Next lets pursue the sorting functionality more directly by fixing a problem with Oracle version numbers. If you have a list of databases, maybe from OEM or some other tool where each database has a different version, you might want to sort them; but version numbers are actually text. So, while it seems natural for 12.2.0.1.0 to follow 9.2.0.7.0 it won't when sorted because the text "9" is greater than the text "1". A simple way to address this is with an object type that can map version number text to a sortable value that is actually numeric.
Oracle versions are made up of 5 numeric parts. Using 12.2.0.1.0 as an example the parts are as follows.
- Major release (12)
- Minor release (2)
- App server number (0)
- Patch number (1)
- Platform specific patch number (0)
To produce the map we'll multiply each sub-value within the version to offset them within a single larger numeric value.
Most of the sub-values are 1 or 2 digits, but the platform patch can be up to 6 digits. For simplicity I'll pad all the sub-values to the same length - 6.
Working from the least-significant to most significant sub-value, multiply each by increasing offsets of 6 digits and sum them together.
- 10^0 * 0 +
- 10^6 * 1 +
- 10^12 * 0 +
- 10^18 * 2 +
- 10^24 * 12
This sum produces the value: 12000002000000000001000000.
Given a version of 9.2.0.7.0 we follow the same process
- 10^0 * 0 +
- 10^6 * 7 +
- 10^12 * 0 +
- 10^18 * 2 +
- 10^24 * 9
Which yields 9000002000000000007000000
These values are large and unwieldy but; unlike the area method of the triangle object, these map values are meant solely for comparison purposes.
Creating a sortable object is relatively simple, simply extract each part, turn it into a number, multiply, and sum.
CREATE OR REPLACE TYPE db_version_obj AS OBJECT
(
version_string VARCHAR2(50),
MAP MEMBER FUNCTION mapvalue
RETURN INTEGER
);
CREATE OR REPLACE TYPE BODY db_version_obj
AS
MAP MEMBER FUNCTION mapvalue
RETURN INTEGER
IS
-- Oracle versions are made up of 5 numeric parts
-- For example 12.2.0.1.0
-- Major release (12)
-- Minor release (2)
-- App server number (0)
-- Patch number (1)
-- Platform specific patch number (0)
--
-- For the map function, we'll turn the string into single large number
-- where each part maps to 6 digits within the overall number
BEGIN
IF version_string IS NULL
THEN
RETURN NULL;
ELSE
RETURN TO_NUMBER(REGEXP_SUBSTR(version_string,'[0-9]+',1,1)) * POWER(1000000, 4)
+ TO_NUMBER(REGEXP_SUBSTR(version_string,'[0-9]+',1,2)) * POWER(1000000, 3)
+ TO_NUMBER(REGEXP_SUBSTR(version_string,'[0-9]+',1,3)) * POWER(1000000, 2)
+ TO_NUMBER(REGEXP_SUBSTR(version_string,'[0-9]+',1,4)) * POWER(1000000, 1)
+ TO_NUMBER(REGEXP_SUBSTR(version_string,'[0-9]+',1,5));
END IF;
END;
END;
And using it is quite simple too
SQL> SELECT ver 2 FROM (SELECT '12.2.0.1.0' ver FROM DUAL 3 UNION ALL 4 SELECT '9.2.0.7.0' FROM DUAL 5 UNION ALL 6 SELECT '11.1.0.1.0' FROM DUAL 7 UNION ALL 8 SELECT '8.1.7.5.3' FROM DUAL 9 UNION ALL 10 SELECT '10.2.0.1.0' FROM DUAL) 11 ORDER BY db_version_obj(ver); VER ---------- 8.1.7.5.3 9.2.0.7.0 10.2.0.1.0 11.1.0.1.0 12.2.0.1.0
This particular example is maybe a little contrived since the same functionality could be achieved with a normal pl/sql function returning the same value as the object method. The point of this example was to illustrate creating an arcane value that is still useful for sorting. Similar techniques could be used for more complex objects. For example mapping a car object's make, model, and year to a number or text value which is then sortable.
If some of these mapping seem too convoluted simple to create a strange value that is only usable in sorting, then it's possible a MAP function is not what you need and instead should examine an ORDER member function which will be the topic of my next blog entry. However, it should be noted MAP functions do tend to be more efficient for comparisons because the mapping is performed once for each object and then the mapped values are used in the sorting. So, you will need to consider your use cases. In my example of database versions, maybe I just have a few in a report so it won't make much difference; but if I had to pick for performance I'd go with MAP. If I'm working with some sort of graphics application that uses thousands of triangles for rendering of images it will probably be more efficient to use the MAP function instead of ORDER.
Also note, if the mapping returns a NULL, then those objects will obey the default ordering of NULLS LAST, but can be reversed with NULLS FIRST in the ORDER BY clause.