Equality Test of two OBJECTS And COLLECTION Types

How to Compare PL/SQL Collections of Scalar Data Types, Object Types and COLLECTION of Objects?

We can easily compare the Variables of Scalar Data Types (Single Data Values). You can even do the same trick with Collections. Let’s see this with the help of below Example.

Comparing ORACLE PL/SQL Collections of Scalar Data types

In the below Example, we will compare the NESTED Tables of Scalar Data Types.

DECLARE
Type NT IS TABLE OF NUMBER; /*NESTED Table Type*/
NT1 NT;
NT2 NT;
NT3 NT;
BEGIN
NT1:= NT(1,2,3);
NT2 := NT(1,2,3);
IF NT1 = NT2 THEN
DBMS_OUTPUT.PUT_LINE('NT2 is the same Nested Table as NT1');
ELSE
DBMS_OUTPUT.PUT_LINE('NT2 is the different Nested Table as NT1');
END IF;
NT3 := NT(1,2,3,4);
IF NT1 = NT3 THEN
DBMS_OUTPUT.PUT_LINE('NT3 is the same Nested Table as NT1');
ELSE
DBMS_OUTPUT.PUT_LINE('NT3 is a different Nested Table from NT1');
END IF;
END;
/
NT2 is the same Nested Table as NT1
NT3 is a different Nested Table from NT1

PL/SQL procedure successfully completed.

However, this will not work when we are actually dealing with Collection of Objects.

Comparing ORACLE PL/SQL Objects (USER Defined Types)

Let’s try to Compare them with equal ‘=’ Operator and see if it works.

Create a Type EMPLOYEE_OBJECT First

SQL> CREATE OR REPLACE TYPE EMPLOYEE_OBJECT IS OBJECT
(EMP_ID NUMBER, EMP_NAME VARCHAR2(30));

Type created.

Now, let’s create two Objects of Type EMPLOYEE_OBJECT and Compare their Values

DECLARE
Obj1   EMPLOYEE_OBJECT := EMPLOYEE_OBJECT (101, 'Ravi');
Obj2   EMPLOYEE_OBJECT;
BEGIN

/*Case 1: Obj2 is identical to Obj1*/
Obj2 := EMPLOYEE_OBJECT(101, 'Ravi');
IF(Obj1 = Obj2) Then
DBMS_OUTPUT.PUT_LINE('Case 1: Obj1 and Obj2 Contains Identical Values');
Else
DBMS_OUTPUT.PUT_LINE('Case 1: Obj1 and Obj2 are Different');
END IF;
 
/*Case 2: Obj2 is not identical to Obj1*/
Obj2 := EMPLOYEE_OBJECT(102, 'Mohan');
IF(Obj1 = Obj2) Then
DBMS_OUTPUT.PUT_LINE('Case 1: Obj1 and Obj2 Contains Identical Values');
Else
DBMS_OUTPUT.PUT_LINE('Case 1: Obj1 and Obj2 are Different');
END IF;
END;
/

ERROR:
IF(Obj1 = Obj2) Then
        *
ERROR at line 8:
ORA-06550: line 8, column 9:
PLS-00526: A MAP or ORDER function is required for comparing objects in PL/SQL.
ORA-06550: line 16, column 9:
PLS-00526: A MAP or ORDER function is required for comparing objects in PL/SQL.

So, that didn’t work but we got the Solution in Error Message itself which tells we need to create a MAP or ORDER Function to fix this problem. Let’s do that:

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

First let’s understand what this MAP function does. As we have seen in our previous Example where we compared Scalar Data Types but unable to compare UDT’s. So, MAP method Translates or Maps each Object into Scalar Data to enable the comparison between Objects.

The MAP member functions are used for performing comparisons between a single attribute of an object instance to a single attribute of another object instance. The MAP functions do not accept any formal parameters and must return a scalar data type, either CHAR, VARCHAR2, NUMBER or DATE, which can be easily compared by the system. The MAP member functions are used for validating object instances with a single attribute.

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

Create a Type EMPLOYEE_OBJECT with MAP Method

CREATE OR REPLACE TYPE EMPLOYEE_OBJECT IS OBJECT
(
EMP_ID NUMBER, 
EMP_NAME VARCHAR2(30),
MAP MEMBER FUNCTION Equality RETURN RAW
);

Type created.

NOTE: RAW would support the equality check with a mix of datatypes in the UDT. If you already know that the UDT has only one Data Type, say, VARCHAR2/NUMBER attributes, you could use VARCHAR2/NUMBER instead.

Create Type BODY Now:

CREATE OR REPLACE TYPE BODY EMPLOYEE_OBJECT AS
MAP MEMBER FUNCTION Equality RETURN RAW AS
BEGIN
/*Return Concatenated RAW String Of All Attributes Of The Object*/
RETURN
/*NVL() To Avoid NULLS Being Treated As Equal. NVL default values: Choose Carefully!*/
UTL_RAW.CAST_TO_RAW(NVL(SELF.EMP_ID, -1) || NVL(SELF.EMP_NAME, '***'));
END Equality;
END;
/
Type body created.

Now, we have indicated ORACLE how Members under Object EMPLOYEE_OBJECT Treated when used for Comparison. Let’s try to execute the PL/SQL Code again for Equality Objects:

SQL> DECLARE
  2  Obj1   EMPLOYEE_OBJECT := EMPLOYEE_OBJECT (101, 'Ravi');
  3  Obj2   EMPLOYEE_OBJECT;
  4  BEGIN
  5
  6  /*Case 1: Obj2 is identical to Obj1*/
  7  Obj2 := EMPLOYEE_OBJECT(101, 'Ravi');
  8  IF(Obj1 = Obj2) Then
  9  DBMS_OUTPUT.PUT_LINE('Case 1: Obj1 and Obj2 Contains Identical Values');
 10  Else
 11  DBMS_OUTPUT.PUT_LINE('Case 1: Obj1 and Obj2 are Different');
 12  END IF;
 13
 14  /*Case 2: Obj2 is not identical to Obj1*/
 15  Obj2 := EMPLOYEE_OBJECT(102, 'Mohan');
 16  IF(Obj1 = Obj2) Then
 17  DBMS_OUTPUT.PUT_LINE('Case 1: Obj1 and Obj2 Contains Identical Values');
 18  Else
 19  DBMS_OUTPUT.PUT_LINE('Case 1: Obj1 and Obj2 are Different');
 20  END IF;
 21  END;
 22  /
Case 1: Obj1 and Obj2 Contains Identical Values
Case 1: Obj1 and Obj2 are Different
PL/SQL procedure successfully completed.

Comparing PL/SQL Collections of Objects

COLLECTION comparison also becomes possible with the MAP Function. We will use the same MAP Function in this Example to compare two NESTED Tables.

/*Comparing Nested Tables Of Objects*/
DECLARE
TYPE NT_EMPLOYEE IS Table Of EMPLOYEE_OBJECT;
Obj1 NT_EMPLOYEE := NT_EMPLOYEE(EMPLOYEE_OBJECT(101, 'Surya'), EMPLOYEE_OBJECT(102, 'Pritesh'));
Obj2 NT_EMPLOYEE;
BEGIN

/*Case 1: Obj2 is identical to Obj1*/
Obj2 := NT_EMPLOYEE(EMPLOYEE_OBJECT(101, 'Surya'), EMPLOYEE_OBJECT(102, 'Pritesh'));
IF(Obj1 = Obj2) Then
DBMS_OUTPUT.PUT_LINE('Case 1: Obj1 and Obj2 Contains Identical Values');
Else
DBMS_OUTPUT.PUT_LINE('Case 1: Obj1 and Obj2 are Different');
END IF;

/*Case 2: Obj2 is not identical to Obj1*/
Obj2 := NT_EMPLOYEE(EMPLOYEE_OBJECT(101, 'Surya'), EMPLOYEE_OBJECT(102, 'Rajan'));
IF(Obj1 = Obj2) Then
DBMS_OUTPUT.PUT_LINE('Case 1: Obj1 and Obj2 Contains Identical Values');
Else
DBMS_OUTPUT.PUT_LINE('Case 1: Obj1 and Obj2 are Different');
END IF;
END;
/
Case 1: Obj1 and Obj2 Contains Identical Values
Case 1: Obj1 and Obj2 are Different
PL/SQL procedure successfully completed.

Let’s see one more Example of NESTED Table Object Comparison
In this Example, we will go step by step:

As we already know that we won’t be able to convert COLLECTION Objects directly with Equal operator, to do so we need to create a MAP method so ORACLE treats them as Scalar Data Type and do the necessary comparison.

Step1: OBJECT Creation with MAP Method

CREATE OR REPLACE TYPE EMP_OBJECT AS OBJECT 

(

EMPNO NUMBER,

ENAME VARCHAR2(10),

SAL NUMBER,

DEPTNO NUMBER,

LOCATION VARCHAR2(20),

ROLE VARCHAR2(10),

MAP MEMBER FUNCTION Equality RETURN RAW

);

Type created 

Step2: CREATE Type BODY

CREATE OR REPLACE TYPE BODY EMP_OBJECT AS
MAP MEMBER FUNCTION Equality RETURN RAW IS
BEGIN
RETURN 
UTL_RAW.CAST_TO_RAW(NVL(SELF.EMPNO,-1)|| NVL(SELF.ENAME,'***') || NVL(SELF.SAL,-99)|| NVL(SELF.DEPTNO,-99)|| NVL(SELF.LOCATION,'***') || NVL(SELF.ROLE,'***'));
END Equality;
END;

Step3: Compare NESTED Table Objects Now

DECLARE
Obj1 EMP_OBJECT;
Obj2 EMP_OBJECT;
Obj3 EMP_OBJECT;
BEGIN
Obj1:= EMP_OBJECT(101,'Ravi', 97000, 50, 'Bangalore', 'DB Expert');
Obj2:= EMP_OBJECT(102,'Rajan',99900, 30, 'York', 'Finance');
Obj3:= EMP_OBJECT(101,'Ravi', 97000, 50, 'Bangalore', 'DB Expert');
IF(Obj1 = Obj2) Then
DBMS_OUTPUT.PUT_LINE('Obj1 and Obj2 Contains Identical Values');
Else
DBMS_OUTPUT.PUT_LINE('Obj1 and Obj2 are Different');
END IF;

IF(Obj1 = Obj3) Then
DBMS_OUTPUT.PUT_LINE('Obj1 and Obj3 Contains Identical Values');
Else
DBMS_OUTPUT.PUT_LINE('Obj1 and Obj3 are Different');
END IF;
END;
/
Obj1 and Obj2 are Different
Obj1 and Obj3 Contains Identical Values

PL/SQL procedure successfully completed.

So, the Question is Why Oracle doesn't do this by default. Well, the TYPE implementation only allows one comparison method (if we have a MAP function we cannot have an ORDER function) so we need to have the capability to choose our own definition of Equality so that we can create our own Function. For instance, a type called RECTANGLE might have a MAP function called AREA() which returns SELF.WIDTH *

SELF.LENGTH.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.