OraFAQ Forum: Server Utilities » Script for compiling all packages

Home » RDBMS Server » Server Utilities » Script for compiling all packages

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Script for compiling all packages [message #69035]

Thu, 18 October 2001 02:27

Phil Quinn
Messages: 1
Registered: October 2001

Junior Member

I want to re-compile all package and procedures.

Is there a script that does each of these in turn, ie. packages, then functions, etc.

----------------------------------------------------------------------

Report message to a moderator

Re: Script for compiling all packages [message #69040 is a reply to message #69035]

Thu, 18 October 2001 03:36

Luis Varon
Messages: 6
Registered: September 2000

Junior Member

Try this

set heading off
spool file
select 'ALTER ' || object_type || ' ' || object_name || ' compile;' from user_objects where object_type in ('PACKAGE','PROCEDURE');

spool off
set heading on

if you want to compile only objects which are invalid you can add this "and status = 'INVALID'".

----------------------------------------------------------------------

Report message to a moderator

Re: Script for compiling all packages [message #69042 is a reply to message #69035]

Thu, 18 October 2001 05:55

Suresh Vemulapalli
Messages: 624
Registered: August 2000

Senior Member

just addition to luis script:

set heading off
set feedback off
set term off
spool s.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
('PACKAGE','PACKAGE BODY','VIEW','PROCEDURE','TRIGGER','FUNCTION')
AND status='INVALID';
spool off
set term on
set heading on
set feedback on
@s.sql

----------------------------------------------------------------------

Report message to a moderator

Re: Script for compiling all packages [message #69061 is a reply to message #69035]

Tue, 23 October 2001 05:14

regis
Messages: 2
Registered: October 2001

Junior Member

Not convinced by these scripts. Dependance between package can impact final status...
There are package in dbms_utility that can help you : dbms_utility.compile_schema('X'), ...

----------------------------------------------------------------------

Report message to a moderator

Re: Script for compiling all packages [message #69070 is a reply to message #69035]

Tue, 23 October 2001 12:02

Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida

Senior Member

I also have a similar script to compile all packages and procedure, and Regis is right, sometimes there are dependencies.

So what I do is run the same script 3-4 times in a series. By the last run, it would have compiled all objects, including dependent ones.

----------------------------------------------------------------------

Report message to a moderator

Previous Topic:	How to let sys user to have access right to another user's resource?
Next Topic:	Exporting delimited data

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 11 23:07:21 CDT 2025