Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)

B28370-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

ALTER PACKAGE Statement

The ALTER PACKAGE statement explicitly recompiles a package specification, body, or both. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.

Because all objects in a package are stored as a unit, the ALTER PACKAGE statement recompiles all package objects together. You cannot use the ALTER PROCEDURE statement or ALTER FUNCTION statement to recompile individually a procedure or function that is part of a package.

Note:

This statement does not change the declaration or definition of an existing package. To redeclare or redefine a package, use the CREATE PACKAGE Statement, or the CREATE PACKAGE BODY Statement with the OR REPLACE clause.

Prerequisites

For you to modify a package, the package must be in your own schema or you must have ALTER ANY PROCEDURE system privilege.

Syntax

alter_package::=

alter_package
Description of the illustration alter_package.gif

compiler_parameters_clause::=

compiler_parameters_clause
Description of the illustration compiler_parameters_clause.gif

Keyword and Parameter Descriptions

schema

Specify the schema containing the package. If you omit schema, then the database assumes the package is in your own schema.

package

Specify the name of the package to be recompiled.

COMPILE

You must specify COMPILE to recompile the package specification or body. The COMPILE keyword is required.

During recompilation, the database drops all persistent compiler switch settings, retrieves them again from the session, and stores them at the end of compilation. To avoid this process, specify the REUSE SETTINGS clause.

If recompiling the package results in compilation errors, then the database returns an error and the body remains invalid. You can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS.

SPECIFICATION

Specify SPECIFICATION to recompile only the package specification, regardless of whether it is invalid. You might want to recompile a package specification to check for compilation errors after modifying the specification.

When you recompile a package specification, the database invalidates any local objects that depend on the specification, such as procedures that call procedures or functions in the package. The body of a package also depends on its specification. If you subsequently reference one of these dependent objects without first explicitly recompiling it, then the database recompiles it implicitly at run time.

BODY

Specify BODY to recompile only the package body regardless of whether it is invalid. You might want to recompile a package body after modifying it. Recompiling a package body does not invalidate objects that depend upon the package specification.

When you recompile a package body, the database first recompiles the objects on which the body depends, if any of those objects are invalid. If the database recompiles the body successfully, then the body becomes valid.

PACKAGE

Specify PACKAGE to recompile both the package specification and the package body if one exists, regardless of whether they are invalid. This is the default. The recompilation of the package specification and body lead to the invalidation and recompilation of dependent objects as described for SPECIFICATION and BODY.

DEBUG

Specify DEBUG to instruct the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger. Specifying this clause has the same effect as specifying PLSQL_DEBUG = TRUE in the compiler_parameters_clause.

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about debugging packages

compiler_parameters_clause

This clause has the same behavior for a package as it does for a function. See the ALTER FUNCTION compiler_parameters_clause.

REUSE SETTINGS

This clause has the same behavior for a package as it does for a function. See the ALTER FUNCTION clause REUSE SETTINGS.

Examples

Recompiling a Package: Examples This statement explicitly recompiles the specification and body of the hr.emp_mgmt package. See Creating a Package: Example for the example that creates this package.

ALTER PACKAGE emp_mgmt
   COMPILE PACKAGE; 

If the database encounters no compilation errors while recompiling the emp_mgmt specification and body, then emp_mgmt becomes valid. The user hr can subsequently call or reference all package objects declared in the specification of emp_mgmt without run-time recompilation. If recompiling emp_mgmt results in compilation errors, then the database returns an error and emp_mgmt remains invalid.

the database also invalidates all objects that depend upon emp_mgmt. If you subsequently reference one of these objects without explicitly recompiling it first, then the database recompiles it implicitly at run time.

To recompile the body of the emp_mgmt package in the schema hr, issue the following statement:

ALTER PACKAGE hr.emp_mgmt 
   COMPILE BODY; 

If the database encounters no compilation errors while recompiling the package body, then the body becomes valid. The user hr can subsequently call or reference all package objects declared in the specification of emp_mgmt without run-time recompilation. If recompiling the body results in compilation errors, then the database returns an error message and the body remains invalid.

Because this statement recompiles the body and not the specification of emp_mgmt, the database does not invalidate dependent objects.

Related Topics