OceanBase Database supports PL system packages to extend database functionality and provide access to SQL functions from PL.
The system packages are provided to use when you create an application or your own stored procedure. This manual covers the system packages supported by OceanBase Database.
System packages are encapsulated sets of related procedural objects stored in a database. Procedural objects include stored procedures, functions, variables, constants, cursors, and exceptions.
After you have the required privileges, run the following statement in the oceanbase schema to query the system packages that are supported and their related information:
SELECT * FROM __all_package;
Composition of the PL system packages
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.
PL system package overview
The following table lists the PL system packages and their brief descriptions supported by the current OceanBase Database version. | System Package | Description | |-------------------------------------------------|------------------------------------| | DBMS_RESOURCE_MANAGER| Used to maintain 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. |