/*

-- Replace %LOCAL_SQL% with your LOCAL SQL KB, %LOCAL_ORACLE% with your LOCAL ORACLE KB

-- E.g.: 	I replaced %LOCAL_SQL% with castpubs_sql_src_local
--		and %LOCAL_ORACLE% with castpubs_ora_local

*/

set search_path to %LOCAL_ORACLE%;

create or replace view Details_ORALinks
as
select case when clrtyp like '%column%' then 'Column'
	when lower(clrtyp) like '%package body%' or lower(clrtyp) like '%package header%' then 'Package'	
	else initcap(replace(replace(clrtyp, 'Oracle ', ''), 'dml ', '')) end CallerType, 
	lower(substring(clrnam, position('.' in clrnam) + 1)) CallerName,
case when cletyp like '%column%' then 'Column'
	when lower(cletyp) like '%package body%' or lower(cletyp) like '%package header%' then 'Package'
	else initcap(replace (replace(cletyp, 'Oracle ', ''), 'dml ', '')) end CalleeType, 
	lower(substring(clenam, position('.' in clenam) + 1)) CalleeName,
	case when acctyplo like '%USE%' then 'USE'
	else acctyplo end KindOfLink
from IDRE_ACC
where acctyplo like '%USE%' or acctyplo like '%ACCESS%' or acctyplo like '%CALL%'
order by 1, 2;


create or replace view Count_ORANumberOfLinks
as
select CallerType,CalleeType, KindOfLink, sum( NumberOfLinks) NumberOfLinks
from(
select case when clrtyp like '%column%' then 'Column'
	when lower(clrtyp) like '%package header%' or lower(clrtyp) like '%package body%' then 'Package'
	else initcap(replace(replace(clrtyp, 'Oracle ', ''), 'dml ', '')) end CallerType, 
case when cletyp like '%column%' then 'Column'
	when lower(cletyp) like '%package header%' or lower(cletyp) like '%package body%' then 'Package'
	else initcap(replace (replace(cletyp, 'Oracle ', ''), 'dml ', '')) end CalleeType, 
	case when acctyplo like '%USE%' then 'USE'
	else acctyplo end KindOfLink, 
	count(1) NumberOfLinks
from IDRE_ACC
where acctyplo like '%USE%' or acctyplo like '%ACCESS%' or acctyplo like '%CALL%'
group by clrtyp, cletyp, acctyplo) count_links
group by CallerType,CalleeType, KindOfLink
order by CallerType,CalleeType;

set search_path to %LOCAL_SQL%;

create or replace view Details_SQLLinks
as
select  case when lower(clrtyp) like '%column%' then 'Column'
	else clrtyp
	end CallerType, 
	lower(clrnam) CallerName,
	case when lower(cletyp) like '%column%' then 'Column'
	else cletyp
	end CalleeType, 
	lower(clenam) CalleeName,
	acctyplo KindOfLink
from IDRE_ACC
where acctyplo like '%USE%' or acctyplo like '%ACCESS%' or acctyplo like '%CALL%'
order by 1, 2;

create or replace view Count_SQLNumberOfLinks
as
select  case when lower(clrtyp) like '%column%' then 'Column'
	else clrtyp
	end CallerType, 
	case when lower(cletyp) like '%column%' then 'Column'
	else cletyp
	end CalleeType, 
	acctyplo KindOfLink, 
	count(1) NumberOfLinks
from IDRE_ACC
where acctyplo like '%USE%' or acctyplo like '%ACCESS%' or acctyplo like '%CALL%'
group by clrtyp, cletyp, acctyplo
order by 1, 2;

create or replace view Compare_NumberOf_SQLORALinks
as
select case 
	when sql.KindOfLink is null then ora.KindOfLink  
	when ora.KindOfLink is null then sql.KindOfLink
	else sql.KindOfLink  
	end KindOfLink,
case 
	when sql.CallerType is null then ora.CallerType  
	when ora.CallerType is null then sql.CallerType
	else sql.CallerType
	end CallerType,
case 
	when sql.CalleeType is null then ora.CalleeType  
	when ora.CalleeType is null then sql.CalleeType
	else sql.CalleeType  
	end CalleeType,
case 
	when sql.CallerType is null then 'Detected only by PL/SQL Analyzer' 
	when ora.CallerType is null then 'Detected only by SQL Analyzer'
	when ora.NumberOfLinks = sql.NumberOfLinks then '='
	else (sql.NumberOfLinks - ora.NumberOfLinks)::text
	end "Compared with Oracle" 
from 
%LOCAL_SQL%.Count_SQLNumberOfLinks sql full outer join %LOCAL_ORACLE%.Count_ORANumberOfLinks ora
on ora.CallerType = sql.CallerType and ora.CalleeType = sql.CalleeType and ora.KindOfLink = sql.KindOfLink
order by KindOfLink,CallerType, CalleeType;


select * from Compare_NumberOf_SQLORALinks;


create or replace view CompareSQLORADetailsLinks
as
select case 
	when sql.KindOfLink is null then ora.KindOfLink  
	when ora.KindOfLink is null then sql.KindOfLink
	else sql.KindOfLink  
	end KindOfLink,
case 
	when sql.CallerType is null then ora.CallerType  
	when ora.CallerType is null then sql.CallerType
	else sql.CallerType
	end CallerType,
case 
	when sql.CallerName is null then ora.CallerName  
	when ora.CallerName is null then sql.CallerName
	else sql.CallerName  
	end CallerName,
case 
	when sql.CalleeType is null then ora.CalleeType  
	when ora.CalleeType is null then sql.CalleeType
	else sql.CalleeType  
	end CalleeType,
case 
	when sql.CalleeName is null then ora.CalleeName  
	when ora.CalleeName is null then sql.CalleeName
	else sql.CalleeName  
	end CalleeName,	
case 
	when sql.CallerType is null then 'Detected only by PL/SQL Analyzer' 
	when ora.CallerType is null then 'Detected only by SQL Analyzer'
	when ora.CallerName = sql.CallerName and ora.CalleeName = sql.CalleeName then '='
	end "Compared with Oracle" 
from 
%LOCAL_SQL%.Details_SQLLinks sql full outer join %LOCAL_ORACLE%.Details_ORALinks ora
on ora.CallerType = sql.CallerType 
	and ora.CallerName = sql.CallerName
	and ora.CalleeType = sql.CalleeType 
	and ora.CalleeName = sql.CalleeName
	and ora.KindOfLink = sql.KindOfLink
where sql.CallerType is NULL or ora.CallerType is NULL
order by KindOfLink, CallerType, CalleeType, CallerName, CalleeName;


select * from CompareSQLORADetailsLinks;