set search_path to castpubs_sql_src_local;

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 castpubs_sql_src_local.TransformSQLObjects where ObjectType in (select ObjectType from castpubs_sql_src_local.Compare_NumberOf_SQLORAObjects
	 where "Compared with Oracle" not in ('=', 'Object type not implemented on SQL'))
	) sql full outer join 
	(select * from castpubs_ora_local.TransformOracleObjects where ObjectType in (select ObjectType from castpubs_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;

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
castpubs_sql_src_local.Count_TransformSQLObjects sql full outer join castpubs_ora_local.Count_TransformOracleObjects ora
on ora.ObjectType = sql.ObjectType
order by sql.ObjectType, ora.ObjectType, sql.NumberOfObjects, ora.NumberOfObjects;


select * from Compare_NumberOf_SQLORAObjects;

select * from Differences_SQLORAObjects;
