Home » RDBMS Server » Server Utilities » Script for compiling all packages
Script for compiling all packages [message #69035] Thu, 18 October 2001 02:27 Go to next message
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.

----------------------------------------------------------------------
Re: Script for compiling all packages [message #69040 is a reply to message #69035] Thu, 18 October 2001 03:36 Go to previous messageGo to next message
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'".

----------------------------------------------------------------------
Re: Script for compiling all packages [message #69042 is a reply to message #69035] Thu, 18 October 2001 05:55 Go to previous messageGo to next message
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

----------------------------------------------------------------------
Re: Script for compiling all packages [message #69061 is a reply to message #69035] Tue, 23 October 2001 05:14 Go to previous messageGo to next message
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'), ...

----------------------------------------------------------------------
Re: Script for compiling all packages [message #69070 is a reply to message #69035] Tue, 23 October 2001 12:02 Go to previous message
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.

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


Current Time: Thu Mar 28 06:14:32 CDT 2024