Reviewing Oracle-supplied packages

Oracle-supplied packages exist as prebuilt programs in the database as wrapper code. These packages not only help database developers work on extended functionalities but also reduce writing extensive and complex code. The use of the Oracle-supplied API is always recommended as it improves code standardization.

The scripts for these packages are available in the $ORACLE_HOME\RDBMS\ADMIN\ folder. All packages reside on the database server. Public synonyms are available (or can be created too) for these packages so that the packages are accessible to the database users. Oracle 12c adds multiple packages to the Oracle-supplied PL/SQL package list. The latest additions provide PL/SQL interfaces for the new functionalities that have been added to the database release.

Some of the important Oracle-supplied packages are listed as follows:

• DBMS_ALERT: This package is used for the notification of database events.

• DBMS_LOCK: This package is used for managing lock operations (lock, conversion, and release) in PL/SQL applications.

• DBMS_SESSION: This package is used to set session level preferences from PL/SQL programs (similar to ALTER SESSION).

• DBMS_OUTPUT: This package is one of the most frequently used built-ins for buffering data messages and displaying debug information.

• DBMS_HTTP: This package is used for HTTP callouts.

• UTL_FILE: This package is used for reading, writing, and performing other file operations on the server.

• UTL_MAIL: This package is used to compose and send mails.

• DBMS_SCHEDULER: This package is used for scheduling execution of stored procedures at a given time.

• DBMS_PARALLEL_EXECUTE: This package can be used to execute a user-defined task in parallel. If the PL/SQL block is running a large update on a table, the package can be used to enable the parallel execution of the task by splitting it into chunks.

• DBMS_PRIVILEGE_CAPTURE: This package is introduced in Oracle Database 12c to set a policy in order to capture the usage of privileges (object and system) in respect to users. It helps in controlling excess privileges for users.

• DBMS_REDACT: This package is introduced in Oracle Database 12c to create redaction policies, in order to mask data based on user authorization.

• DBMS_RESOURCE_MANAGER: This package is used to create consumer groups, directives, and resource manager plans for containers as well as a pluggable database. The resource manager plan determines the resources allocated to a pluggable database, a schema, or a task.

• DBMS_DATAPUMP: This package is used to move data, metadata, or both from one database to another. The source and target databases can be on different platforms.

• DBMS_PDB: This package is introduced in Oracle Database 12c to generate or analyze the integration properties of a pluggable database for unplug/plug operations.

• DBMS_SQL: This package enables dynamic SQL in PL/SQL. You can run DML or DDL statements using DBMS_SQL from a PL/SQL block.

• DBMS_REDEFINITION: This package is used to perform online redefinition tasks for tables.

• DBMS_UTILITY: This package is used to accomplish many utility operations such as analyze object, compile schema, get dependency, resolve a given name, validate database objects, format call and error stack, expand SQL text, and retrieve current database version.

Based on the objective to be achieved, the packages can be categorized as follows:

• Standard application development: Many of the DBMS packages provide an application interface for database features. For example, DBMS_REDACT provides an interface to create and manage redaction policies. Similarly, DBMS_UTILITY provides subprograms to retrieve instance or database information, call stacks and error stacks, and analyze and validate objects. The DBMS_OUTPUT package is one of the packages most frequently used to display text messages. It can be efficiently used for tracing and debugging purposes. Accessing and writing operating system files was made possible through UTL_FILE.

• General usage and application administration: Oracle has many packages for monitoring applications and users. Statistics generation, load history, and space management are the key objectives accomplished by these packages. DBMS_UTILITY comprises subprograms for general usage.

• Internal support packages: Oracle maintains these packages for its own use.

• Transaction processing packages: Oracle provides utility packages that enable the monitoring of transaction stages. Though they are rarely used, they can efficiently ensure transparent and smooth transactions. For example, DBMS_TRANSACTION is used to access SQL transactions from stored subprograms. DBMS_PARALLEL_EXECUTE executes a large update in parallel by splitting it into small tasks.