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.
Similarly to compile a function the alter function command can be used.
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;
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’);
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.
| 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 |
“Amazon and the Amazon logo are trademarks of Amazon.com, Inc. or its affiliates.”Copyright - © 2004 - 2019 - All Rights Reserved.