Stored Procedure to compare two table field wise

----- This SP will take two table as input and will compare for thier identical columns.
----- those columns should be identical in name, then it will compare only.
----- after compareing those two columns , it will give common values count for that column from both table
----- it will also give the count for those values which is in 1st table but not in 2nd table , vice versa.
----- it will also give the identical columns has the same data type and precision or not ..?


create or replace procedure SP_TABLE_COMPARISON(Tab_Name1 IN VARCHAR2, Tab_Name2 IN VARCHAR2) as
Table_Name1 USER_TAB_COLUMNS.table_name%type;
Table_Name2 USER_TAB_COLUMNS.table_name%type;
Column_Name1 varchar2(100);
Data_Type1 varchar2(100);
Data_Length1 varchar2(100);
Common_count varchar2(100);
In1_NotIn2 varchar2(100);
In2_NotIn1 varchar2(100);
Total_count_1 varchar2(100);
Total_count_2 varchar2(100);
Prec_flag  varchar2(100);
Data_flag  varchar2(100);
Column_Name2 varchar2(100);
Data_Type2 varchar2(100);
Data_Length2 varchar2(100);
Col_flag varchar2(100);

------------- creating cursor for two tables
------------- for thier all columns and constraints

CURSOR ALL_COLUMN1(tablename1 varchar2) is
SELECT
COLUMN_NAME ,
DATA_TYPE,
DATA_LENGTH,
NULLABLE
FROM USER_TAB_COLUMNS WHERE TABLE_NAME=tablename1 ;
CURSOR ALL_COLUMN2(tablename2 varchar2) is
SELECT
COLUMN_NAME ,
DATA_TYPE,
DATA_LENGTH,
NULLABLE
FROM USER_TAB_COLUMNS WHERE TABLE_NAME=tablename2 ;

ALL_COLUMN1_VIEW        ALL_COLUMN1%rowtype;
ALL_COLUMN2_VIEW        ALL_COLUMN2%rowtype;

begin
--set serveroutput on;
--- opening the cursor ------- 

execute immediate ' truncate table TABLE_COMPARE_REPORT ';
Open ALL_COLUMN1(Tab_Name1);
----start the loop for first table -----
LOOP
Fetch ALL_COLUMN1 into ALL_COLUMN1_VIEW ;
      EXIT WHEN ALL_COLUMN1%NOTFOUND;
      Open ALL_COLUMN2(Tab_Name2);
     
      Column_Name1 := ALL_COLUMN1_VIEW.COLUMN_NAME;
      Data_Type1 := ALL_COLUMN1_VIEW.DATA_TYPE;
      Data_Length1 := ALL_COLUMN1_VIEW.DATA_LENGTH;
      Col_flag := '' ;
      Prec_flag := '' ;
      Data_flag := '' ;
---start the loop for second table ----     
      LOOP
            Fetch ALL_COLUMN2 into ALL_COLUMN2_VIEW ;
            EXIT WHEN ALL_COLUMN2%NOTFOUND;
           
               Column_Name2 := ALL_COLUMN2_VIEW.COLUMN_NAME;
               Data_Type2 := ALL_COLUMN2_VIEW.DATA_TYPE;
               Data_Length2 := ALL_COLUMN2_VIEW.DATA_LENGTH;
           
            IF (Column_Name1 = Column_Name2)
               THEN Col_flag := 'GOT IT..' ;
                
               execute immediate ' select count(distinct ' || Column_Name1 || ') from ' || Tab_Name1   into Total_count_1;
               execute immediate ' select count(distinct ' || Column_Name2 || ') from ' || Tab_Name2   into  Total_count_2;
              
                execute immediate ' select count(*) from (
                                     select ' || Column_Name1 || ' from ' || Tab_Name1 ||
                                   ' intersect
                                    select ' || Column_Name2 || ' from ' ||  Tab_Name2 || ' ) '  into Common_count;
                                       
                execute immediate ' select count(*) from (
                                     select ' || Column_Name1 || ' from ' || Tab_Name1 ||
                                   ' minus
                                    select ' || Column_Name2 || ' from ' ||  Tab_Name2 || ' ) '  into In1_NotIn2;
                                 
                               
                execute immediate ' select count(*) from (
                                     select ' || Column_Name2 || ' from ' || Tab_Name2 ||
                                   ' minus
                                    select ' || Column_Name1 || ' from ' ||  Tab_Name1 || ' ) ' into In2_NotIn1;
               
               
                IF (Data_Type1 = Data_Type2 )
                  THEN Data_flag := 'match' ;
                    ELSE Data_flag := 'no match' ;
                 END IF;
               
                   IF (Data_Length1 = Data_Length2 )
                  THEN Prec_flag := 'match' ;
                    ELSE  Prec_flag := 'no match' ;
                 END IF;
                              
                                  
                
                insert into TABLE_COMPARE_REPORT(
                Column_of_Table1      ,    
                Column_of_Table2       ,  
                Total_Count_in_Tab1,
                Total_Count_in_Tab2,
                Total_Common_Values      , 
                Value_in_Tab1_not_in_Tab2 ,
                Value_in_Tab2_not_in_Tab1 ,
                Data_Type_Check  ,         
                Precision_Check  )        
               
                values
                (
                Column_Name1,
                Column_Name2,
                Total_count_1,
                Total_count_2,
                Common_count,
                In1_NotIn2,
                In2_NotIn1,
                Data_flag,
                Prec_flag
                )   ;            
                 EXIT;
            END IF ;
           
      COMMIT;
      END LOOP;
      CLOSE ALL_COLUMN2;
END LOOP;
CLOSE ALL_COLUMN1;
end ;