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:

  1. Comparing data items of scalar data type (NUMBER, VARCHAR2, DATE, etc.)
  2. Comparing nested tables of scalar data type
  3. Comparing objects of user-defined type, when a MAP function is defined for the type
  4. 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: