How to compare results of two SQL queries

Sometimes, for example during unit tests of some PL/SQL procedures, it is required to compare the results of any two SQL queries. In the results of the queries both the column composition and all the returned values should be compared. In Oracle database this can be done using DBMS_SQL package.

The simple package below compares row numbers, column numbers, column names and types, and all the values in the corresponding columns of the corresponding rows produced by two specified SQL queries. The procedure compare_query_results throws an informative uncaught exception if any mismatch is detected. The procedure aborts when the first mismatch is detected with the rest values remain not considered. Specifically, the following custom exceptions are thrown:

• The specified two queries returned different row numbers
• The results have different column numbers
• The corresponding column names or types differ
• The values in the corresponding columns of the corresponding rows differ

CREATE OR REPLACE PACKAGE "MY_COMPARE"
IS
  PROCEDURE compare_query_results(
      query1              VARCHAR2,
      query2              VARCHAR2,
      skip_columns_regexp VARCHAR2:=null);
END;
/
CREATE OR REPLACE PACKAGE BODY "MY_COMPARE"
IS
  c1 INTEGER;
  c2 INTEGER;
  rec_tab1 DBMS_SQL.DESC_TAB3;
  rec_tab2 DBMS_SQL.DESC_TAB3;
  namevar VARCHAR2(4000);
  numvar  NUMBER;
  datevar DATE;
  PROCEDURE close_cursors(
      c1 IN OUT INTEGER,
      c2 IN OUT INTEGER )
  IS
  BEGIN
    DBMS_SQL.CLOSE_CURSOR(c1);
    DBMS_SQL.CLOSE_CURSOR(c2);
  END;
  PROCEDURE compare_all_values(
      c1 INTEGER,
      c2 INTEGER,
      rec_tab DBMS_SQL.DESC_TAB3,
      skip_columns_regexp VARCHAR2 )
  IS
    rows_fetched1 INTEGER;
    rows_fetched2 INTEGER;
    row_conter pls_integer:=0;
    namevar1 VARCHAR2(4000);
    numvar1  NUMBER;
    datevar1 DATE;
    namevar2 VARCHAR2(4000);
    numvar2  NUMBER;
    datevar2 DATE;
  BEGIN
    LOOP
      rows_fetched1   := DBMS_SQL.FETCH_ROWS(c1);
      rows_fetched2   := DBMS_SQL.FETCH_ROWS(c2);
      IF rows_fetched1!= rows_fetched2 THEN
        RAISE_APPLICATION_ERROR(-20000, 'Queries returned different row numbers');
      END IF;
      EXIT
    WHEN rows_fetched1=0;
      row_conter     :=row_conter+1;
      FOR i IN rec_tab.first .. rec_tab.last
      LOOP
        IF (skip_columns_regexp IS NOT NULL AND REGEXP_INSTR( rec_tab(i).col_name,skip_columns_regexp)>0) THEN
          CONTINUE;
        END IF;
        IF (rec_tab(i).col_type = 1) THEN
          DBMS_SQL.COLUMN_VALUE(c1, i, namevar1);
          DBMS_SQL.COLUMN_VALUE(c2, i, namevar2);
          IF namevar1!=namevar2 THEN
            RAISE_APPLICATION_ERROR(-20000, 'Column '||rec_tab(i).col_name||' values differ: '||namevar1||'; '||namevar2);
          END IF;
        ELSIF (rec_tab(i).col_type = 2) THEN
          DBMS_SQL.COLUMN_VALUE(c1, i, numvar1);
          DBMS_SQL.COLUMN_VALUE(c2, i, numvar2);
          IF numvar1!=numvar2 THEN
            RAISE_APPLICATION_ERROR(-20000, 'Column '||rec_tab(i).col_name||' values differ: '||numvar1||'; '||numvar2);
          END IF;
        ELSIF (rec_tab(i).col_type = 12) THEN
          DBMS_SQL.COLUMN_VALUE(c1, i, datevar1);
          DBMS_SQL.COLUMN_VALUE(c2, i, datevar2);
          IF datevar1!=datevar2 THEN
            RAISE_APPLICATION_ERROR(-20000, 'Column '||rec_tab(i).col_name||' values differ: '||datevar1||'; '||datevar2);
          END IF;
        ELSE
          RAISE_APPLICATION_ERROR(-20000, 'Unknown column type: '||rec_tab(i).col_type);
        END IF;
      END LOOP;
    END LOOP;
  END;
   
  PROCEDURE compare_column_definitions(
      rec_tab1 DBMS_SQL.DESC_TAB3,
      rec_tab2 DBMS_SQL.DESC_TAB3)
  IS
  BEGIN
    IF rec_tab1.count!=rec_tab2.count THEN
      RAISE_APPLICATION_ERROR(-20001, 'Tables have different numbers of columns');
    END IF;
    FOR i IN rec_tab1.first .. rec_tab1.last
    LOOP
      IF rec_tab1(i).col_name != rec_tab2(i).col_name THEN
        RAISE_APPLICATION_ERROR(-20000, 'Column names differ at index: '||i||'; '|| rec_tab1(i).col_name||'; '||rec_tab2(i).col_name );
      END IF;
      IF rec_tab1(i).col_type != rec_tab2(i).col_type THEN
        RAISE_APPLICATION_ERROR(-20000, 'Column types differ at index: '||i||'; '|| rec_tab1(i).col_type||'; '||rec_tab2(i).col_type );
      END IF;
    END LOOP;
  END;
  FUNCTION open_cursor_and_define_columns(
      query VARCHAR2,
      colcnt OUT INTEGER,
      rec_tab OUT DBMS_SQL.DESC_TAB3)
    RETURN INTEGER
  IS
    c INTEGER;
    d INTEGER;
  BEGIN
    c := DBMS_SQL.OPEN_CURSOR;  
    DBMS_SQL.PARSE(c,query, DBMS_SQL.NATIVE);
    d := DBMS_SQL.EXECUTE(c);  
    DBMS_SQL.DESCRIBE_COLUMNS3(c, colcnt, rec_tab);
     FOR i IN 1 .. colcnt
    LOOP
       IF rec_tab(i).col_type = 2 THEN
        DBMS_SQL.DEFINE_COLUMN(c, i, numvar);
      ELSIF rec_tab(i).col_type = 12 THEN
        DBMS_SQL.DEFINE_COLUMN(c, i, datevar);
      ELSE --1 varchar2
        DBMS_SQL.DEFINE_COLUMN(c, i, namevar, rec_tab(i).col_max_len);
      END IF;
    END LOOP;
    RETURN c;
  END;
  PROCEDURE compare_query_results(
      query1              VARCHAR2,
      query2              VARCHAR2,
      skip_columns_regexp VARCHAR2)
  IS
    colcnt1 INTEGER;
    colcnt2 INTEGER;
  BEGIN
    c1:= open_cursor_and_define_columns(query1, colcnt1, rec_tab1);
    c2:= open_cursor_and_define_columns(query2, colcnt2, rec_tab2);
    BEGIN
      IF colcnt1!=colcnt2 THEN
        RAISE_APPLICATION_ERROR(-20000, 'Tables have different column numbers');
      END IF;
      compare_column_definitions( rec_tab1 ,rec_tab2 );
      compare_all_values(c1 , c2 ,rec_tab1, skip_columns_regexp ) ;
      close_cursors(c1 ,c2 );
      DBMS_OUTPUT.PUT_LINE('THE RESULTS ARE EQUAL');
    EXCEPTION
    WHEN OTHERS THEN
      close_cursors(c1 ,c2 );
      DBMS_OUTPUT.PUT_LINE('VALUES DIFFER, SEE ABOVE');
      raise;
    END;
  END;
END;
/

Evidently, the queries supplied to the procedure must be ordered. Let's try some examples in HR schema. First, let's create a copy of EMPLOYEES tables. This table will be modified and compared to the original.

set serveroutput on;

create table employees_copy as select * from employees;

declare
query1 varchar2(4000):='select * from employees  order by employee_id';
query2 varchar2(4000):=replace(query1,'employees','employees_copy');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2);
end;
/

THE RESULTS ARE EQUAL

Often the values in the sorted results of two queries are identical, except for primary keys. For the procedure to ignore the values in the primary key column, the user can specify as the third argument a regular expression matching the primary key column name. To demonstrate, I modify employee_id column in the copy table.

update employees_copy set employee_id=employee_id+1111;

declare
query1 varchar2(4000):='select * from employees  order by employee_id';
query2 varchar2(4000):=replace(query1,'employees','employees_copy');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2);
end;
/

Error starting at line : 5 in command -
declare
query1 varchar2(4000):='select * from employees  order by employee_id';
query2 varchar2(4000):=replace(query1,'employees','employees_copy');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2);
end;
Error report -
ORA-20000: Column EMPLOYEE_ID values differ: 100; 1211
ORA-06512: at "HR.MY_COMPARE", line 138
ORA-06512: at line 5
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.
VALUES DIFFER, SEE ABOVE

As expected, the exception says: Column EMPLOYEE_ID values differ: 100; 1211. Now let's try with the name of the column to skip.

declare
query1 varchar2(4000):='select * from employees  order by employee_id';
query2 varchar2(4000):=replace(query1,'employees','employees_copy');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2, 'EMPLOYEE_ID');
end;
/

THE RESULTS ARE EQUAL

Let's try selecting different column sets:

declare
query1 varchar2(4000):='select FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,MANAGER_ID from employees order by employee_id';
query2 varchar2(4000):=replace(query1,'PHONE_NUMBER','DEPARTMENT_ID');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2 );
end;
/

Error starting at line : 16 in command -
declare
query1 varchar2(4000):='select FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,MANAGER_ID from employees order by employee_id';
query2 varchar2(4000):=replace(query1,'PHONE_NUMBER','DEPARTMENT_ID');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2 );
end;
Error report -
ORA-20000: Column names differ at index: 4; PHONE_NUMBER; DEPARTMENT_ID
ORA-06512: at "HR.MY_COMPARE", line 138
ORA-06512: at line 5
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.
VALUES DIFFER, SEE ABOVE

The thrown exception indicates that: Column names differ at index: 4; PHONE_NUMBER; DEPARTMENT_ID

Let's make subtle change in a row of the copy table.

update employees_copy set FIRST_NAME='TEST!' where employee_id=1225;
 
declare
query1 varchar2(4000):='select * from employees  order by employee_id';
query2 varchar2(4000):=replace(query1,'employees','employees_copy');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2, 'EMPLOYEE_ID');
end;
/

Error starting at line : 6 in command -
declare
query1 varchar2(4000):='select * from employees  order by employee_id';
query2 varchar2(4000):=replace(query1,'employees','employees_copy');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2, 'EMPLOYEE_ID');
end;
Error report -
ORA-20000: Column FIRST_NAME values differ: Den; TEST!
ORA-06512: at "HR.MY_COMPARE", line 138
ORA-06512: at line 5
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.
VALUES DIFFER, SEE ABOVE

So this simple packaged procedure can help you to make your tests.