Compare the values ​​in each column of the two Oracle types

Question

Today I played with the pluto-test-framework , and I would like to get some existing functions in a test harness with it.

I have many 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 things, and then returns pNewSchedule. Logger_context just logs.

As part of the test, I would like to be able to compare the values ​​in each type column without having to write separate IF statements.

It will need to return a boolean value to indicate whether pOldSchedule and pNewSchedule match.

Any ideas?

Answer 1

:

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>

:

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>

- . , MAP. , , , (, EXECUTE DBMS_CRYPTO ). NVL() , (null, value) (value, null) . , .

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>

:

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>    

, , Oracle . , TYPE ( MAP, ORDER), . , rectangle MAP, area(), self.width * self.length.