Compare values in each column of two Oracle Types

Question

I've been playing around with the pluto-test-framework today, and I'd like to get some existing functions into a test harness with it.

I have lots of functions with this type of specification.

   FUNCTION DO_SOME_STUFF   (pOldSchedule       IN      SCHEDULE_OBJ,
                             pNewSchedule          OUT  SCHEDULE_OBJ,
                             pLoggerContext     IN OUT  LOGGER_CONTEXT_OBJ)
   RETURN NUMBER;

It takes pOldSchedule, does some stuff to it, and then returns pNewSchedule. The logger_context just does logging.

As part of a test, I'd like to be able to compare the values in each of the columns of the type, without having to write individual IF statements.

It'll need to return boolean to signify whether or not pOldSchedule and pNewSchedule match.

Any ideas?

Answer 1

Straightforward equality tests work with nested tables:

SQL> declare
  2      type nt is table of number;
  3      nt1 nt;
  4      nt2 nt;
  5      nt3 nt;
  6  begin
  7      nt1 := nt(1,2,3);
  8      nt2 := nt(1,2,3);
  9      if nt1 = nt2 then
 10          dbms_output.put_line('NT2 is the same nested table as NT1');
 11      else
 12          dbms_output.put_line('NT2 is a different nested table from NT1');
 13      end if;
 14      nt2 := nt(1,2,3,4);
 15      if nt1 = nt3 then
 16          dbms_output.put_line('NT3 is the same nested table as NT1');
 17      else
 18          dbms_output.put_line('E3 is a different nested table from NT1');
 19      end if;
 20  end;
 21  /
NT2 is the same nested table as NT1
E3 is a different nested table from NT1

PL/SQL procedure successfully completed.

SQL>

However the same is not true of full-on objects:

SQL> create or replace type new_emp as object (
  2      ename varchar2(10)
  3      , sal number
  4      , deptno number
  5      , job varchar2(10))
  6  /

Type created.

SQL> declare
  2      e1 new_emp;
  3      e2 new_emp;
  4  begin
  5      e1 := new_emp('KESTELYN', 3700, 30, 'MARKETING');
  6      e2 := new_emp('KESTELYN', 3700, 30, 'MARKETING');
  7      if e1 = e2 then
  8          dbms_output.put_line('E2 is the same as E1');
  9      else
 10          dbms_output.put_line('E2 is different from E1');
 11      end if;
 12  end;
 13  /
    if e1 = e2 then
          *
ERROR at line 7:
ORA-06550: line 7, column 11:
PLS-00526: A MAP or ORDER function is required for comparing objects in PL/SQL.


SQL>

We need to explicitly define a member function for executing comparisons. So here is the same object with a MAP function. The example implementation generates a hashed string, which is useful if we want to store the value for later comparison, but it could just return the concatenated string (especially as EXECUTE on DBMS_CRYPTO is not granted by default). The NVL() functions are necessary to avoid (null, value) and (value, null) being evaluated as equal. There is the always a risk when using magic values, so we need to choose them carefully.

SQL> create or replace type new_emp as object (
  2      ename varchar2(10)
  3      , sal number
  4      , deptno number
  5      , job varchar2(10)
  6      , map member function equals return raw)
  7  /

Type created.

SQL> create or replace type body new_emp as
  2      map member function equals return raw
  3      is
  4      begin
  5          return dbms_crypto.hash(
  6                     utl_raw.cast_to_raw(nvl(self.ename,'***')||
  7                                          nvl(self.sal,-99)||
  8                                          nvl(self.deptno,-99)||
  9                                          nvl(self.job,'***')
 10                                        )
 11                                   , 1);
 12      end equals;
 13  end;
 14  /

Type body created.

SQL>

Now we have a basis for comparing instances of our objects:

SQL> declare
  2      e1 new_emp;
  3      e2 new_emp;
  4  begin
  5      e1 := new_emp('KESTELYN', 3700, 30, 'MARKETING');
  6      e2 := new_emp('KESTELYN', 3700, 30, 'MARKETING');
  7      if e1 = e2 then
  8          dbms_output.put_line('E2 is the same as E1');
  9      else
 10          dbms_output.put_line('E2 is different from E1');
 11      end if;
 12  end;
 13  /
E2 is the same as E1

PL/SQL procedure successfully completed.

SQL>    

You might be wondering why Oracle doesn't do this by default. Well, the TYPE implemntation 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. For instance, a type called rectangle might have a MAP function called area() which returns self.width * self.length.