Procedure

[hummy]

How do i compile all procedures in a database using sql plus. I want to compile packages, functions and procedures

[markmd]

try this :

set heading off
set pagesize 0
set feedback off

spool xyz.sql

select 'alter package '||object_name||' compile body;'
from user_objects
where object_type = 'PACKAGE BODY'
union
select 'alter package '||object_name||' compile;'
from user_objects
where object_type = 'PACKAGE'
union
select 'alter procedure '||object_name||' compile;'
from user_objects
where object_type = 'PROCEDURE'
union
select 'alter function '||object_name||' compile;'
from user_objects
where object_type = 'FUNCTION';

spool off

@xyz.sql

[gopi123_r]

set feedback off
set heading off
set pagesize 0

spool comp.sql
select 'alter '||decode(object_type,'PACKAGE BODY',
'PACKAGE',object_type)||' '||object_name||
decode(object_type,'PACKAGE BODY',
' compile body;', ' compile;')
from user_objects
where object_type in
('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY')
and status='INVALID';

spool off

@comp.sql

[kris109]

You can compile all objects that are invalid with this script:

set heading off;
set feedback on;
set pages 10000;

spool &&spoolfile

select 'alter '||object_type, object_name
||' compile;'
from user_objects
where status = 'INVALID'
and object_type != 'PACKAGE BODY'
union
select 'alter package '||NULL, object_name
||' compile body;'
from user_objects
where status = 'INVALID'
and object_type = 'PACKAGE BODY';

spool off

@&&spoolfile

host del &&spoolfile

undefine spoolfile;