Compiling Procedures, Functions and Packages during Application Development in Oracle10g

Oracle compiles procedures, functions and packages at the time of creation. Sometimes these procedural objects may become invalid if the database objects that they refer undergo changes.

In that case run time compiling occurs when the procedural objects are executed. This may cause performance degradation. To avoid this, explicit recompilation of procedures, functions and packages must be done.

To compile the procedure the alter procedure command can be used.
Example: alter procedure EMP_PROCESS compile;

Similarly to compile a function the alter function command can be used.
Example: alter function EMP_DETAILS compile;

When packages are recompiled both the package specification and body or just the package body can be recompiled. If only the source code for procedures or functions are changed within the package, only the package body can be recompiled. However usually, it is appropriate to recompile both specification and body which is also the default case.

Example: alter package EMP_PAYROLL compile;
Since the option of Package or Body or specification is not given explicitly, the default of compiling full PACKAGE is used.

To do any of the above recompilation the object must be owned by user or the alter system privilege must be given to the user.

An alternative approach is to use the DBMS_DDL package to perform recompilations of invalid objects.

Example: EXEC DBMS_DDL.alter_compile (‘PROCEDURE’,’MY_SCHEMA’,’EMP_PROC’);
This method is applicable only for PL/SQL objects.

For a small number of objects, the above manual recompilation methods hold good. If there are a large number of objects to be recompiled a custom script can be coded to identify and compile the invalid objects using dynamic SQL commands within a loop.

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package can be used to compile in one shot all procedures, functions, packages and triggers within the schema that is specified.

The UTL_RECOMP package contains two procedures that are used to compile invalid objects in a schema, one in serial mode and the other in parallel ode. There names are RECOMP_SERIAL and RECOMP PARALLEL.

Another utility provided by Oracle are the utlrp.sql and utlprp.sql scripts to recompile invalid objects. These are usually run after major changes in a database.

The Procedures, functions and packages can also be replaced by the create or replace command. The “or replace” clause that is added is used to make existing grants continue to hold well. Dropping and re-creation of objects will cause the need for EXECUTE privileges that were existing earlier to be regranted.

| About Sequences and their attributes in Oracle10g | Accessing Remote data with Database Links in Oracle10g | A Guide to Iterative Processing with Loops in PL/SQL | A note on Dynamic SQL and its implementation in PL/SQL Application | Autonomous Transactions in Oracle – How to create and use if efficiently | Backup and Recovery – Best Practices in Oracle10g | Compiling Procedures, Functions and Packages during Application Development in Oracle10g | Introducing Publish-Subscribe Model in Oracle10g | Exploring Debugging Procedures in Oracle10g | External Procedures and their uses in Oracle10g | Guidelines for Locking Data in Oracle10g | How to customize an oracle10g Database using Database triggers | Specifying Constraints while creating table in Oracle10g to enhance Data Integrity |