utlrp.sql recompilation for Oracle 7.3 ?

[rogerF]

Oracle People:

I've had great luck re-compiling packages/procedures after import export with Oracle8i "utlrp.sql" which basically re-complies everything after you receive errors during and import:

MP-00041 Warning: object created with compilation warnings

Now that I can do this ... I've had the same problem in Oracle 7.3 but that version doesn't have utlrp.sql and it won't run in 7.3. Does 7.3 have such a script or ...How do I get out of this one ?

Thanks,

Roger

[sreddy]

Try recompiling all the views first and then recompile packages. Most of the times its dependency of the view/any objects that are invalid for that package to be compiled. This script creates 3 spool files for

sys owned objects
system owned objects
non-system objects

run whatever objects want to compile...

--------------------------------cut here------------------------------
set echo off
set heading off
set feedback off
set linesize 80
set pagesize 0
set newpage 0
set pagesize 0

spool nsys_objrecomp.sql

select 'alter package '||owner||'.'||object_name||' compile package;'
from sys.dba_objects where status='INVALID' and object_type='PACKAGE'
and owner <> 'SYS';

select 'alter package '||owner||'.'||object_name||' compile body; ' from
sys.dba_objects where status = 'INVALID' and object_type='PACKAGE BODY'
and owner <> 'SYS';

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from
sys.dba_objects where status = 'INVALID' and object_type in('PROCEDURE', 'VIEW','FUNCTION','TRIGGER')
and owner <>'SYS';

spool off

spool sys_objrecomp.sql

select 'alter package '||owner||'.'||object_name||' compile package;'
from sys.dba_objects where status='INVALID' and object_type='PACKAGE'
and owner='SYS';

select 'alter package '||owner||'.'||object_name||' compile body; ' from
sys.dba_objects where status = 'INVALID' and object_type='PACKAGE BODY' and
owner = 'SYS';

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from
sys.dba_objects where status = 'INVALID' and object_type in('PROCEDURE', 'VIEW','FUNCTION','TRIGGER')
and owner = 'SYS';

spool off

spool syst_objrecomp.sql

select 'alter package '||owner||'.'||object_name||' compile package;'
from sys.dba_objects where status='INVALID' and object_type='PACKAGE'
and owner='SYSTEM';

select 'alter package '||owner||'.'||object_name||' compile body; ' from
sys.dba_objects where status = 'INVALID' and object_type='PACKAGE BODY' and
owner = 'SYSTEM';

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from
sys.dba_objects where status = 'INVALID' and object_type in('PROCEDURE', 'VIEW','FUNCTION','TRIGGER')
and owner = 'SYSTEM';

spool off

set echo on
set feedback on
set lines 80
set pages 50

--------------------------------cut here------------------------------


[Edited by sreddy on 12-27-2000 at 11:53 AM]

[rogerF]

Thanks !

I just got back from New years break and will apply your script today.

-Roger