Aside from the STANDARD package that gives the Oracle Server its basic functionality, such as your basic subtype declarations and data type conversion routines, there is a set of packages intended for use by DBAs and developers. These packages are distinguished by their names, which begin with DBMS_ or UTL_, meaning they interact with the database or provide general- purpose utilities. First, we'll take a quick look at these packages and afterwards examine them more closely.
Most of the Oracle-supplied packages work on data within the server environment: data dictionary items, user database objects, or objects found solely within the System Global Area, like the shared memory pool. With them, you can manage snapshots, recompile program units, generate asynchronous alerts when database entries change, run jobs, and so on.
Many of these packages interface with functionality built into the database application or
extends the database environment with calls to loadable external modules, such as DLLs. It's
not expected or necessary that you understand or use these module entry points. I'd go so
far as to say you're definitely not supposed to try to use them directly, but rather, through the
PL/SQL programmatic interface provided.
Some packages give feedback to the calling context or otherwise provide an interface to
an external process. The Oracle pipe feature, for example, is intended as a method
for intersession communication that is strictly memory onlyno database objects are used
for intermediate storage. As another example, the
DBMS_OUTPUT package allows the display of print statements when running PL/SQL programs in SQL*Plus.
Several packaged routines enable you to obtain additional tuning information about your database server, such as shared memory pool usage, segment space information, running traces, getting general database information, and so on. Once you get used to using them, they'll become a standard part of your toolbox.
Table 12.1 is a handy, quick reference of each Oracle-supplied package and a brief description of what it contains.
Page 273Table 12.1 Summary of Supplied Packages
Package Name | Package Header File | Description |
DBMS_ALERT | dbmsalrt.sql | Asynchronous handling of database events. |
DBMS_APPLICATION_INFO | dbmsutil.sql | Register the name of the application that's currently running (for performance monitoring). |
DBMS_DDL | dbmsutil.sql | Recompile stored subprograms and packages, analyze database objects. |
DBMS_DESCRIBE | dbmsdesc.sql | Describe parameters for a stored subprogram. |
DBMS_JOB | dbmsjob.sql | Run user-defined jobs at a specified time or interval. |
DBMS_LOCK | dbmslock.sql | Manage database locks. |
DBMS_OUTPUT | dbmsotpt.sql | Write text lines to a buffer for later retrieval and display. |
DBMS_PIPE | dbmspipe.sqlz | Send and receive data between sessions via a memory "pipe." |
DBMS_REFRESH | dbmssnap.sql | Manage groups of snapshots that can be refreshed together. |
DBMS_SESSION | dbmsutil.sql | Perform Alter Session statements programmatically. |
DBMS_SHARED_POOL | dbmspool.sql | View and manage the contents. |
DBMS_SNAPSHOT | dbmssnap.sql | Refresh, manage snapshots, and purge snapshot logs. |
DBMS_SPACE | dbmsutil.sql | Get segment space information. |
DBMS_SQL | dbmssql.sql | Perform dynamic SQL and PL/SQL. |
DBMS_SYSTEM | dbmsutil.sql | Turn on/off SQL trace for the given session. |
DBMS_TRANSACTION | dbmsutil.sql | Manage SQL transactions. |
continuesPage 274
Table 12.1 Continued
Package Name | Package Header File | Description |
DBMS_UTILITY | dbmsutil.sql | Various Utilities: For a given schema, recompile stored subprograms and packages, analyze database objects, format error and call stacks for display, display whether instance is running in parallel server mode, get the current time in 10ms increments, resolve the full name of a database object, convert a PL/SQL table to a comma-delimited string or vv., get a database version/operating system string. |
UTL_RAW | utlraw.sql | String functions for RAW data type. |
UTL_FILE# | utlfile.sql | Read/write ASCII-based operating system files. |
UTL_HTTP+ | utlhttp.sql | Get an HTML-formatted page from a given URL. |
DBMS_LOB+ | dbmslob.sql | Manage large objects. |
# Oracle7.3 and later
+ Oracle8.0 and later
Before you can use the routines contained in the Oracle-supplied packages, you should first check that they've been installed and are valid. The DBA can run the following query:
SELECT object_name, object_type, status FROM dba_objects WHERE owner='SYS' AND object_type LIKE `PACKAGE%' ORDER BY object_name, object_type; This should give you a list similar to the following: OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ------------ ------ DBMS_ALERT PACKAGE VALID DBMS_ALERT PACKAGE BODY VALID DBMS_APPLICATION_INFO PACKAGE VALID DBMS_APPLICATION_INFO PACKAGE BODY VALIDPage 275
DBMS_DDL PACKAGE VALID DBMS_DDL PACKAGE BODY VALID DBMS_DESCRIBE PACKAGE VALID DBMS_DESCRIBE PACKAGE BODY VALID DBMS_JOB PACKAGE VALID DBMS_JOB PACKAGE BODY VALID DBMS_LOCK PACKAGE VALID DBMS_LOCK PACKAGE BODY VALID DBMS_OUTPUT PACKAGE VALID DBMS_OUTPUT PACKAGE BODY VALID DBMS_PIPE PACKAGE VALID DBMS_PIPE PACKAGE BODY VALID DBMS_REFRESH PACKAGE VALID DBMS_REFRESH PACKAGE BODY VALID DBMS_SESSION PACKAGE VALID DBMS_SESSION PACKAGE BODY VALID DBMS_SHARED_POOL PACKAGE VALID DBMS_SHARED_POOL PACKAGE BODY VALID DBMS_SNAPSHOT PACKAGE VALID DBMS_SNAPSHOT PACKAGE BODY VALID DBMS_SPACE PACKAGE VALID DBMS_SPACE PACKAGE BODY VALID DBMS_SQL PACKAGE VALID DBMS_SQL PACKAGE BODY VALID DBMS_SYSTEM PACKAGE VALID DBMS_SYSTEM PACKAGE BODY VALID DBMS_TRANSACTION PACKAGE VALID DBMS_TRANSACTION PACKAGE BODY VALID DBMS_UTILITY PACKAGE VALID DBMS_UTILITY PACKAGE BODY VALID
There will be some other packages not shown here, but we won't be concerning ourselves with them. Most of these additional packages are not intended to be called by user applications, but rather are for internal use only.
The Oracle-supplied packages are located in <ORACLE_HOME>\RDBMS<version>\ADMIN where ORACLE_HOME is the path to the Oracle Home directory (you can check the system variable of the same name in UNIX or check the Registry entry under Windows 95/NT). version is the database version you are running. You can examine the package header files listed in Table 12.1 for each of these packages to see what routines and global variables are available.
You may also note the existence of files of the form
prvt*.sql and prvt*.plb (for example, prvtpipe.sql and
prvtpipe.plb). The former are the package bodies for the supplied
packages, in ASCII format. The latter are binary compiled versions of the package body. The
PLB files are recognizable by the PL/SQL engine and result in a valid and executable package
body when submitted to the Oracle Server. These represent the "release" form of the
package bodies.