OceanBase Database supports PL system packages to extend database functionality and provide access to SQL features through PL.
You can use the provided system packages when creating applications or your own stored procedures. This manual describes the system packages supported by the current version of OceanBase Database.
System packages are collections of related program objects stored in the database. Program objects include stored procedures, functions, variables, constants, cursors, and exceptions.
After you have the necessary privileges, run the following statement to query the system packages supported by the current version of OceanBase Database and their related information:
SELECT * FROM DBA_SOURCE;
Run the following statement to query the system packages supported by the current version of OceanBase Database and their related information:
SELECT * FROM DBA_SOURCE WHERE NAME ='pl_package_name';
Structure of PL system packages
PL system packages consist of two parts: the package specification (SPECIFICATION) and the package body (BODY). The package specification serves as the application interface, containing public declarations visible to the application. These declarations define the types, variables, constants, exceptions, cursors, and subprograms available for use. The package body, on the other hand, contains the implementation details and private declarations hidden from the application. It fully defines the cursors and subprograms to support the implementation of the package specification.
Note
OceanBase Database does not support user modifications to system packages in the current version.
Using PL system packages
When you create a database, OceanBase Database automatically installs the system packages it provides.
Overview of PL system packages
The following table lists the PL system packages supported by OceanBase Database in the current version and provides a brief description of each.
| System package | Description |
|---|---|
| DBMS_APPLICATION_INFO | Registers the application name in the database for auditing or performance tracking. |
| DBMS_AUDIT_MGMT | Manages audit logs. |
| DBMS_CRYPTO | Encrypts and decrypts stored data, can be used with running PL programs, and supports encryption and hashing algorithms. |
| DBMS_DEBUG | Debugs PL programs. |
| DBMS_DESCRIBE | Retrieves information about PL objects. |
| DBMS_JOB | Schedules and manages jobs in the job queue.NoteIn OceanBase Database V4.x, the |
| DBMS_LOB | Provides general procedures for working with large objects (LOB) data types BLOB and CLOB (read/write). |
| DBMS_LOCK | Requests, converts, and releases locks through the Lock Management service. |
| DBMS_METADATA | Allows the caller to easily retrieve complete database object definitions (metadata) from the dictionary. |
| DBMS_MVIEW | Allows the caller to understand the features of materialized views and potential materialized views, including their query rewrite availability. You can also refresh materialized views that do not belong to the same refresh group and clear logs. |
| DBMS_MVIEW_STAT | Allows the caller to manage the collection and retention of statistics for materialized view refresh operations. |
| DBMS_MONITOR | Allows the caller to use PL/SQL to control the behavior of full-link tracing. |
| DBMS_OUTPUT | Used for input and output information. |
| DBMS_PLAN_CACHE | Removes the specified SQL statement from the plan cache. |
| DBMS_PLSQL_CODE_COVERAGE | Collects code coverage for PL/SQL code during runtime. |
| DBMS_RANDOM | Provides a built-in random number generator. |
| DBMS_RESOURCE_MANAGER | Manages resource usage plans, resource usage groups, and resource plan directives. |
| DBMS_SCHEDULER | Manages scheduled jobs. |
| DBMS_SESSION | Provides stored procedures for accessing the SQL ALTER SESSION statement and other session information. |
| DBMS_SQL | Accesses the database using dynamic SQL. |
| DBMS_STATS | Views and modifies the statistics collected by the optimizer for database objects. |
| DBMS_UDR | Provides rewrite binding functionality, which allows you to rewrite SQL statements received by the database before the SQL statements start execution based on matching rules. |
| DBMS_UTILITY | Provides various utility subprograms. |
| DBMS_WORKLOAD_REPOSITORY | Manages the Automatic Workload Repository (AWR). |
| DBMS_XA | Contains XA/Open interfaces that can be called by applications in PL/SQL. |
| DBMS_XMLGEN | Converts the results of an SQL query to a standard XML format and returns the results as a CLOB. |
| DBMS_XPLAN | Provides functionality related to logical plan management, including logical plan optimization tracing. |
| DBMS_DDL | Used to encrypt PL/SQL object definition strings. |
| ODCIConst | Defines constants applicable to PL/SQL. |
| UTL_ENCODE | Provides functionality to encode RAW data in a standard format for data transmission between hosts. |
| UTL_FILE | Allows PL/SQL programs to read and write text files on the operating system and provides a restricted version of standard operating system stream file I/O. |
| UTL_I18N | Provides a set of services (globalization services) for applications written in PL/SQL. |
| UTL_RECOMP | Recompiles objects in the entire database or in a specific schema. |
| UTL_RAW | Provides SQL functions for processing RAW data types. |
| ANYDATA TYPE | Contains an instance of a given type and a description of that type. |
| XMLType | Used to process XML data. |
| DBMS_EXTERNAL_TABLE | Provides a programmatic interface for managing and operating external tables. |
