plsql - comparing tables in oracle across schemas -

below code used compare 2 tables in different schemas.

issue: comparing 2 schemas, able "table matching" output, not "table not matching" output.

create or replace procedure comparesch2( src_cowner in all_tables.owner%type , tgt_cowner in all_tables.owner%type) -- source cursor declaration  cursor c_tbl_src   select table_name         all_tables            owner = src_cowner; -- target cursor declaration cursor c_tbl_tgt   select table_name         all_tables            owner = tgt_cowner;  r_tbl_src c_tbl_src%rowtype;  r_tbl_tgt c_tbl_tgt%rowtype; begin   open c_tbl_src;    open c_tbl_tgt; loop    fetch c_tbl_src r_tbl_src;     exit when c_tbl_src%notfound; fetch c_tbl_tgt r_tbl_tgt;     exit when c_tbl_tgt%notfound; if r_tbl_src.table_name != r_tbl_tgt.table_name     dbms_output.put_line ( ' table donot match:'|| r_tbl_src.table_name);  end if; if r_tbl_src.table_name = r_tbl_tgt.table_name    dbms_output.put_line ( ' table  match:'|| r_tbl_src.table_name); end if;   end loop; close c_tbl_src; close c_tbl_tgt; end; 

one of main problems comparing nulls. easiest solution avoid problem putting of logic in sql instead of pl/sql:

create or replace procedure compare_sch2(src_cowner varchar2, tgt_cowner varchar2) begin     tables in     (         select             case                 when count(*) = 1 'table not match: '||table_name                 when count(*) = 2 'table matches: '||table_name             end does_it_match         all_tables         owner in (src_cowner, tgt_cowner)         group table_name         order table_name     ) loop         dbms_output.put_line(tables.does_it_match);     end loop; end; / 

here's sample:

create user schema1 identified schema1; create user schema2 identified schema2;  create table schema1.on_both(a number); create table schema2.on_both(a number); create table schema1.on_1_only(a number); create table schema2.on_2_only(a number);  grant select on schema1.on_both jheller; grant select on schema2.on_both jheller; grant select on schema1.on_1_only jheller; grant select on schema2.on_2_only jheller;   begin     compare_sch2('schema1', 'schema2'); end; /  output:  table not match: on_1_only table not match: on_2_only table matches: on_both