compile procedures

[programmer]

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

[mooks]

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;

[PSoni]

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

[Pinakin]

Hey,

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

[sambavan]

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

Sam