cpmpare.sql

/*
        The script iterates through all the tables existing in both schemas,
        and identifies all the records missed in any of those tables. Record
        comparison is based upon table columns common in both schemas; - it is
        assumed that those column are of the same datatype (though, their size
        might be different).

        Database link setup is borrowed from O.Sorland's script.
        */
        set serveroutput on
        set arraysize 1
        set maxdata 32000
        begin
         dbms_output.enable(999999);
        end;
        /
        undef prex
        undef prefx
        undef a
        undef thisuser
        undef b
        undef REMOTESCHEMA
        undef REMOTEPASSW
        undef connstring
        undef c
        undef todaysdate
        variable prefx varchar2(3)
        declare
        i number ;
        j number ;
        k number ;
        cnt number;
        begin
         for i in ascii('A') .. ascii('Z') loop
          for j in ascii('A') .. ascii('Z') loop
           for k in ascii('A') .. ascii('Z') loop
             select count(*) into cnt from user_objects where object_name like
             chr(i)||chr(j)||chr(k)||'%';
             if cnt = 0 then
               :prefx := chr(i)||chr(j)||chr(k);
               return;
             end if;
            end loop;
           end loop;
          end loop;
        end;
        /
        column a new_val prex
        set verify off
        set linesize 132
        set feedback off
        select :prefx a from dual;
        column b new_val thisuser
        select user b from dual;
        column c new_val todaysdate
        select to_char(sysdate,'DD-MON-YYYY HH24:MI') c from dual;
        accept REMOTESCHEMA char prompt 'Enter remote username:'
        accept REMOTEPASSW char prompt 'Enter remote password:' hide
        accept connstring char prompt 'Enter remote connectstring:'
        spool tblsdiff
        PROMPT
        PROMPT
        PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        PROMPT     TABLE DATA DIFFERENCES     &todaysdate
        PROMPT
        PROMPT           this schema: &thisuser
        PROMPT         remote schema: &remoteschema.@&connstring
        PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        PROMPT
        PROMPT
        create database link &prex.lnk connect to &REMOTESCHEMA identified
        by &REMOTEPASSW using '&CONNSTRING';
        PROMPT
        PROMPT
        declare
            cursor ctbl is
            select table_name from user_tables
                intersect
            select table_name from user_tables@&prex.lnk
            ;
            tblname varchar2(30);
            dblink varchar2(50);
        begin
            dblink := '&prex.lnk';
            open ctbl;
            loop
                fetch ctbl into tblname;
                exit when ctbl%notfound;
                declare
                    cursor ccol is
                    select cname from col where tblname = tname
                                    and cname <> 'CREDATE'
                                    and cname <> 'CREUSER'
                                    and cname <> 'UPDDATE'
                                    and cname <> 'UPDUSER'
                        intersect
                    select cname from col@&prex.lnk where tblname = tname;
                    colname varchar2(100);
                    cnames varchar2(4000);
                    i      integer;
                    query varchar2(4000);
                    rec_tab dbms_sql.desc_tab;
                    rec   varchar2(4000);
                    rec_col varchar2(2000);
                    isdiff  boolean;
                    procedure tbl_diff(query in varchar2, msg in varchar2) is
                        cursor_name INTEGER;
                        ret INTEGER;
                    begin
                        cursor_name := dbms_sql.open_cursor;
                        dbms_sql.parse(cursor_name, query, DBMS_SQL.native);
                        FOR j IN 1..i LOOP
                            DBMS_SQL.DEFINE_COLUMN(cursor_name, j, rec_col, 30);
                        END LOOP;
                        ret := dbms_sql.execute(cursor_name);
                        --dbms_sql.describe_columns(cursor_name, i, rec_tab);
                        LOOP
                          IF DBMS_SQL.FETCH_ROWS(cursor_name)>0 THEN
                            -- get column values of the row
                            rec := '';
                            FOR j IN 1..i LOOP
                                DBMS_SQL.COLUMN_VALUE(cursor_name, j, rec_col);
                                rec := rec || SUBSTR(rec_col,1,30) || ',';
                            END LOOP;
                            if isdiff=false then
                                dbms_output.put_line('-------------------------------------');
                                dbms_output.put_line(tblname || msg);
                                dbms_output.put_line('-------------------------------------');
                                isdiff:=true;
                            end if;
                            dbms_output.put_line(rec);
                          ELSE
                          -- no more row to copy
                            EXIT;
                          END IF;
                        END LOOP;

                        dbms_sql.close_cursor(cursor_name);
                    exception
                        WHEN OTHERS THEN
                        dbms_sql.close_cursor(cursor_name);
                        raise;
                    end;
                begin
                    open ccol;
                    cnames := '';
                    i := 0;
                    loop
                        fetch ccol into colname;
                        exit when ccol%notfound;
                        if i<>0 then
                            cnames := cnames || ',';
                        end if;
                        cnames := cnames || colname;
                        i := i+1;
                    end loop;
                    close ccol;
                    --EXECUTE IMMEDIATE
                    isdiff := false;
                    query :=
                        ' select ' || cnames || ' from ' || tblname || '@' || dblink ||
                        ' minus ' ||
                        ' select ' || cnames || ' from ' || tblname ;
                    tbl_diff(query, ' - local data missed:');
                    query :=
                        ' select ' || cnames || ' from ' || tblname ||
                        ' minus ' ||
                        ' select ' || cnames || ' from ' || tblname || '@' || dblink ;
                    tbl_diff(query, ' - remote data missed:');
                end;
            end loop;
        end;
        /
        drop database link &prex.lnk;
        --drop table &prex.comcod;
        spool off
        set verify on
        set feedback on
        undef prex
        undef prefx
        undef a
        undef thisuser
        undef b
        undef REMOTESCHEMA
        undef REMOTEPASSW
        undef connstring
        undef c
        undef todaysdate