SchCompare.sql

==== CREATING TABLES ====
create table saner10 as select a.TABLE_NAME, a.COLUMN_NAME, a.DATA_TYPE, a.DATA_TYPE_MOD,
a.DATA_TYPE_OWNER, a.DATA_LENGTH, a.DATA_PRECISION, a.DATA_SCALE, a.NULLABLE, a.COLUMN_ID
from user_tab_columns a, user_tables b where a.table_name = b.table_name;


create table saner9 as select a.TABLE_NAME, a.COLUMN_NAME, a.DATA_TYPE, a.DATA_TYPE_MOD,
a.DATA_TYPE_OWNER, a.DATA_LENGTH, a.DATA_PRECISION, a.DATA_SCALE, a.NULLABLE, a.COLUMN_ID
from user_tab_columns@er90 a, user_tables@er90 b where a.table_name = b.table_name;

==========CODE STARTS HERE=============
DECLARE
t_prev_tab varchar2(30);
DATA_TYPE_10 saner10.data_type%type;
DATA_TYPE_MOD_10 saner10.data_type_mod%type;
DATA_TYPE_OWNER_10 saner10.DATA_TYPE_OWNER%type;
DATA_LENGTH_10 saner10.DATA_LENGTH%type;
DATA_PRECISION_10 saner10.DATA_PRECISION%type;
DATA_SCALE_10 saner10.DATA_SCALE%type;
NULLABLE_10 saner10.NULLABLE%type;
COLUMN_ID_10 saner10.COLUMN_ID%type;
DATA_TYPE_9 saner10.data_type%type;
DATA_TYPE_MOD_9 saner10.data_type_mod%type;
DATA_TYPE_OWNER_9 saner10.DATA_TYPE_OWNER%type;
DATA_LENGTH_9 saner10.DATA_LENGTH%type;
DATA_PRECISION_9 saner10.DATA_PRECISION%type;
DATA_SCALE_9 saner10.DATA_SCALE%type;
NULLABLE_9 saner10.NULLABLE%type;
COLUMN_ID_9 saner10.COLUMN_ID%type;

BEGIN
dbms_output.put_line(rpad('=',80,'='));
dbms_output.put_line('Tables that do not exist in ER9.x Database');
for i in (select distinct a.table_name
from saner10 a
where not exists (select 1
from saner9 b
where substr(a.table_name,3) = b.table_name)) loop
dbms_output.put_line('==> '||i.table_name);
end loop;
dbms_output.put_line(rpad('=',80,'='));
dbms_output.put_line('Tables that do not exist in ER10 Database');
for i in (select distinct a.table_name
from saner9 a
where not exists (select 1
from saner10 b
where a.table_name = substr(b.table_name,3))) loop
dbms_output.put_line('==> '||i.table_name);
end loop;

-- Missing Columns Code
dbms_output.put_line(rpad('=',80,'='));
dbms_output.put_line('The following columns are new in ER10');
for i in (select distinct a.table_name
from saner10 a
where exists (select 1 from saner9 b where substr(a.table_name,3) = b.table_name)
order by a.table_name)
loop
t_prev_tab := i.table_name;
for j in (
select a.table_name, a.column_name
from saner10 a
where not exists (select 1
from saner9 b
where substr(a.table_name,3) = b.table_name
and a.column_name = b.column_name)
and a.table_name = i.table_name
) loop
if t_prev_tab = i.table_name then
dbms_output.put_line('Table: '||j.table_name);
t_prev_tab := null;
end if;
dbms_output.put_line(' ==> '||j.column_name);
end loop;
end loop;
dbms_output.put_line(rpad('=',80,'='));
dbms_output.put_line('The following columns are in ER9.x but NOT IN ER 10');
for i in (select distinct a.table_name
from saner10 a
where exists (select 1 from saner9 b where substr(a.table_name,3) = b.table_name)
order by a.table_name)
loop
for j in (
select a.table_name, a.column_name
from saner9 a
where not exists (select 1
from saner10 b
where substr(b.table_name,3) = a.table_name
and a.column_name = b.column_name)
and a.table_name = i.table_name
) loop
dbms_output.put_line('==> '||j.table_name||'.'||j.column_name);
end loop;
end loop;

--Column exists but DIFFERS code
dbms_output.put_line(rpad('=',80,'='));
dbms_output.put_line('Following columns exists in both differs in ER10');
for i in (select distinct a.table_name, a.column_name
from saner10 a
where exists (select 1 from saner9 b
where substr(a.table_name,3) = b.table_name
and a.column_name = b.column_name)
order by a.table_name, a.column_name)
loop

for j in (select substr(a.table_name,3) table_name, a.COLUMN_NAME, a.DATA_TYPE, a.DATA_TYPE_MOD,
a.DATA_TYPE_OWNER, a.DATA_LENGTH, a.DATA_PRECISION, a.DATA_SCALE, a.NULLABLE, a.COLUMN_ID
from saner10 a
where A.table_name = i.table_name
and a.column_name = i.column_name
minus
select a.table_name, a.COLUMN_NAME, a.DATA_TYPE, a.DATA_TYPE_MOD,
a.DATA_TYPE_OWNER, a.DATA_LENGTH, a.DATA_PRECISION, a.DATA_SCALE, a.NULLABLE, a.COLUMN_ID
from saner9 a
where A.table_name = substr(i.table_name,3)
and a.column_name = i.column_name)
loop
select DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER, DATA_LENGTH,
DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID
into DATA_TYPE_10, DATA_TYPE_MOD_10, DATA_TYPE_OWNER_10, DATA_LENGTH_10,
DATA_PRECISION_10, DATA_SCALE_10, NULLABLE_10, COLUMN_ID_10
from saner10
where table_name = i.table_name
and column_name = i.column_name;

select DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER, DATA_LENGTH,
DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID
into DATA_TYPE_9, DATA_TYPE_MOD_9, DATA_TYPE_OWNER_9, DATA_LENGTH_9,
DATA_PRECISION_9, DATA_SCALE_9, NULLABLE_9, COLUMN_ID_9
from saner9
where table_name = substr(i.table_name,3)
and column_name = i.column_name;
dbms_output.put_line(rpad('=',80,'='));
dbms_output.put_line('Column differences for Table '||i.table_name||'.'||i.column_name);
dbms_output.put_line(lpad('in ER10',27,' ')||rpad(' ',23,' ')||'in ER9.x');

dbms_output.put_line(rpad('Data Type',20,'-') ||rpad(data_type_10,30,' ')||rpad(data_type_9,30,' '));
dbms_output.put_line(rpad('Data Type Mod',20,'-') ||rpad(data_type_mod_10,30,' ')||rpad(data_type_mod_9,30,' '));
dbms_output.put_line(rpad('Data Type Owner',20,'-') ||rpad(data_type_owner_10,30,' ')||rpad(data_type_owner_9,30,' '));
dbms_output.put_line(rpad('Data Length',20,'-') ||rpad(data_length_10,30,' ')||rpad(data_length_9,30,' '));
dbms_output.put_line(rpad('Data Precision',20,'-') ||rpad(data_precision_10,30,' ')||rpad(data_precision_9,30,' '));
dbms_output.put_line(rpad('Data Scale',20,'-') ||rpad(data_scale_10,30,' ')||rpad(data_scale_9,30,' '));
dbms_output.put_line(rpad('Nullable',20,'-') ||rpad(nullable_10,30,' ')||rpad(nullable_9,30,' '));
dbms_output.put_line(rpad('Column ID',20,'-') ||rpad(column_id_10,30,' ')||rpad(column_id_9,30,' '));
end loop;
end loop;
end;
/