How do I dynamically compare data in a table?

I have around 70 tables that have the same structure (only the columns change, but always start with CURRENT_DATE and ID) and I would like to make some function or procedure that would help me speed up the process of checking the data, if its uploaded correctly or if something is new.

Tables structure likes something like this:

CURRENT_DATE	ID	Column1	Column2	...
12.2.2O23	1	some text	some value	some text
12.2.2O23	2	some text	some value	some text
12.2.2O23	3	some text	some value	some text
....	...	some text	some value	some text
21.11.2O23	1	some text	some value	some text
21.11.2O23	2	some text	some value	some text
21.11.2O23	3	some text	some value	some text

I would like to do something where I would input table_name, columns, date1, date2 (dates where I want to compare the data) and the output would be something like this.

STATUS	ID	Column1	Column2	...
NEW	50	some text	some value	some text
NEW	270	some text	some value	some text
CHANGED	15	some text	some value	some text

So my question is, how would you handle this? Using two cursors and then comparing the dynamic data would insert data? Or is there a much simpler way? I'm trying to learn SQL but I'm not sure how to do this and I don't want to write each SQL script by hand for 70 tables.

Thank you everyone and have a nice day!

Solution

Fiddle https://dbfiddle.uk/1Dv0aiDG

Oof, started writing this before remembering what an eye sore dynamic sql is.

Chris Saxon's answer is very good. And probably what I'd do too. It has the big advantage that the results don't have to be objects in the system views. But having to pass in static column names might not be a general as you are looking for.

This assumes that the table pairs have corresponding names _baseline and _compare. Of course this probably isn't your case, but handling your actual names should be trivial.

Each iteration of the table cursor results in a comparison string whole_query_new_and_changed which you can do with what you see fit. Execute immediate or save off somewhere.

Be wary of NULLs in the compare condition. Usually I'll do

field1 <> field2 
Or (field1 is null and field2 is not null) 
or (field2 is not null and field2 is null).

But didn't feel like doing all that typing here.

There are some dummy strings just so the constructions work, ex 1=1 or (1=2). You can trim these out later on if you want it looking cleaner.

Also note that, depending on how this is used sql injection is always something to keep in mind.

Lastly, if this was going to be production code and I didn't want my coworker's eyes bleeding I would do this in python or some other language connecting to Oracle. Much easier to keep readable than dynamic PL/SQL.

create table some_test_data_baseline
   ( id integer,
     column1 varchar(50),
     column2 varchar(50)
   );

create table some_test_data_compare
  ( id integer,
    column1 varchar(50),
    column2 varchar(50)
  );

insert into some_test_data_baseline values (1,'some changed text','some other changed test');
insert into some_test_data_baseline values (2,'new text','some other new test');
insert into some_test_data_baseline values (3,'unchanged text','some other unchanged test');
insert into some_test_data_compare values (1,'some text','some other test');
insert into some_test_data_compare values (3,'unchanged text','some other unchanged test');

declare
  changed_query VARCHAR2(10000);
  new_query VARCHAR2(10000);
  compare_table_name VARCHAR2(255);
  whole_query_new_and_changed VARCHAR2(10000);
  columns_in_table VARCHAR2(10000);
  TYPE cur_cur IS REF CURSOR;
  v_col_cur cur_cur;
  all_columns_query VARCHAR2(2500);
  col_name VARCHAR2(255);
  select_col_list VARCHAR2(2500);
  difference_condition VARCHAR2(2500);
begin
  for table_cur in
    (
     select table_name
       from all_tables
      where upper(table_name) like '%_BASELINE'
    )
  loop
    all_columns_query := 'SELECT column_name 
                            FROM all_tab_columns
                           where table_name = ''' || table_cur.table_name || '''';
    select_col_list := '';
    difference_condition := '';
    changed_query := '';
    OPEN v_col_cur FOR all_columns_query;
       LOOP
       FETCH v_col_cur INTO col_name;
       EXIT WHEN v_col_cur%NOTFOUND;
       select_col_list := select_col_list || 't1.' || col_name || ',';
       difference_condition := difference_condition || ' (t1.' || col_name || ' <> t2.' || col_name || ') OR ';
       END LOOP;
       select_col_list := select_col_list || 'terminated';
       select_col_list := replace(select_col_list,',terminated','');
    CLOSE v_col_cur;
   difference_condition := difference_condition || ' (1=2) ';
   compare_table_name := replace(table_cur.table_name,'_BASELINE','_COMPARE');
   changed_query := 'SELECT ''CHANGED'' as description, ' || select_col_list || ' FROM ' || table_cur.table_name  || ' t1 ' || 
                    ' INNER JOIN ' || compare_table_name || ' t2 
                       ON t1.id = t2.id ' || 
                    ' WHERE (1 = 2) OR ' || difference_condition;
   new_query := 'SELECT ''NEW'' as description, ' || select_col_list || ' from ' || table_cur.table_name || ' t1 ' ||
                '  where not exists ( select 1 from ' || compare_table_name || ' t2 ' ||
                '                        where t1.id = t2.id )';
   whole_query_new_and_changed := changed_query || ' 
     UNION 
     ' || new_query;
   dbms_output.put_line(whole_query_new_and_changed);
   END LOOP;

end;

-- Single iteration's output, formatted.
SELECT
  'CHANGED' as description,
  t1.ID,
  t1.COLUMN1,
  t1.COLUMN2
FROM
  SOME_TEST_DATA_BASELINE t1
  INNER JOIN SOME_TEST_DATA_COMPARE t2 ON t1.id = t2.id
WHERE
  (1 = 2)
  OR (t1.ID <> t2.ID)
  OR (t1.COLUMN1 <> t2.COLUMN1)
  OR (t1.COLUMN2 <> t2.COLUMN2)
  OR (1 = 2)
UNION
SELECT
  'NEW' as description,
  t1.ID,
  t1.COLUMN1,
  t1.COLUMN2
from
  SOME_TEST_DATA_BASELINE t1
where
  not exists (
    select
      1
    from
      SOME_TEST_DATA_COMPARE t2
    where
      t1.id = t2.id
  )