-
How do i compile all procedures in a database using sql plus. I want to compile packages, functions and procedures
-
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
-
Procedure Compile
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
(OCP)
-
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|