OceanBase Database supports the PL system package to extend database functionality and provide PL access to SQL features.
You can use the provided system packages when creating applications or your own stored procedures. This topic describes the system packages supported by the current version of the OceanBase Database server.
A system package is a encapsulated collection of related program objects stored in a database. Program objects include stored procedures, functions, variables, constants, cursors, and exceptions.
After obtaining the necessary privileges, run the following statement in the oceanbase schema to query the currently supported system packages and their related information:
SELECT * FROM __all_package;
Components of the PL system package
PL packages consist of two parts: the specification (SPECIFICATION) and the body (BODY). The specification is the application's interface and contains public declarations visible to the application, which declare types, variables, constants, exceptions, cursors, and subprograms that can be used. The body contains the implementation details and private declarations hidden from the application, fully defining cursors and subprograms to support the specification.
Purpose
Users are not allowed to modify system packages in OceanBase Database of this version.
Usage of PL packages
When you create a database, the system packages provided by OceanBase Database are automatically installed.
Overview of PL system packages
The following table lists the PL system packages and their brief descriptions supported by the current OceanBase Database version.
System Package |
Description |
|---|---|
| DBMS_APPLICATION_INFO | Registers and monitors application names, modules, operations, and client information. |
| DBMS_RESOURCE_MANAGER | Maintains resource usage plans, resource groups, and resource plan directives. |
| DBMS_DAILY_MAINTENANCE | Manages and maintains window merging. |
| DBMS_STATS | View and modify the statistics that the optimizer collects for database objects. |
| DBMS_UDR | DBMS_UDR provides rewrite binding capabilities. Rewriting of database-received SQL queries can be performed based on matching rules before SQL execution starts. |
| DBMS_XPLAN | It provides logical plan management features, including optimization tracking. |
| DBMS_WORKLOAD_REPOSITORY | Manages Automatic Workload Repository (AWR) |
| DBMS_MVIEW | The DBMS_MVIEW package allows the invoker to understand the functionality of materialized views and potential materialized views, including query rewriting availability. It also supports refreshing materialized views that are not part of the same refresh group and clearing logs. |
| DBMS_MVIEW_STAT | The DBMS_MVIEW_STAT package provides subprograms to manage the collection and retention of statistics on materialized view refresh operations. |
| DBMS_TRUSTED_CERTIFICATE_MANAGER | Allows the caller to add, delete, and modify CA root certificates trusted by the OBServer cluster to be used for RPC security authentication. |
| DBMS_OB_LIMIT_CALCULATOR | The DBMS_OB_LIMIT_CALCULATOR module is used to obtain the current resource usage of the cluster based on which to plan operations such as scale-out and scale-in, node replacement, and creation of standby tenants. |
| DBMS_EXTERNAL_TABLE | Provides a set of procedures to manage and operate external tables. |
| DBMS_PARTITION | Provides a dynamic partition management interface. |
| DBMS_JAVA | This package allows you to integrate and manage Java programs in the database, supporting Java stored procedures, functions, and security strategies. |
| DBMS_PYTHON | This package lets you integrate and manage Python programs in a database and implements Python stored procedures, functions, and security policies. |
| DBMS_DATA_DICT | Controls the scheduling of data dictionary updates. |
| DBMS_DATA_DICT | It is used to control the scheduling of data dictionaries. |