You can use the ALTER PROCEDURE statement to explicitly recompile an independent stored procedure.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
To recompile a stored procedure that is part of a program package, use the ALTER PACKAGE statement.
Prerequisites
If the stored procedure to be recompiled is in the schema of the SYS user, you must connect to the database with the SYSDBA role. Otherwise, the stored procedure must be located in your own schema, or you must have the ALTER ANY PROCEDURE system privilege.
Syntax
The syntax of alter_procedure is as follows:
ALTER PROCEDURE [ schema. ] procedure_name
{ procedure_compile_clause} ;
The syntax of procedure_compile_clause is as follows:
COMPILE [ DEBUG ] [ compiler_parameters_clause ... ] [ REUSE SETTINGS ]
Semantics
| Syntax | Keyword or syntax node | Description |
|---|---|---|
| alter_procedure | schema | The name of the schema where the stored procedure is located. The default value is your schema. |
| procedure_name | The name of the stored procedure to be recompiled. | |
| procedure_compile_clause | - | Recompiles the stored procedure. |
Examples
The following example explicitly recompiles the userlogin stored procedure of the SYS user:
ALTER PROCEDURE sys.userlogin COMPILE;
If the database does not encounter errors when it recompiles the userlogin stored procedure, userlogin becomes valid. The database can directly run this function later without the need to recompile it.
If the database encounters an error when it recompiles userlogin, it returns an error message and invalidates userlogin.
The database will also invalidate all objects that depend on the userlogin stored procedure, including any stored procedure, function, and package body that calls userlogin. If these objects are referenced later without being explicitly recompiled, the database will implicitly recompile the objects during running.