Search code examples
sqloracle-databaseuser-defined-types

Oracle - object order - how does it work?


Oracle doc says as follows: "As with map methods, an order method, if one is defined, is called automatically whenever two objects of that type need to be compared."

So I assume it will sort when using 'order by' clausule.

Lets say, I have following object:

create or replace type height as object
(
    val number,

    constructor function height return self as result,
    constructor function height (val number) return self as result,

    order member function compare (other height) RETURN INTEGER 
);

and its body implementation:

create or replace type body height is
    constructor function height return self as result is
         begin
            self.val := null;
         end;

    constructor function height (val number) return self as result is
         begin
            self.val := val;
            return;
         end; 

    order member function compare (other height) return integer is
        begin
            if self.val > self.val then return 1;
            elsif self.val < self.val then return -1;
            else return 0;
            end if;
        end;
end;

Then my table looks like this:

CREATE TABLE people (
    name varchar2(50),
    height height
);

And some records ...

insert into people values ('Foo', height(150));
insert into people values ('Bar', height(130));
insert into people valueS ('Baz', height(180));

Then I want to sort it:

select p.height.val from people p order by height ASC;
select p.height.val from people p order by height DESC;

But result is the same for 2 queries, no matter if order is asc or desc.


Solution

  • In turned out to be a simple mistake... Implementation of compare function compares self with self instead with other.