Previous | Table of Contents | Next

Page 271

Chapter 12

Using Supplied Oracle Database
Packages

In this chapter

Page 272

About the Supplied Oracle Database Packages

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.

Interaction Within the Server

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.

Interaction Beyond the Server

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 only—no 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.

Getting More Information from Your Server

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.

Describing Supplied Packages

Table 12.1 is a handy, quick reference of each Oracle-supplied package and a brief description of what it contains.

Page 273

Table 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.
                                            continues
Page 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

Getting Started with the Oracle-Supplied Packages

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 VALID
Page 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.

Locating the DBMS Packages

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.

Previous | Table of Contents | Next