We're re-writing a COBOL application in Java. We want to make sure, the applications behave the same, by validating our unit tests produce the same DB changes.

How can we easily diff the data in the DB to a given master-DB (e.g. the Database in which the COBOL program has written data). We want to do this in an automated way. Bonus: Is it possible to exclude several columns like date columns in a comparison?


There are two methods for comparing tables. Method 1 works well with Unit Testing software.

You will want to use some sort of Unit Testing Software (and run it from the command line).

For full automation, you'll need some sort of DevOps Infrastructure.

SQL Developer Unit Testing

SQL Developer ($0) has some Unit Testing Capabilities

Running it from Command Line was found in this Related StackOverflow question.


Building the Test

  • You'll need to create a no-op function to "test".
  • The "Process Validation" will be of type "Query returning no row(s)"

Building the Query

I cheat. I use a template based code generator to create the comparison SQL(s).

There are a few different ones for Oracle. (FTLDB, tePLSQL, jk64)

There is also a plug-in for SQL Developer that allows you to place a GUI around the parameters for your template (eg table_name and db_link). The name for the plugin is oddgen.

Method 1

This is the Brute-Force Method of comparing tables.

I cheat. I use a template based code generator to create this code.

with old_data as (
        banner -- specify your columns here
    from v$version
), new_data as (
    from v$version@production
select * from (
    select 'new - old' test, a.* from new_data a
    select 'new - old' test, b.* from old_data b
union all
select * from (
    select 'old - new' test, a.* from old_data a
    select 'old - new' test, b.* from new_data b

Method 2

This one is OK for small tables.

select /*+ parallel */ sum(
    ora_hash( e.empno 
           || e.ename
           || to_char( e.hiredate, 'yyyy-mm-dd hh24:mi:ss')
) hash_value
from scott.emp e

Not the answer you're looking for? Browse other questions tagged or ask your own question.