The ALTER PACKAGE statement is used to explicitly recompile the package header and (or) body.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL-compatible mode.
Because all objects in a package are stored as a single unit, the ALTER PACKAGE statement will recompile all package objects. You cannot use the ALTER PROCEDURE or ALTER FUNCTION statement to recompile a part of the package, such as a stored procedure or function.
Prerequisites
If the package is in the SYS schema, you must connect as SYSDBA. Otherwise, the package must be in your current schema, or you must have the ALTER ANY PROCEDURE system privilege.
Syntax
The syntax of alter_package is as follows:
ALTER PACKAGE [ schema. ] package_name
{ package_compile_clause} ;
where package_compile_clause is specified as follows:
COMPILE [ DEBUG ] [ compiler_parameters_clause ... ] [ REUSE SETTINGS ]
Semantics
| Syntax | Keywords or syntax nodes | Description |
|---|---|---|
| alter_package | schema | The name of the schema where the package is located. The default value is the current user's schema. |
| alter_package | package_name | The name of the package to be recompiled. |
| package_compile_clause | -- | Recompiles the package header and (or) body. |
Examples
The following example explicitly recompiles the specification and body of the sys.obdemo_pack package.
ALTER PACKAGE obdemo_pack COMPILE PACKAGE;
If the database does not encounter any compilation errors during the recompilation of the obdemo_pack specification and body, then obdemo_pack becomes valid. Subsequently, user sys can call or reference all package objects declared in the obdemo_pack specification without needing to recompile them at runtime.
If the recompilation of obdemo_pack results in compilation errors, the database returns an error message, and obdemo_pack remains invalid. The database also invalidates all objects that depend on obdemo_pack. If these objects are subsequently referenced without being explicitly recompiled, the database will implicitly recompile them at runtime.
The following example recompiles the body of the obdemo_pack package in the SYS schema:
ALTER PACKAGE sys.obdemo_pack COMPILE BODY;
If the database does not encounter any compilation errors during the recompilation of the obdemo_pack body, then the body becomes valid. Subsequently, user sys can call or reference all package objects declared in the obdemo_pack specification without needing to recompile them at runtime.
If the recompilation of the body results in compilation errors, the database returns an error message, and the body remains invalid.
Since this statement recompiles the body of the package rather than the specification, the database does not invalidate dependent objects.