-- set echo off
--
--  Name: storedobjsdiff.sql    
--
--  Description: Objects - Display differences between stored objects of two different instances
--
--  Usage: @storedobjsdiff
--
--

set pagesize 0 
set linesize 105 
set verify off
set echo off
set feedback off 
set pause off;

--accept obj_owner prompt 'Table Owner: '
accept inst_1 prompt 'First instance DB Link (Include @):'
accept inst_2 prompt 'Second instance DB Link (Include @):'

column inst_code1 noprint new_value inst_code1
column inst_code2 noprint new_value inst_code2
column column_id noprint
--
define lsize=400
set linesize &&lsize
--
define out_file=storedobjsdiff.ddl
spool &&out_file
--
select value inst_code1
  from v$parameter&inst_1
where name = 'db_name'
/
select value inst_code2
  from v$parameter&inst_2
where name = 'db_name'
/
--
--select 'set heading off' from dual;
--select 'set feedback off' from dual;
select 'select ''##################################################################################### '' from dual; ' from dual;
--select 'set feedback on' from dual;
--select 'set heading on' from dual;
--

select 'select ''Object ----> '||object_type||' '||object_name||''' from dual;'||chr(10)||
'select text from user_source where name = '''||object_name||
''' 
minus select text from user_source&inst_1 where name = '''||object_name||'''
;'
from user_objects
where object_type in ('PROCEDURE','PACKAGE BODY','FUNCTION')
and object_name not like 'SQLPROG%'
and object_name not like 'PK_CNV%'
and object_name not like 'P_CNV%'
and object_name not like 'F_CNV%'
order by object_type, object_name
;

spool off
set doc off
set feedback on
set pagesize 30
set termout on
set echo on
start &&out_file
-- exit