1

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?

closed as too broad by mustaccio, Marcello Miorelli, John Eisbrener, John aka hot2use, Colin 't Hart Sep 11 at 9:32

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

0

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.

./sdcli.exe unittest -run -suite -name AWARD_BONUS_SUITE -repo UNIT_TEST_REPO_CONNECTION -db TARGET_SCHEMA_CONNECTION

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 (
    select
        banner -- specify your columns here
    from v$version
), new_data as (
    select
        banner
    from v$version@production
)
select * from (
    select 'new - old' test, a.* from new_data a
    minus
    select 'new - old' test, b.* from old_data b
)
union all
select * from (
    select 'old - new' test, a.* from old_data a
    minus
    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.