Command for executing all packages / functions

[Guest_imported]

Is there a command for executing all packages / functions?

 

[rcurva]

none, if i understood you correctly. by the way, why do you need to run them all at the same time?

you can make a script to run all of them, but the result will not be very acceptable, i tell you.

 

[Guest_imported]

It's a new database (a copy ofd a live one) and I need to re-compile all the functions and packages.

Thanks for the advice

 

[Guest_imported]

As far as I understand there is a package that compiles all packages in the correct order .... ?!?

 

[Kenrae]

If you want to compile (not execute) all your code, then try this:

SELECT 'alter '||object_type||' '||object_name||' compile;'
FROM user_objects
WHERE object_type='PROCEDURE' or object_type='FUNCTION'
UNION
SELECT 'alter package '||object_name||' compile;'
FROM user_objects
WHERE object_type='PACKAGE BODY';

Its output will be the script you need. Then you can copy&paste it, or you can spool it to a file using sqlplus.

Executing the script repeated times will compile everything (if there are no errors, of course).

 

[rcurva]

How did you migrate by the way? If you export/import by schema user then there is no need for the recompilation of the procs.

To recompile manually, you can create a script that runs and create another script. For example;

--------------------------------------------------------
SPOOL script2
SELECT 'ALTER '||object_type||' '||object_name||' COMPILE;'
FROM DBA_OBJECTS
WHERE OBJECT_TYPE LIKE ('PROCEDURE','FUNCTION','TRIGGER','PACKAGE')
AND STATUS = 'INVALID';

SPOOL OFF

@script2

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

 

[rcurva]

You can also check out this site:

http://www.angelfire.com/vi/rcurva/db/compile_obj.txt

 

[carp]

You might try the DBMS_UTILITY.COMPILE_SCHEMA procedure that Oracle supplies.
A quick piece of PL/SQL ought to do the trick for your entire database:

DECLARE
CURSOR user_cursor IS SELECT DISTINCT owner
FROM dba_objects
WHERE status = 'INVALID';
BEGIN
FOR i IN user_cursor LOOP
DBMS_UTILITY.COMPILE_SCHEMA(i.owner);
END LOOP;
END;
/