I have 2 identical tables with same columns. I have to compare both to detect any changes made in both tables. When a change is detected, I have to get the column name, the value from one table and the value of the other table. I know I can use MINUS statement: select * from table A MINUS select * from table B, the results are rows with all records in table A which table B don't have. That's great, but I still have to compare each value in each column and note the changes. Both tables have 75 columns, it is time consuming to type each column name, so I want to use all_tab_columns to get all the column names. My problem is, how to reference back to the column name in my cursor? Below is the code sample, I am stuck at:' if B.A.column_name != C.A.column_name then' statement.
If you know how to rewrite it or better way to do this, let me know please, I really appreciate any help.
cursor columns_cur is
where table = 'FLO_1'
and owner = 'CIS'
order by column_id;
cursor flo_1_cur is
select * from flo_1;
cursor flo_2_cur(c_flo_id in number) is
select * from flo_2
where flo_id = c_flo_id;
for A in columns_cur
for B in flo_1 cur
for C in flo_2_cur(B.flo_id)
if B.A.column_name != C.A.column_name then
t_column_name := A.column_name;
t_value_changed_ind := 'Y';
t_old_value := B.A.column_name;
t_new_value := C.A.column_name;
t_value_changed_ind := 'N'; end if;