/*

-- Replace castoncast_sql_src_local with your LOCAL SQL KB, castoncast_ora_local with your LOCAL ORACLE KB

-- E.g.: 	I replaced castoncast_sql_src_local with castpubs_sql_src_local
--		and castoncast_ora_local with castpubs_ora_local

*/
set search_path to castoncast_ora_local;

create or replace view TransformOracleObjects
as
select distinct lower(substring(ofn.fullname, position('.' in ofn.fullname) + 1)) ObjectName, 
	case 
		when t.TypDsc = 'Oracle check table constraint' then 'Check Constraint'
		when t.TypDsc = 'Oracle default table constraint' then 'Default Constraint'
		when t.TypDsc = 'Oracle foreign key table constraint' then 'Foreign Key'
		when t.TypDsc = 'Oracle primary key table constraint' then 'Indexes Primary Keys Unique Constraints'
		when t.TypDsc = 'Oracle index' then 'Indexes Primary Keys Unique Constraints'
		when t.TypDsc = 'Oracle bitmap index' then 'Indexes Primary Keys Unique Constraints'
		when t.TypDsc = 'Oracle function based index' then 'Indexes Primary Keys Unique Constraints'
		when t.TypDsc = 'Oracle table' then 'Table'
		when t.TypDsc = 'Oracle instance' then 'Instance'
		when t.TypDsc like '%collection%' then 'Collection'
		when t.TypDsc like '%object%' then 'Type'
		when t.TypDsc = 'Oracle function' then 'Function'
		when t.TypDsc = 'Oracle private stored function' then 'Function'
		when t.TypDsc like '%procedure%' then 'Procedure'
		when t.TypDsc like '%trigger%' then 'Trigger'
		when t.TypDsc like '%package header%' or t.TypDsc like '%package body%' then 'Package'
		when t.TypDsc like '%column%' then 'Column'
		when t.TypDsc like '%synonym%' then 'Synonym'
		when t.TypDsc like '%schema%' then 'Schema'
		when t.TypDsc like '%unique table constraint%' then 'Indexes Primary Keys Unique Constraints'
		when t.TypDsc like '%view%' then 'View'
		when t.TypDsc like '%sequence%' then 'Sequence'
		when t.TypDsc like '%Database Link%' then 'Database Link'
		when t.TypDsc like '%package cursor%' then 'Package Cursor'
		when t.TypDsc like '%cursor datatype%' then 'Cursor Datatype'
		when t.TypDsc like '%Package Variable%' then 'Package Variable'
	else t.TypDsc
	end ObjectType
from ObjFulNam ofn join
Keys k 
on k.IdKey = ofn.IdObj
join Typ t
on k.ObjTyp = t.IdTyp
where t.IdTyp in (select IdTyp from TypCat where IdCatParent = 140750)
and (	t.TypDsc not like '%Project%' 
	and t.TypDsc not like '%Subset%'
	)
order by ObjectType, ObjectName;

create or replace view Count_TransformOracleObjects
as
select ObjectType, count(1) NumberOfObjects
from TransformOracleObjects
group by ObjectType
order by ObjectType;


set search_path to castoncast_sql_src_local;

create or replace view TransformSQLObjects
as
select distinct lower(ofn.fullname) ObjectName, 
	case
		when t.TypDsc like '%Column%' then 'Column'
		when t.TypDsc like '%Unique%' or t.TypDsc like '%Index%' or t.TypDsc like '%Primary%' then 'Indexes Primary Keys Unique Constraints'
	else t.TypDsc 
	end ObjectType
from ObjFulNam ofn join
Keys k 
on k.IdKey = ofn.IdObj
join Typ t
on k.ObjTyp = t.IdTyp
where t.IdTyp in (select IdTyp from TypCat where IdCatParent = 1101004)
and (	t.TypDsc not like '%Project%' 
	and t.TypDsc not like '%Subset%'
	)
order by ObjectType, ObjectName;


create or replace view Count_TransformSQLObjects
as
select ObjectType, count(1) NumberOfObjects
from TransformSQLObjects
group by ObjectType
order by ObjectType;


create or replace view Compare_NumberOf_SQLORAObjects
as
select case 
	when sql.ObjectType is null then ora.ObjectType  
	when ora.ObjectType is null then sql.ObjectType
	else sql.ObjectType
	end ObjectType,
case 
	when sql.ObjectType is null then ora.NumberOfObjects  
	when ora.ObjectType is null then sql.NumberOfObjects
	else sql.NumberOfObjects  
	end NumberOfObjects,
case 
	when sql.ObjectType is null then
		case when ora.ObjectType in ('Check Constraint', 'Instance', 'Package Cursor', 'Sequence') 
			then 'Object type not implemented on SQL'
			else 'Detected only by PL/SQL Analyzer' 
		end
	when ora.ObjectType is null then 'Detected only by SQL Analyzer'
	when sql.NumberOfObjects <> ora.NumberOfObjects then (sql.NumberOfObjects - ora.NumberOfObjects)::text
	else '=' 
	end "Compared with Oracle" 
from
castoncast_sql_src_local.Count_TransformSQLObjects sql full outer join castoncast_ora_local.Count_TransformOracleObjects ora
on ora.ObjectType = sql.ObjectType
order by sql.ObjectType, ora.ObjectType, sql.NumberOfObjects, ora.NumberOfObjects;


create or replace view Differences_SQLORAObjects
as
select case 
	when sql.ObjectType is null then ora.ObjectType  
	when ora.ObjectType is null then sql.ObjectType
	else sql.ObjectType
	end ObjectType,
case 
	when sql.ObjectName is null then ora.ObjectName  
	when ora.ObjectName is null then sql.ObjectName
	else sql.ObjectName  
	end ObjectName,
case 
	when sql.ObjectType is null then
		case when ora.ObjectType in ('Check Constraint', 'Instance', 'Package Cursor', 'Sequence') 
			then 'Object type not implemented on SQL'
			else 'Detected only by PL/SQL Analyzer' 
		end
	when ora.ObjectName is null then 'Detected only by SQL Analyzer'
	else '=' 
	end "Compared with Oracle" 
from 
	(select * from castoncast_sql_src_local.TransformSQLObjects where ObjectType in (select ObjectType from castoncast_sql_src_local.Compare_NumberOf_SQLORAObjects
	 where "Compared with Oracle" not in ('=', 'Object type not implemented on SQL'))
	) sql full outer join 
	(select * from castoncast_ora_local.TransformOracleObjects where ObjectType in (select ObjectType from castoncast_sql_src_local.Compare_NumberOf_SQLORAObjects 
	where "Compared with Oracle" not in ('=', 'Object type not implemented on SQL'))
	) ora
on ora.ObjectName = sql.ObjectName and ora.ObjectType = sql.ObjectType
order by ObjectType, ObjectName;


select * from Compare_NumberOf_SQLORAObjects;

select * from Differences_SQLORAObjects;
