You can use the ALTER PACKAGE statement to explicitly recompile the specification and/or body of a program package.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
All objects in a package are stored as a unit. Therefore, the ALTER PACKAGE statement will recompile all the objects in the package. You cannot use the ALTER PROCEDURE or ALTER FUNCTION statement to independently recompile a stored procedure or function in a program package.
Prerequisites
If the program package is in the schema of the SYS user, you must connect to the database with the SYSDBA role. Otherwise, the program package must be located in your own schema, or you have the ALTER ANY PROCEDURE system privilege.
Syntax
Syntax of alter_package:
ALTER PACKAGE [ schema. ] package_name
{ package_compile_clause} ;
Syntax of package_compile_clause:
COMPILE [ DEBUG ] [ compiler_parameters_clause ... ] [ REUSE SETTINGS ]
Semantics
| Syntax | Keyword or syntax node | Description |
|---|---|---|
| alter_package | schema | The name of the schema where the program package is located. The default value is your schema. |
| alter_package | package_name | The name of the program package to be recompiled. |
| package_compile_clause | -- | Recompiles the specification and/or body of the program package. |
Examples
Explicitly recompile the specification and body of the sys.obdemo_pack package.
ALTER PACKAGE obdemo_pack COMPILE PACKAGE;
If the database does not encounter an error when it recompiles the specification and body of the obdemo_pack package, the obdemo_pack package becomes valid. Then, the SYS user can call or reference all objects declared in the specification of the obdemo_pack package, without the need to recompile them during running.
If the database encounters an error when recompiling the obdemo_pack package, the database returns an error and invalidates obdemo_pack. The database also invalidates all objects that depend on the obdemo_pack package. If these objects are referenced later without being explicitly recompiled, the database will implicitly recompile the objects during running.
The following example recompiles the package body in the schema of the SYS user.
ALTER PACKAGE sys.obdemo_pack COMPILE BODY;
If the database does not encounter an error when it recompiles the body of the obdemo_pack package, the package body becomes valid. Then, the SYS user can call or reference all objects declared in the specification of the obdemo_pack package, without the need to recompile them during running.
If the database encounters an error when it recompiles the package body, the database returns an error and the package body is still invalid.
This statement recompiles the body rather than the specification of the obdemo_pack package. Therefore, the database does not invalidate the dependent objects.