Comparing record variables

Oracle does not provide possibility to compare variables of record type. Even if they are the same type. Unfortunately, you have to write special block of code to implement such tasks. This is not so hard, when you want to process with simple record variables. You should to write the block for each field as follow:

((record_variable1.field1 = records_variable2.field1) OR (record_variable1.field1 IS NULL AND record_variable2.field1 IS NULL))

Because the field type is unknown, I can not use NVL function. In the case when the variables have a large number of fields, you have to write a large block of code. And, therefore, possible number of errors is increasing. It would be nice to get a procedure which can write comparing code automatically. At least for the variables whose data type is based on the table. The following procedure allows to get code to compare for equality two record variables whose data type is based on the table. The specification for the procedure follows.

PROCEDURE get_rec_isequal_code (parent_table_name IN VARCHAR2
                                ,rec_name_1 IN VARCHAR2
                                ,rec_name_2 IN VARCHAR2);

The procedure shows desired code via DBMS_OUTPUT package. So, you should turn on SERVEROUTPUT parameter in your SQL*Plus (or another environment) session. The following is a practical example. The procedure builds logical expression for the record variables based on the EMP table (scott schema).

SET SERVEROUTPUT ON
DECLARE
      employee1 emp%ROWTYPE;
      employee2 emp%ROWTYPE;
BEGIN
      get_rec_isequal_code('emp','employee1','employee2');
END;

Below is the output.

anonymous block completed
RESULT:
*******
((employee1.EMPNO=employee2.EMPNO) OR (employee1.EMPNO IS NULL AND employee2.EMPNO IS NULL)) AND ((employee1.ENAME=employee2.ENAME) OR (employee1.ENAME IS NULL AND employee2.ENAME IS NULL)) AND ((employee1.JOB=employee2.JOB) OR (employee1.JOB IS NULL AND employee2.JOB IS NULL)) AND ((employee1.MGR=employee2.MGR) OR (employee1.MGR IS NULL AND employee2.MGR IS NULL)) AND ((employee1.HIREDATE=employee2.HIREDATE) OR (employee1.HIREDATE IS NULL AND employee2.HIREDATE IS NULL)) AND ((employee1.SAL=employee2.SAL) OR (employee1.SAL IS NULL AND employee2.SAL IS NULL)) AND ((employee1.COMM=employee2.COMM) OR (employee1.COMM IS NULL AND employee2.COMM IS NULL)) AND ((employee1.DEPTNO=employee2.DEPTNO) OR (employee1.DEPTNO IS NULL AND employee2.DEPTNO IS NULL))

The source code of the get_rec_isequal_code procedure is available here.