03 November 2011

Automated Table Data Comparison via DBMS_COMPARISON package


Four years ago, i was in a project that was mainly focused on coparing table data between two different oracle databases. Actually, the table data was very important for customer. The customer has over 100 tables for configuration and these tables have at least 100.000 rows, in total. The Data Model has very complex relationships between entities. There were lots of bla bla Id and bla bla description columns J.  Because of the errors that data operators have done was unacceptable,the customer has set up two different databases with exact the same configurational data. Two data operators have input the same configuration data via complex GUI screens, independently. At the end, a pl/sql procedure has been executed and generates an alarm if there is an inequality between the configuration tables. By the help of this routine, the customer intends to avoid undesirable data configuration errors.
For this part of project, we have created a general data comparison utility that takes some complex queries as input parameters and compares these resultset. It was something difficult to do all these jobs on-the-fly.  We have spent lots of time in order to fulfill customer requirements by developing and testing some pl/sql procedures. The business logic behind these configurational data was also very hard to understand. ..Finally, we did it J
….
Why i told you this story? A few days ago, i had time to analyze DBMS_COMPARISON package. I admit that, i like it so much. When i play with procedures of this package, i remembered my old days J If this package has been released four years ago, may be, we would fulfill customer needs more easily.
Follow demostration below:
---base table
CREATE TABLE YASEMIN.BASE_TABLE(
  ID    NUMBER PRIMARY KEY,
  DES   VARCHAR2(10)
);
INSERT INTO  YASEMIN.BASE_TABLE(ID,DES) VALUES(1,'TEST1');
INSERT INTO  YASEMIN.BASE_TABLE(ID,DES) VALUES(2,'TEST2');
INSERT INTO  YASEMIN.BASE_TABLE(ID,DES) VALUES(3,'TEST3');

---compare table
CREATE TABLE PAPATYA.COMP_TABLE(
  ID     NUMBER PRIMARY KEY,
  DES    VARCHAR2(10)
);
INSERT INTO  PAPATYA.COMP_TABLE(ID,DES) VALUES(1,'TEST1');
INSERT INTO  PAPATYA.COMP_TABLE(ID,DES) VALUES(4,'TEST4');
COMMIT;
SQL> SELECT * FROM YASEMIN.BASE_TABLE;

        ID DES
---------- ----------
         1 TEST1
         2 TEST2
         3 TEST3
SQL> SELECT * FROM PAPATYA.COMP_TABLE;

        ID DES
---------- ----------
         1 TEST1
         4 TEST4

SQL>
--create a comparison for two tables
BEGIN
  DBMS_COMPARISON.create_comparison(comparison_name    => 'COMPARISON_FOR_BASE_TABLE',
                                    schema_name        => 'YASEMIN',
                                    object_name        => 'BASE_TABLE',
                                    dblink_name        => NULL,
                                    remote_schema_name => 'PAPATYA',
                                    remote_object_name => 'COMP_TABLE');
END;
/

--- make comparison
DECLARE
  vt_ComparisonType dbms_comparison.comparison_type;
  vb_Result         BOOLEAN;
BEGIN
  vb_Result := dbms_comparison.compare(comparison_name => 'COMPARISON_FOR_BASE_TABLE',
                                       scan_info       => vt_ComparisonType,
                                       perform_row_dif => TRUE);

  IF vb_Result THEN
    dbms_output.put_line('no difs are found');
  ELSE
    dbms_output.put_line('difs are found');
  END IF;

  dbms_output.put_line('scan_id = ' || vt_ComparisonType.scan_id);
  dbms_output.put_line('loc_rows_merged  = ' || vt_ComparisonType.loc_rows_merged);
  dbms_output.put_line('rmt_rows_merged  = ' || vt_ComparisonType.rmt_rows_merged);
  dbms_output.put_line('loc_rows_deleted = ' || vt_ComparisonType.loc_rows_deleted);
  dbms_output.put_line('rmt_rows_deleted = ' || vt_ComparisonType.rmt_rows_deleted);
END;
/
difs are found
scan_id = 28
loc_rows_merged  = 0
rmt_rows_merged  = 0
loc_rows_deleted = 0
rmt_rows_deleted = 0

--converge, apply diff results
DECLARE
  vt_ComparisonType dbms_comparison.comparison_type;
  vb_Result         BOOLEAN;
BEGIN
  dbms_comparison.converge(comparison_name  => 'COMPARISON_FOR_BASE_TABLE',
                           scan_id          => 28,
                           scan_info        => vt_ComparisonType,
                           converge_options => dbms_comparison.CMP_CONVERGE_LOCAL_WINS,
                           perform_commit   => FALSE);

  dbms_output.put_line('scan_id = ' || vt_ComparisonType.scan_id);
  dbms_output.put_line('loc_rows_merged  = ' || vt_ComparisonType.loc_rows_merged);
  dbms_output.put_line('rmt_rows_merged  = ' || vt_ComparisonType.rmt_rows_merged);
  dbms_output.put_line('loc_rows_deleted = ' || vt_ComparisonType.loc_rows_deleted);
  dbms_output.put_line('rmt_rows_deleted = ' || vt_ComparisonType.rmt_rows_deleted);

END;
/
scan_id = 28
loc_rows_merged  = 0
rmt_rows_merged  = 2
loc_rows_deleted = 0
rmt_rows_deleted = 1

--data has been fixed, but not committed
SQL> SELECT * FROM YASEMIN.BASE_TABLE;

        ID DES
---------- ----------
         1 TEST1
         2 TEST2
         3 TEST3
SQL> SELECT * FROM PAPATYA.COMP_TABLE;

        ID DES
---------- ----------
         1 TEST1
         2 TEST2
         3 TEST3

SQL>
SQL>
SQL>
SQL>
SQL> rollback;

Rollback complete

SQL>
SQL> SELECT * FROM YASEMIN.BASE_TABLE;

        ID DES
---------- ----------
         1 TEST1
         2 TEST2
         3 TEST3
SQL> SELECT * FROM PAPATYA.COMP_TABLE;

        ID DES
---------- ----------
         1 TEST1
         4 TEST4

SQL>
update YASEMIN.BASE_TABLE set des = 'asdf' where id = 1;
---recheck
DECLARE
  vb_Result BOOLEAN;
BEGIN
  vb_Result := dbms_comparison.recheck(comparison_name => 'COMPARISON_FOR_BASE_TABLE',
                                       scan_id         => 28,
                                       perform_row_dif => TRUE);
  IF vb_Result THEN
    dbms_output.put_line('no difs are found');
  ELSE
    dbms_output.put_line('difs are found');
  END IF;
END;
/
---finally drop comparison
BEGIN
  dbms_comparison.drop_comparison(comparison_name    => 'COMPARISON_FOR_BASE_TABLE');
END;
/
DROP TABLE YASEMIN.BASE_TABLE;
DROP TABLE PAPATYA.COMP_TABLE;

---other useful tables to check
select * from user_comparison;
select * from user_comparison_columns;
select * from user_comparison_row_dif;
select * from user_comparison_scan;
select * from user_comparison_scan_summary;
select * from user_comparison_scan_values;


No comments: