How to loop in a cursor referencing column names in all_tab_columns

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
select column_name
from all_tab_columns
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;

t_column_name       varchar2(20);
t_value_changed_ind       varchar2(1);
t_old_value             varchar2(20);
t_new_value            varchar2(20);
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;
            end loop;
      end loop;
end loop;
Who is Participating?
HainKurtConnect With a Mentor Sr. System AnalystCommented:
u can use exec immediate... maybe it works... not sure now..

col_equal boolean;
exec immediate "col_equal := B." || A.column_name || "="C." || A.column_name || ";"
exec immediate "begin col_equal := B." || A.column_name || "="C." || A.column_name || "; end;"
then use col_equal

if col_equal then
end if
muliaAuthor Commented:
Thanks, not the exact answer I was looking for, but it gave me an idea to exec immediate select the old value into a variable and select the new value into a variable, then compare both variables.

Have you figured out how to do it?
I need help in doing this too. I am thinking of spooling data from 2 tables into two different flat files and then do a diff on them.
since I have to modify one table to be like the other table.
but I have to use all_tab_columns to get the order of columns to spool into flat file.
select ( select column_name from all_tab_columns where table_name = 'x' order by column_id) from x;
something like this. Can you help me with that.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.