Oracle Collections and Object Types: Comparing for Equality

Oracle can easily compare data items of scalar data types (those that hold a single data value with no internal components — e.g. NUMBER, DATE or VARCHAR2). So, if a and b are two NUMBER variables, all you need to do to check whether they are the same or not, is test "if (a=b)".

Can we extend this simple equality check to instances of PL/SQL collections or object types (UDTs)? Let’s find out.

Comparing PL/SQL Collections of Scalar Data types

You can, in fact, compare two nested tables of scalar data types with the same ease as "a=b".

SQL> -- Comparing nested tables of scalar datatypes
SQL> declare
  2    type tbl_n is table of number;
  3
  4    a   tbl_n := tbl_n(3,21,5);
  5    b   tbl_n;
  6
  7  begin
  8
  9    -- Case 1: b is identical to a
 10    b := tbl_n(3,21,5);
 11
 12    if (a = b) then
 13      dbms_output.put_line('Case 1: a and b are the same');
 14    else
 15      dbms_output.put_line('Case 1: a and b are different');
 16    end if;
 17
 18    -- Case 2: b is not identical to a
 19    b := tbl_n(3,21,5,9);
 20
 21    if (a = b) then
 22      dbms_output.put_line('Case 2: a and b are the same');
 23    else
 24      dbms_output.put_line('Case 2: a and b are different');
 25    end if;
 26
 27  end;
 28  /
Case 1: a and b are the same
Case 2: a and b are different

PL/SQL procedure successfully completed.

Note: The ‘=’ operator works with nested table comparison, not with varray comparison.

SQL> -- Comparing varrays of scalar datatypes
SQL> declare
  2    type tbl_n is varray(3) of number;
  3
  4    a   tbl_n := tbl_n(3,21,5);
  5    b   tbl_n;
  6
  7  begin
  8
  9    -- Case 1: b is identical to a
 10    b := tbl_n(3,21,5);
 11
 12    if (a = b) then
 13      dbms_output.put_line('Case 1: a and b are the same');
 14    else
 15      dbms_output.put_line('Case 1: a and b are different');
 16    end if;
 17
 18    -- Case 2: b is not identical to a
 19    b := tbl_n(3,21,59);
 20
 21    if (a = b) then
 22      dbms_output.put_line('Case 2: a and b are the same');
 23    else
 24      dbms_output.put_line('Case 2: a and b are different');
 25    end if;
 26
 27  end;
 28  /
  if (a = b) then
        *
ERROR at line 12:
ORA-06550: line 12, column 9:
PLS-00306: wrong number or types of arguments in call to '='
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored
ORA-06550: line 21, column 9:
PLS-00306: wrong number or types of arguments in call to '='
ORA-06550: line 21, column 3:
PL/SQL: Statement ignored

Comparing Objects (User-Defined Types)

Now consider objects of a user-defined type (UDT), and try to compare them with the ‘=’ operator.

SQL> create or replace type user_obj is object
  2  ( user_id number
  3  , user_name varchar2(50)
  4   );
  5  /

Type created.

We want two objects to be considered equal if they are of the same TYPE, and contain identical values for each attribute. Try comparing two objects of type user_obj.

SQL> -- Comparing objects
SQL> declare
  2
  3    a   user_obj := user_obj(1, 'Mark');
  4    b   user_obj;
  5
  6  begin
  7
  8    -- Case 1: b is identical to a
  9    b := user_obj(1, 'Mark');
 10
 11    if (a = b) then
 12      dbms_output.put_line('Case 1: a and b are the same');
 13    else
 14      dbms_output.put_line('Case 1: a and b are different');
 15    end if;
 16
 17    -- Case 2: b is not identical to a
 18    b := user_obj(1, 'Malcolm');
 19
 20    if (a = b) then
 21      dbms_output.put_line('Case 2: a and b are the same');
 22    else
 23      dbms_output.put_line('Case 2: a and b are different');
 24    end if;
 25
 26  end;
 27  /
  if (a = b) then
        *
ERROR at line 11:
ORA-06550: line 11, column 9:
PLS-00526: A MAP or ORDER function is required for comparing objects in PL/SQL.
ORA-06550: line 20, column 9:
PLS-00526: A MAP or ORDER function is required for comparing objects in PL/SQL.

Well, that didn’t work — but unlike in the case of MULTISET operations, the error message told us the exact solution to fix the problem.

Create a MAP function for the object TYPE, then compare objects

The MAP method translates or "maps" each object into a scalar data type, which informs Oracle how to compare objects.

We’ll add a TYPE body with a MAP method, returning the concatenated RAW of all attributes:

SQL> create or replace type user_obj is object
  2  ( user_id number
  3  , user_name varchar2(50)
  4  , map member function equals return raw
  5   );
  6  /

Type created.

SQL> create or replace type body user_obj as
  2
  3    map member function equals return raw as
  4    begin
  5    -- Return concatenated RAW string of
  6    -- all attributes of the object
  7    return
  8      -- NVL() to avoid NULLS being treated
  9      -- as equal. NVL default values: choose
 10      -- carefully!
 11      utl_raw.cast_to_raw(
 12         nvl(self.user_id, -1)
 13      || nvl(self.user_name, '***')
 14        );
 15    end equals;
 16
 17  end;
 18  /

Type body created.

Now we have informed Oracle how it should compare objects. Let’s try the PL/SQL code for equality check once again:

SQL> -- Comparing objects (with MAP method defined)
SQL> declare
  2
  3    a   user_obj := user_obj(1, 'Mark');
  4    b   user_obj;
  5
  6  begin
  7
  8    -- Case 1: b is identical to a
  9    b := user_obj(1, 'Mark');
 10
 11    if (a = b) then
 12      dbms_output.put_line('Case 1: a and b are the same');
 13    else
 14      dbms_output.put_line('Case 1: a and b are different');
 15    end if;
 16
 17    -- Case 2: b is not identical to a
 18    b := user_obj(1, 'Malcolm');
 19
 20    if (a = b) then
 21      dbms_output.put_line('Case 2: a and b are the same');
 22    else
 23      dbms_output.put_line('Case 2: a and b are different');
 24    end if;
 25
 26  end;
 27  /
Case 1: a and b are the same
Case 2: a and b are different

PL/SQL procedure successfully completed.

Comparing PL/SQL Collections of Objects

With that MAP function defined for the object type, comparing nested tables of objects becomes possible too.

SQL> -- Comparing nested tables of objects
SQL> declare
  2    type tbl_user is table of user_obj;
  3
  4    a   tbl_user := tbl_user(
  5                   user_obj(1, 'Mark')
  6                 , user_obj(2, 'Arnold')
  7                            );
  8    b   tbl_user;
  9
 10  begin
 11
 12    -- Case 1: b is identical to a
 13    b := tbl_user(
 14                   user_obj(1, 'Mark')
 15                 , user_obj(2, 'Arnold')
 16                 );
 17
 18    if (a = b) then
 19      dbms_output.put_line('Case 1: a and b are the same');
 20    else
 21      dbms_output.put_line('Case 1: a and b are different');
 22    end if;
 23
 24    -- Case 2: b is not identical to a
 25    b := tbl_user(
 26                   user_obj(1, 'Mark')
 27                 , user_obj(2, 'Adam')
 28                 );
 29
 30    if (a = b) then
 31      dbms_output.put_line('Case 2: a and b are the same');
 32    else
 33      dbms_output.put_line('Case 2: a and b are different');
 34    end if;
 35
 36  end;
 37  /
Case 1: a and b are the same
Case 2: a and b are different

PL/SQL procedure successfully completed.

Summary

Though only the first of these seems easily doable, Oracle can actually perform a simple equality check, with the ‘=’ operator, in ALL of these cases:

Comparing data items of scalar data type (NUMBER, VARCHAR2, DATE, etc.)
Comparing nested tables of scalar data type
Comparing objects of user-defined type, when a MAP function is defined for the type
Comparing nested tables of objects of user-defined type, when a MAP function is defined for the type

The examples in this post demonstrate how the equality check can be done when dealing with user-defined types.

References:

{ 3 comments… read them below or add one }

1 Piotr February 9, 2015 at 9:11 pm: Hi,
This is a great post and I am so fortunate to find it.
I have one question:
Is there any particular reason you use RAW return type as return of MAP function?
2 oratabler February 9, 2015 at 9:38 pm: Hi Piotr, RAW would support the equality check with a mix of datatypes in the UDT. If you already know that the UDT has only, say, VARCHAR2 attributes, you could use VARCHAR2 instead.
3 Piotr August 14, 2015 at 9:43 pm: Hello again after some time!

I started to use map member functions and thought it’s something cool, but then recently I found they seem to be terribly slow.
Consider an example:

CREATE OR REPLACE TYPE t_reference FORCE AS OBJECT
(
CODE NUMBER(4),
VALUE VARCHAR2(60),
MAP MEMBER FUNCTION equals (SELF IN OUT NOCOPY T_REFERENCE) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY t_reference AS
map MEMBER FUNCTION equals (SELF IN OUT NOCOPY T_REFERENCE) RETURN NUMBER as
BEGIN
RETURN self.code;
END;
END;
/

And two code snippets:

declare
tab t_reference_tbl := t_reference_tbl(t_Reference(1,2),t_Reference(1,’a’),t_Reference(2,’b’),t_Reference(3,’a’));
vl t_reference := t_reference(4,2);
var NUMBER := 0;
BEGIN
FOR r IN 1..2000 LOOP
tab.Extend(1);
tab(tab.Last) := t_reference(r+1,r); –some mangling to fool optimizer
vl.code := r;
if vl member OF tab THEN
var := var+1;
end if;
END
end;
>> PL/SQL block, executed in 2.223 sec.
/
declare
tab t_reference_tbl := t_reference_tbl(t_Reference(1,2),t_Reference(1,’a’),t_Reference(2,’b’),t_Reference(3,’a’));
vl t_reference := t_reference(4,2);
var NUMBER := 0;
BEGIN
FOR r IN 1..2000 LOOP
tab.Extend(1);
tab(tab.Last) := t_reference(r+1,r); –some mangling to fool optimizer
vl.code := r;
FOR rr IN Nvl(tab.First,0)..Nvl(tab.last,-1) loop
if vl.code = tab(rr).code THEN
var := var+1;
end if;
END LOOP;
END LOOP;
end;
/
>> PL/SQL block, executed in 137 ms

Surprisingly I made a similar test with TABLE OF VARCHAR2, and the result was in favor of MEMBER OF method…

Am I doing something wrong or this is just how the things are?