compile procedures

How to compile all procedures in a database. what are the priviledges do we need for that.
thanks

Hi,

The following sql will be able to list out the command to compile all the invalid procedures in your database. You will need to have alter procedure privileges.

Hopes that this helps :)


select decode( OBJECT_TYPE, 'PROCEDURE',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from dba_objects
where STATUS = 'INVALID'
and OBJECT_TYPE = 'PROCEDURE'
and owner = 'ORCL'
order by OWNER, OBJECT_TYPE, OBJECT_NAME;

Hopes also this will do

select 'Alter Procedure ' || OWNER||'.'||OBJECT_NAME || ' compile;'
from dba_objects
where STATUS = 'INVALID'
and OBJECT_TYPE = 'PROCEDURE'
and owner = 'ORCL'
order by OWNER, OBJECT_NAME;


Thanks
P Soni

Hey,

What about DBMS_UTILITY.Compile_Schema(<>) .... ????

Ask your DBA to run utlrcmp.sql or utlrp.sql from $ORACLE_HOME/rdbms/admin, which would recompile all the invalid procedures ....

Sam