In my previous post I showed how to use the MAP member functions to compare two objects of the same class. The MAP functions are useful when an object can be meaningfully represented and compared by a single value, especially if that value might provide functionality or meaning itself (such as the triangle area mapping in that article.)
Sometimes though the ordering of two objects is based more on a rule or series of rules than any one particular value. The Oracle version mapping function provided in my previous post could fall in to that category. While it was possible to create a mapping function, the resulting value is a little clunky. A more logical comparison function would be to look at each component of the version string compare the corresponding values. That is, if I’m comparing 11.2.0.3.0 to 12.1.0.2.0, then I want to compare 11 to 12 and I know the result. If I have 12.1.0.2.0 and 12.2.0.1.0 then I compare 12 to 12, see they are the same and then compare 1 to 2 to see the result. This kind of logic isn’t possible in a MAP function; but can be implemented with an ORDER member function.
To achieve the ordering logic, we’ll use a constructor to parse the version string into individual elements stored in a collection. Then iterate through the collection elements. Unlike the MAP member function which generates a value from its own object, the ORDER function must compare to another object which is passed in as a parameter. The output of the comparison is a number. If the result is negative then the object is less than the input parameter. If 0 then the object has equivalent ordering to the input parameter, and if positive then the object is considered greater than the input parameter.
First, we’ll declare a collection type to hold the elements.
CREATE OR REPLACE TYPE number_table AS TABLE OF NUMBER;
CREATE OR REPLACE TYPE db_version_obj AS OBJECT
(
version_string VARCHAR2(50),
elements number_table,
CONSTRUCTOR FUNCTION db_version_obj(p_version_string IN VARCHAR2)
RETURN SELF AS RESULT,
ORDER MEMBER FUNCTION sorter(p_obj IN db_version_obj)
RETURN INTEGER
);
CREATE OR REPLACE TYPE BODY db_version_obj
AS
CONSTRUCTOR FUNCTION db_version_obj(p_version_string IN VARCHAR2)
RETURN SELF AS RESULT
IS
BEGIN
version_string := p_version_string;
elements := number_table();
elements.EXTEND(REGEXP_COUNT(p_version_string, '[0-9]+'));
FOR i IN 1 .. elements.COUNT
LOOP
elements(i) :=
TO_NUMBER(REGEXP_SUBSTR(p_version_string,
'[0-9]+',
1,
i));
END LOOP;
RETURN;
END;
ORDER MEMBER FUNCTION sorter(p_obj IN db_version_obj)
RETURN INTEGER
IS
v_index INTEGER := 1;
v_diff INTEGER := 0;
BEGIN
WHILE v_diff = 0 AND v_index <= LEAST(self.elements.COUNT, p_obj.elements.COUNT)
LOOP
v_diff :=
CASE
WHEN self.elements(v_index) IS NOT NULL AND p_obj.elements(v_index) IS NOT NULL
THEN
self.elements(v_index) - p_obj.elements(v_index)
WHEN self.elements(v_index) IS NULL AND p_obj.elements(v_index) IS NULL
THEN
0
WHEN self.elements(v_index) IS NOT NULL
THEN
1
ELSE
-- p_obj.elements(v_index) is not null
-1
END;
v_index := v_index + 1;
END LOOP;
-- if all of the pieces match, check if one side has more pieces
-- example: 11.2 vs 11.2.0.1, in this case 11.2.0.1 is greater.
-- this can produce potentially arguable results like 11.2.0 > 11.2
-- but even if undesirable sometimes they will at least be consistent
IF v_diff = 0
THEN
v_diff := self.elements.COUNT - p_obj.elements.COUNT;
END IF;
RETURN v_diff;
END;
END;
Note the commented section at the end of the sorter function. I wanted the object to be able to sort abbreviated versions correctly, so ‘12.1’ > ‘9.2.0.7’ even though neither one is a full 5-number version string. The following example shows a mix of complete and partial versions sorting correctly.
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 '12.2' FROM DUAL 7 UNION ALL 8 SELECT '12.1' FROM DUAL 9 UNION ALL 10 SELECT '11.1.0.1.0' FROM DUAL 11 UNION ALL 12 SELECT '8.1.7.5.3' FROM DUAL 13 UNION ALL 14 SELECT '10.2.0.1.0' FROM DUAL 15 UNION ALL 16 SELECT '10.1' FROM DUAL 17 UNION ALL 18 SELECT '7.3' FROM DUAL) 19 ORDER BY db_version_obj(ver); VER ---------- 7.3 8.1.7.5.3 9.2.0.7.0 10.1 10.2.0.1.0 11.1.0.1.0 12.1 12.2 12.2.0.1.0
Also note, NULLS must be handled by you when using an ORDER function. If you let the ORDER function return a NULL, you’ll raise an exception.
ORA-22951: NULL returned by ORDER method
In my example, I sort NULLs as less than a populated value. You could reverse that, raise an exception, or change your constructor to cleanse the input data, either by raising an exception or forcing default values when a NULL would be generated. This also means you must hard code the null handling within the object and it can’t be changed at query time using the NULLS FIRST/NULLS LAST modifiers to ORDER BY clauses.
Another thing to consider about the constructor is it isn’t entirely reliable. This is because the elements collection can be manipulated directly. Unfortunately, as of 12cR2, there is no way to hide an attribute in the body of an object. So, an alternate implementation would be to store only the version string itself as an attribute and do the parsing inside the ORDER member.
An implementation of that method might look like this:
CREATE OR REPLACE TYPE db_version_obj AS OBJECT
(
version_string VARCHAR2(50),
ORDER MEMBER FUNCTION sorter(p_obj IN db_version_obj)
RETURN INTEGER
);
CREATE OR REPLACE TYPE BODY db_version_obj
AS
ORDER MEMBER FUNCTION sorter(p_obj IN db_version_obj)
RETURN INTEGER
IS
v_index INTEGER := 1;
v_diff INTEGER := 0;
self_elements number_table := number_table();
other_elements number_table := number_table();
BEGIN
self_elements.EXTEND(REGEXP_COUNT(version_string, '[0-9]+'));
FOR i IN 1 .. self_elements.COUNT
LOOP
self_elements(i) :=
TO_NUMBER(REGEXP_SUBSTR(version_string,
'[0-9]+',
1,
i));
END LOOP;
other_elements.EXTEND(REGEXP_COUNT(p_obj.version_string, '[0-9]+'));
FOR i IN 1 .. other_elements.COUNT
LOOP
other_elements(i) :=
TO_NUMBER(REGEXP_SUBSTR(p_obj.version_string,
'[0-9]+',
1,
i));
END LOOP;
WHILE v_diff = 0 AND v_index <= LEAST(self_elements.COUNT, other_elements.COUNT)
LOOP
v_diff :=
CASE
WHEN self_elements(v_index) IS NOT NULL AND other_elements(v_index) IS NOT NULL
THEN
self_elements(v_index) - other_elements(v_index)
WHEN self_elements(v_index) IS NULL AND other_elements(v_index) IS NULL
THEN
0
WHEN self_elements(v_index) IS NOT NULL
THEN
1
ELSE
-- other_elements(v_index) is not null
-1
END;
v_index := v_index + 1;
END LOOP;
-- if all of the pieces match, check if one side has more pieces
-- example: 11.2 vs 11.2.0.1, in this case 11.2.0.1 is greater.
-- this can produce potentially arguable results like 11.2.0 > 11.2
-- but even if undesirable sometimes they will at least be consistent
IF v_diff = 0
THEN
v_diff := self_elements.COUNT - other_elements.COUNT;
END IF;
RETURN v_diff;
END;
END;
With the end results being the same as earlier, but a little more reliable.
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 '12.2' FROM DUAL 7 UNION ALL 8 SELECT '12.1' FROM DUAL 9 UNION ALL 10 SELECT '11.1.0.1.0' FROM DUAL 11 UNION ALL 12 SELECT '8.1.7.5.3' FROM DUAL 13 UNION ALL 14 SELECT '10.2.0.1.0' FROM DUAL 15 UNION ALL 16 SELECT '10.1' FROM DUAL 17 UNION ALL 18 SELECT '7.3' FROM DUAL) 19 ORDER BY db_version_obj(ver); VER ---------- 7.3 8.1.7.5.3 9.2.0.7.0 10.1 10.2.0.1.0 11.1.0.1.0 12.1 12.2 12.2.0.1.0
As mentioned in the MAP article, ORDER member functions are generally slower than MAP member functions. Mapping occurs once for each object. Then the mapped objects are used to do the sorting. With an ORDER function, their is no predetermined value or set of values to compare directly, so the ORDER function must be executed not just once for every object, but for every comparison! If the code behind your function is complex and expensive it won’t scale well when processing a lot of data. So, if you’ll be working with lots of objects, you may want to consider a mapping, even if it might create a seemingly awkward value.
Sometimes though, speed is irrelevant. If it’s not possible/feasible to create a mapping to a single value, then ORDER functions are the only other option.
One last thing to consider with MAP and ORDER functions is they are mutually exclusive.
A single object type can have only one MAP or one ORDER, but not both, and not more than one of either.