CODEWITHSUNDEEP
oracle-databaseplsql
sri
sri

Reputation: 21

comparing tables in oracle across schemas

Below is the code used to compare two tables in different schemas.

Issue: Comparing two schemas, I am able to get the "Table Matching" output, but not the "Table Not matching" output.

create or replace procedure comparesch2( src_cowner IN all_tables.owner%TYPE , tgt_cowner IN all_tables.owner%TYPE) IS
-- Source Cursor declaration 
cursor c_tbl_src IS
  SELECT table_name
   FROM 
    all_tables
     where
      owner = src_cowner;
-- Target Cursor declaration
cursor c_tbl_tgt IS
  SELECT table_name
   FROM 
    all_tables
     where
      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 INTO r_tbl_src;
    EXIT WHEN c_tbl_src%NOTFOUND;
fetch c_tbl_tgt INTO r_tbl_tgt;
    EXIT WHEN c_tbl_tgt%NOTFOUND;
IF r_tbl_src.table_name != r_tbl_tgt.table_name THEN
    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 THEN
   dbms_output.put_line ( ' Table  Match:'|| r_tbl_src.table_name);
end if;
  END LOOP;
CLOSE c_tbl_src;
CLOSE c_tbl_tgt;
END;

Upvotes: 2

Views: 947

Answers (1)

Jon Heller
Jon Heller

Reputation: 36922

One of the main problems was with comparing NULLs. The easiest solution is to avoid the problem by putting most of the logic in SQL instead of PL/SQL:

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

Here's a sample:

create user schema1 identified by schema1;
create user schema2 identified by 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 to jheller;
grant select on schema2.on_both to jheller;
grant select on schema1.on_1_only to jheller;
grant select on schema2.on_2_only to jheller;


begin
    compare_sch2('SCHEMA1', 'SCHEMA2');
end;
/

Output:

Table does not match: ON_1_ONLY
Table does not match: ON_2_ONLY
Table matches: ON_BOTH

Upvotes: 1

Related Questions