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.
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
), new_data as (
select * from (
select 'new - old' test, a.* from new_data a
select 'new - old' test, b.* from old_data b
select * from (
select 'old - new' test, a.* from old_data a
select 'old - new' test, b.* from new_data b
This one is OK for small tables.
select /*+ parallel */ sum(
|| to_char( e.hiredate, 'yyyy-mm-dd hh24:mi:ss')
from scott.emp e