Oracle 18c Documentation is now available on OTN!


https://docs.oracle.com/en/database/oracle/oracle-database/18/index.html

Definitely interested in looking into the json functionality

https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/changes.html#GUID-8C9B00DD-45D8-499B-919C-8632E034B664

and digging in more with analytic views

https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Changes-in-This-Release-for-Oracle-Database-SQL-Language-Reference.html#GUID-9D1C204A-91C3-4B8D-A882-374F78118862

Advertisements

Determining Endian format for your database server.


The servers of your Oracle databases will be of either Big or Little endianness. That is, the ordering of bytes within values. For the most part, the Oracle APIs insulate your code from ever needing to know which method your system is using.

However, there are cases, such as transportable tablespaces or working with binary numbers where the endianness of your server can come into play. Fortunately, Oracle’s data dictionary provides a convenient list of platforms and their corresponding endian configuration.

SQL>   SELECT platform_name, endian_format, platform_id
  2      FROM v$transportable_platform
  3  ORDER BY platform_name;

PLATFORM_NAME                       ENDIAN_FORMAT  PLATFORM_ID
----------------------------------- -------------- -----------
AIX-Based Systems (64-bit)          Big                      6
Apple Mac OS                        Big                     16
Apple Mac OS (x86-64)               Little                  21
HP IA Open VMS                      Little                  19
HP Open VMS                         Little                  15
HP Tru64 UNIX                       Little                   5
HP-UX (64-bit)                      Big                      3
HP-UX IA (64-bit)                   Big                      4
IBM Power Based Linux               Big                     18
IBM zSeries Based Linux             Big                      9
Linux IA (32-bit)                   Little                  10
Linux IA (64-bit)                   Little                  11
Linux x86 64-bit                    Little                  13
Microsoft Windows IA (32-bit)       Little                   7
Microsoft Windows IA (64-bit)       Little                   8
Microsoft Windows x86 64-bit        Little                  12
Solaris Operating System (x86)      Little                  17
Solaris Operating System (x86-64)   Little                  20
Solaris[tm] OE (32-bit)             Big                      1
Solaris[tm] OE (64-bit)             Big                      2

20 rows selected.

With a simple join to v$database you can extract your system’s platform id and lookup which endian format your system uses.

So, for example, on one of my Linux servers I run this query and I can see my database is running on a Little endian system.

SQL> SELECT p.endian_format
  2    FROM v$transportable_platform p
  3    INNER JOIN
  4         v$database d
  5      ON p.platform_id = d.platform_id;

ENDIAN_FORMAT
--------------
Little

One caveat to these queryies is you need extra privileges to run them. By default they are not open to PUBLIC. So, one option, obviously, you can simply ask your DBA to look up the values for you; but you can also derive the endian format yourself by converting binary values and comparing to big and little values to see which one you get.

Using the UTL_RAW package (which has PUBLIC execute,) we can choose big, little, or native conversion. So, by converting a value to all 3 we can check if our native conversion matches the big value or the little value and we’ll know which endian format our system uses. As expected the Little and Machine formats are the same.

SQL> SELECT UTL_RAW.cast_to_binary_integer(HEXTORAW('123'), 1) big,
  2         UTL_RAW.cast_to_binary_integer(HEXTORAW('123'), 2) little,
  3         UTL_RAW.cast_to_binary_integer(HEXTORAW('123'), 3) machine
  4    FROM DUAL;

       BIG     LITTLE    MACHINE
---------- ---------- ----------
       291       8961       8961

We can make the output a little friendlier with a CASE statement on the results and once again we see my test system is Little endian.

SQL> SELECT CASE WHEN machine = big THEN 'Big'
  2              WHEN machine = little THEN 'Little'
  3         END endian
  4    FROM (SELECT UTL_RAW.cast_to_binary_integer(HEXTORAW('123'), 1) big,
  5                 UTL_RAW.cast_to_binary_integer(HEXTORAW('123'), 2) little,
  6                 UTL_RAW.cast_to_binary_integer(HEXTORAW('123'), 3) machine
  7            FROM DUAL);

ENDIAN
------
Little

It’s always nice as a developer to have escalated privileges, but not having them doesn’t mean all information necessarily remains hidden. Hopefully this little trick will prove handy for you if/when you need it.

Using Object Types: User-Defined Aggregates – Skewness


One of the more interesting features of Oracle object types, is the Oracle Data Cartridge Interface (ODCI.) Using ODCI you can create your own aggregate functions like MIN, MAX, and AVG.

In this article I’ll show how to create an aggregate function to measure skewness of a sample. That is, an indication of how asymmetric points are within a distribution.

In this example I’m using an unweighted formula to determine skew:

n/((n-1)(n-2)) SUM((x(i) – m)/s)^3

Where:

  • n = number of elements in the sample
  • x(i) = the ith element with the sample (Sum where i=1..n)
  • m = the mean of the sample
  • s = the standard deviation of the sample

Using native functions it’s possible to perform the calculation:

So here I’ll build a test case with sample data of known skewness.
SKEWNESS(1,2,4,8,16) = 1.32531471

SQL> WITH sample_data AS
  2     (SELECT COLUMN_VALUE x FROM TABLE(numarray(1,2,4,8,16)))
  3  SELECT SUM(z) skewness
  4    FROM (SELECT   COUNT(*) OVER ()
  5                 / (COUNT(*) OVER () - 1)
  6                 / (COUNT(*) OVER () - 2)
  7                 * POWER((x - AVG(x) OVER ()) / STDDEV(x) OVER (), 3)
  8                     z
  9            FROM sample_data);

  SKEWNESS
----------
1.32531471

Now, let’s build an aggregate to do it for us so we can invoke it with a simple:

SELECT skewness(x)
  FROM sample_data;

 
Unfortunately, we can’t use native aggregates within our user-defined aggregate. So, we’ll have to generate the count, mean, and standard deviation ourselves. To that end, we’ll keep a running count and total along with the actual values as we go.

CREATE OR REPLACE TYPE skewness_agg_type
    AS OBJECT
(
    v_count INTEGER,
    v_mean_sum NUMBER,
    v_values numarray,
    STATIC FUNCTION odciaggregateinitialize(ctx IN OUT skewness_agg_type)
        RETURN NUMBER,
    MEMBER FUNCTION odciaggregateiterate(self IN OUT skewness_agg_type, p_value IN NUMBER)
        RETURN NUMBER,
    MEMBER FUNCTION odciaggregatemerge(self IN OUT skewness_agg_type, ctx2 IN skewness_agg_type)
        RETURN NUMBER,
    MEMBER FUNCTION odciaggregateterminate(
        self          IN     skewness_agg_type,
        returnvalue      OUT NUMBER,
        flags         IN     NUMBER
    )
        RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY skewness_agg_type
IS
    STATIC FUNCTION odciaggregateinitialize(ctx IN OUT skewness_agg_type)
        RETURN NUMBER
    IS
    BEGIN
        -- Initialize to 0 count, 0 sum, and empty collection
        ctx := skewness_agg_type(0, 0, numarray());
        RETURN odciconst.success;
    END odciaggregateinitialize;

    MEMBER FUNCTION odciaggregateiterate(self IN OUT skewness_agg_type, p_value IN NUMBER)
        RETURN NUMBER
    IS
    BEGIN
        -- For each new value, increment the count, and sum
        -- and add the value to our collection
        self.v_count := self.v_count + 1;
        self.v_mean_sum := self.v_mean_sum + p_value;
        self.v_values.EXTEND;
        self.v_values(self.v_count) := p_value;
        RETURN odciconst.success;
    END odciaggregateiterate;

    MEMBER FUNCTION odciaggregatemerge(self IN OUT skewness_agg_type, ctx2 IN skewness_agg_type)
        RETURN NUMBER
    IS
    BEGIN
        -- If merging two sub-results (possibly from parallel threads)
        --  then the count of the total is sum of each intermediate count
        --  The sum is the sum of each intermediate sum
        --  And the collection should hold all values from both intermediate results
        self.v_count := self.v_count + ctx2.v_count;
        self.v_mean_sum := self.v_mean_sum + ctx2.v_mean_sum;
        self.v_values := self.v_values MULTISET UNION ALL ctx2.v_values;
        RETURN odciconst.success;
    END odciaggregatemerge;

    MEMBER FUNCTION odciaggregateterminate(
        self          IN     skewness_agg_type,
        returnvalue      OUT NUMBER,
        flags         IN     NUMBER
    )
        RETURN NUMBER
    IS
        v_variance   NUMBER := 0;
        v_stddev     NUMBER;
        v_mean       NUMBER;
        v_temp       NUMBER := 0;
    BEGIN
        -- Implement the formula
        -- n/((n-1)(n-2)) SUM((x(i) - m)/s)^3
        --
        -- Where:
        --   n = number of elements in the sample
        --   x(i) = the ith element with the sample (Sum where i=1..n)
        --   m = the mean of the sample
        --   s = the standard deviation of the sample
    
        -- Calculate the mean from the running total and count
        v_mean := self.v_mean_sum / self.v_count;

        -- Standard deviation = square root of (variance/(N-1))
        -- So, first step is calculate the variance by iterating through all of the values
        FOR i IN 1 .. self.v_count
        LOOP
            v_variance := v_variance + ((self.v_values(i) - v_mean) ** 2);
        END LOOP;

        v_stddev := SQRT(v_variance / (self.v_count - 1));

        -- Sum the cube of the difference from mean divided by standard deviation
        FOR i IN 1 .. self.v_count
        LOOP
            v_temp := v_temp + POWER((self.v_values(i) - v_mean) / v_stddev, 3);
        END LOOP;

        -- apply count factoring against the sum
        returnvalue := self.v_count / (self.v_count - 1) / (self.v_count - 2) * v_temp;
        RETURN odciconst.success;
    END odciaggregateterminate;
END;
/

--- Create function implementing the object 
CREATE OR REPLACE FUNCTION skewness(p_value NUMBER)
    RETURN NUMBER
    PARALLEL_ENABLE
    AGGREGATE USING skewness_agg_type;

Now we have an aggregate function we can test it with the same sample data above and should get the same result.

SQL> WITH sample_data AS
  2     (SELECT COLUMN_VALUE x FROM TABLE(numarray(1,2,4,8,16)))
  3  SELECT skewness(x)
  4    FROM sample_data;

SKEWNESS(X)
-----------
 1.32531471

Obviously, this function does require your input data to have at least 3 values.
If you have fewer than 3 values you’ll get an “ORA-01476: divisor is equal to zero” exception because of the n/(n-1)/(n-2) division. That could be handled internally to return NULL or some other exception if you’d rather.

If you are unfamiliar with the ODCI syntax, the constructs above may appear overly complex; but they are actually fairly easy to use.

Each aggregate needs 4 parts

  • initializer – this is the method invoked by the function before processing any rows.
  • iterator – this method is invoked once for each row processed
  • merge – this method might not be called but is necessary to ensure proper processing of sub-results. The most common reason to encounter these would be parallel queries where 2 threads each process some subset of the data an the PQ coordinator must merge the results into a single total result
  • terminator – this method is invoked after the last row is processed and a final result needs to be returned

It’s usually better to use native functions if possible for maximum efficiency. However if the resulting code complexity outweighs the need for speed or if it’s simply not possible to implement the result directly then the ODCI functionality may be exactly the tools needed.

I’ll provide other, hopefully, interesting and useful aggregates in future articles.

Using Object Types: ORDER member functions


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  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  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.

Using Object Types: MAP member functions


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 = 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;
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.

  1. 10^0 * 0 +
  2. 10^6 * 1 +
  3. 10^12 * 0 +
  4. 10^18 * 2 +
  5. 12^24 * 12

This sum produces the value: 12000002000000000001000000.

Given a version of 9.2.0.7.0 we follow the same process

  1. 10^0 * 0 +
  2. 10^6 * 7 +
  3. 10^12 * 0 +
  4. 10^18 * 2 +
  5. 12^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.

Using Object Types: constructors


When an object type is instantiated (i.e. assigned a non-null value) it will execute initialization code. This initialization code is called a CONSTRUCTOR function. Every object type includes an implicit constructor which simply assigns values to the object attributes according to the values provided in the object assignment.

For example:

CREATE OR REPLACE TYPE simpleobject AS OBJECT(a NUMBER, b NUMBER, c NUMBER);

I can create an instance of the object by providing it’s name and values for the attributes.

SQL> select simpleobject(1,2,3) example from dual;

EXAMPLE(A, B, C)
----------------------
SIMPLEOBJECT(1, 2, 3)

or, in pl/sql

SQL> set serveroutput on
SQL> DECLARE
  2      v_obj   simpleobject := simpleobject(1, 2, 3);
  3  BEGIN
  4      DBMS_OUTPUT.put_line(v_obj.a);
  5      DBMS_OUTPUT.put_line(v_obj.b);
  6      DBMS_OUTPUT.put_line(v_obj.c);
  7  END;
  8  /
1
2
3

That’s easy enough, but what if we don’t always know all of the values and only want to define some of them. In that case we define our own constructor by creating a function with the CONSTRUCTOR key word and naming it the same as our object. It’s not strictly necessary to declare the first parameter (SELF) as it will be implicit in any constructor, but it is common practice to do so.

So, either of these versions will provide equivalent functionality. First, with the SELF parameter explicitly defined:

CREATE OR REPLACE TYPE simpleobject AS OBJECT
(
    a NUMBER,
    b NUMBER,
    c NUMBER,
    CONSTRUCTOR FUNCTION simpleobject(self IN OUT simpleobject, p_a IN NUMBER)
        RETURN SELF AS RESULT
);

CREATE OR REPLACE TYPE BODY simpleobject
IS
    CONSTRUCTOR FUNCTION simpleobject(self IN OUT simpleobject, p_a IN NUMBER)
        RETURN SELF AS RESULT
    IS
    BEGIN
        self.a := p_a;

        RETURN;
    END;
END;

or using the implicit parameter:

CREATE OR REPLACE TYPE simpleobject AS OBJECT
(
    a NUMBER,
    b NUMBER,
    c NUMBER,
    CONSTRUCTOR FUNCTION simpleobject(a IN NUMBER)
        RETURN SELF AS RESULT
);

CREATE OR REPLACE TYPE BODY simpleobject
IS
    CONSTRUCTOR FUNCTION simpleobject(a IN NUMBER)
        RETURN SELF AS RESULT
    IS
    BEGIN
        self.a := a;
        
        RETURN;
    END;
END;

Now we have the option of instantiating with just the a parameter, or using all three attributes.

SELECT simpleobject(1), simpleobject(1, 2, 3) FROM DUAL;

DECLARE
    v_obj   simpleobject := simpleobject(1, 2, 3);
BEGIN
    DBMS_OUTPUT.put_line(v_obj.a);
    DBMS_OUTPUT.put_line(v_obj.b);
    DBMS_OUTPUT.put_line(v_obj.c);
END;

DECLARE
    v_obj   simpleobject := simpleobject(1);
BEGIN
    DBMS_OUTPUT.put_line(v_obj.a);
    DBMS_OUTPUT.put_line(v_obj.b);
    DBMS_OUTPUT.put_line(v_obj.c);
END;

Those are fairly simple. Let’s get a little more complicated. I’ll create a triangle object. It will have 6 attributes containing the 3 sides and 3 angles. Since the angles can be calculated if we have the 3 sides, we’ll create a constructor that only requires the side values and we’ll calculate the angles ourselves.

The triangle object then would look like this with attributes for the sides X,Y,Z and the corresponding angles opposite those sides, also X,Y, and Z.
The constructor accepts 3 values for the sides.

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
);

In the body we can our constructor is quite a bit more complicated than the previous “simpleobject”. First I sort the inputs by size, and then using some rules from trigonometry we can calculate the values of the angles and set the attributes. I also add some parameter checking to make sure the input values can form a valid triangle. If they don’t then raise the VALUE_ERROR exception.

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;
END;

Trying out the object with some sample values, we see the sides are populated in descending order of length and each sides opposite angle is calculated correctly.

SQL> SELECT my_triangle(3, 4, 2) FROM DUAL;

MY_TRIANGLE(3,4,2)(X_SIDE, Y_SIDE, Z_SIDE, X_ANGLE, Y_ANGLE, Z_ANGLE)
----------------------------------------------------------------------
MY_TRIANGLE(4, 3, 2, 104.477512, 46.5674634, 28.9550244)

SQL> SELECT my_triangle(4, 3, 5) FROM DUAL;

MY_TRIANGLE(4,3,5)(X_SIDE, Y_SIDE, Z_SIDE, X_ANGLE, Y_ANGLE, Z_ANGLE)
----------------------------------------------------------------------
MY_TRIANGLE(5, 4, 3, 90, 53.1301024, 36.8698976)

SQL> SELECT my_triangle(7, 7, 7) FROM DUAL;

MY_TRIANGLE(7,7,7)(X_SIDE, Y_SIDE, Z_SIDE, X_ANGLE, Y_ANGLE, Z_ANGLE)
----------------------------------------------------------------------
MY_TRIANGLE(7, 7, 7, 60, 60, 60)

As you can see, defining your own constructors allows for a lot of versatility in how you can use and populate your object’s attributes and I highly recommend exploring their use, looking for opportunities where they may benefit your object. From simply defaulting to nulls as shown in the simpleobject, to calculated results, or possibly just some formatting, such as using UPPER, LOWER, ROUND, etc. on your values at instantiation time to act as data cleanup.

Using Object Types: consolidating multiple values


A lot of developers see the Object Types as confusing, esoteric, or simply unrelated to their SQL work.  To be fair, some objects can be quite complex and some of the data cartridge (ODCI) features will never find a home in some developer’s applications. But, that still shouldn’t preclude an examination of some of the fundamentals of this extremely useful feature.

The most basic feature of an object is that it can be used to consolidate multiple values into attributes of a single entity.  Similar to a record declared in PL/SQL an object type allows you to have a reference a single value that acts as a container for other values.

Why might that be useful?  After all, a SQL statement can already reference multiple values (columns) from a table, or invoke multiple functions calls, or return values from sub-queries.  So each row returned by a SQL statement can already be viewed as a consolidated set of values.  What does an object type have to offer beyond what we already have?

First – lets look at functions.  Lets say I have list of sales to various customers and I want to call a function that returns detail information about those sales.

I might have a function that returns product name, another function returns weight, and yet another returning price.  So, if I want to gather all of that information I have to call all 3 functions.  Behind the scenes, each of those functions will likely execute a SQL statement to fetch a row from the product table and extract the relevant column.

Obviously that’s not very efficient.  Better would be to have a single function call that could return all 3 values.  But, we all know a function can only return one value, so we’re kind of stuck!  There are, of course, other options, such as caching values within PL/SQL records or collections thereby reducing the IO penalties, but still necessitating multiple function calls and possibly context switches.  While still viable for some problems; those options are outside the scope of this article.

So, what would the Object Type solution look like?

First, let’s create an object to hold are 3 product values.

CREATE OR REPLACE TYPE product_info 
  AS OBJECT(
    product_name   VARCHAR2(50), 
    product_weight NUMBER,
    product_price  NUMBER
);

Next, create a function to return a PRODUCT_INFO value.

CREATE OR REPLACE FUNCTION get_product_info(p_product_id IN products.id%TYPE)
    RETURN product_info
IS
    v_info   product_info;
BEGIN
    SELECT product_info(name, weight, price)
      INTO v_info
      FROM products
     WHERE id = p_product_id;

    RETURN v_info;
END;

Now we can use our new function and type within a SQL statement.

SELECT order_date, product_id, quantity, get_product_info(product_id) product
  FROM sales
 WHERE customer_id = 1234 AND order_date = DATE '2017-01-11';

ORDER_DATE          PRODUCT_ID QUANTITY PRODUCT(PRODUCT_NAME, PRODUCT_WEIGHT, PRODUCT_PRICE)
------------------- ---------- -------- -----------------------------------------------------
2017-01-11 00:00:00         50        1 PRODUCT_INFO('Bananas', 1, .86)
2017-01-11 00:00:00         35        3 PRODUCT_INFO('Apples-Gala', .33, .4)

Depending on the tool used, the object type may be represented in different forms. OBJECT_NAME(FIELD_1, FIELD_2, FIELD_3, etc.) is how SQL*Plus displays objects.

The most important at this stage though is confirming our function works. We got the expected results. In particular, the three values we need are returned as a single column containing our object.

Next though, because we’re using SQL, it’s likely we’ll want the results returned in rows and columns of simple, scalar values. So, let’s extract the individual attributes of our object out into their respective columns for final output. We’ll do that with an inline view of the previous query and then pull the attributes out by name. Note, when dereferencing an object to get to the attributes, you must wrap the object name in parentheses to create an object expression.

SQL> SELECT order_date,
  2         product_id,
  3         quantity,
  4         (product).product_name product_name,
  5         (product).product_weight weight,
  6         (product).product_price price
  7    FROM (SELECT order_date,
  8                 product_id,
  9                 quantity,
 10                 get_product_info(product_id) product
 11            FROM sales
 12           WHERE customer_id = 1234 AND order_date = DATE '2017-01-11');

ORDER_DATE          PRODUCT_ID   QUANTITY PRODUCT_NAME   WEIGHT  PRICE
------------------- ---------- ---------- ------------- ------- ------
2017-01-11 00:00:00         50          1 Bananas             1    .55
2017-01-11 00:00:00         35          3 Apples-Gala       .33     .4

You can also use objects within a scalar subquery in order to return consolidate multiple values from a lookup table into a single object for the subquery to return. When doing so you should test if a simple join to pull the related values might be more efficient or equivalent but simpler.

For example, if the function above was not available and I had direct access to the underlying product table then I could write the query like this:

SELECT order_date,
       product_id,
       quantity,
       (product).product_name product_name,
       (product).product_weight weight,
       (product).product_price price
  FROM (SELECT order_date,
               product_id,
               quantity,
               (SELECT product_info(name, weight, price)
                  FROM products
                 WHERE products.id = sales.product_id)
                   product
          FROM sales
         WHERE customer_id = 2576 AND product_id = 35);

Elapsed: 00:00:00.03

Statistics
---------------------------------------------------------
          0  recursive calls
          0  db block gets
       4464  consistent gets
          0  physical reads
          0  redo size
        972  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         16  rows processed

or, with a simple join as shown below (in this case a referential constraint makes an inner join equivalent to the functionality above, but if not then an outer join to products could be used.)

SELECT s.order_date,
       s.product_id,
       s.quantity,
       p.name,
       p.weight,
       p.price
  FROM sales s, products p
 WHERE s.customer_id = 2576 
  AND s.product_id = 35
  AND p.id = s.product_id;

Elapsed: 00:00:00.03

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4464  consistent gets
          0  physical reads
          0  redo size
        964  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         16  rows processed

In this case the join is simpler syntax for equivalent performance and resource consumption. Also consider, this was a simple example. Some data may not be available through a table and may require a function. Either because of api design and user privileges or because the data comes from a non-sql source, such as as a web service, remote file, or other pl/sql routines.

When building such functions it is important to consider use cases that may require multiple pieces of output. When those are found, it might be appropriate to return an XML or JSON document; but it may be easier and more efficient to return an object type.

%d bloggers like this: