Oracle Built-in Packages

Built-in Packages Covered in This Book

Oracle Corporation provides many built-in packages, in a variety of its products. You may find it hard to believe, but even this large book cannot document all of those packages. Oracle Developer/2000 contains a set of built-in packages, including DDE and TEXT_IO. Oracle WebServer offers its own built-in packages, from HTP to OWA_UTIL, for use in web-based development. This book does not discuss those packages. Instead, its focus is on the core database built-in packages, generally those with a DBMS_ or UTL_ prefix.

Within that context, this book discusses all of the built-in packages that you are likely to use. Although (as noted above) it does not offer descriptions for every single package ever created by Oracle Corporation and stored in the database, I would be very surprised if you ever needed to use one of the few packages not found in these pages. The rest of this section introduces you to the packages covered in this book; I have organized these packages into three general areas:

Application development packages

Used primarily by developers as they build applications.

Server management packages

Used mostly by database administrators to manage their database servers.

Distributed database packages

Used by database administrators and developers to manage data across a distributed enterprise.

It is, of course, quite possible and not uncommon for a DBA to use a package we have designated in this book as an “application developer package.” In addition, a single package may contain both administrative and development programs. These categories are not meant to restrict your use of the built-in packages; instead, we hope to give some structure to a long, complex list of functional areas so that you will be able to access the technology with more ease and minimal confusion.

The following sections briefly describe each of the packages found in the chapters of this book. Table 1.1 provides a quick summary. Some of these packages, or the programs within them, are available only under certain Oracle versions, as explained in the following chapters.

Table 1-1. Oracle Built-in Packages

Package	Description	Chapter
DBMS_AQ	Creates messages in, and consumes messages from, specific queues provided by the Oracle Advanced Queuing facility.	5
DBMS_AQADM	Performs Oracle Advanced Queueing administrative tasks such as creating and dropping queue tables and altering queues.	5
DBMS_APPLICATION_INFO	Monitors the execution of an application.	7
DBMS_ALERT	Broadcasts notification to multiple users that specific database events have occurred.	3
DBMS_DDL	Recompiles stored code, analyzes objects in a schema, and modifies referenceability of object identifiers in Oracle8.	10
DBMS_DEFER	Queues deferred remote procedure calls (RPCs).	17
DBMS_DEFER_QUERY	Provides access to parameters passed to deferred calls, primarily for diagnostic purposes.	17
DBMS_DEFER_SYS	Performs administrative tasks such as scheduling, executing, and deleting queued transactions.	17
DBMS_DESCRIBE	Gets information about the parameters of a stored program.	10
DBMS_JOB	Provides an interface to Oracle’s job scheduler (for noninteractive execution of PL/SQL programs).	13
DBMS_LOB	Acesses and manipulates large objects (LOBs) from within PL/SQL programs.	8
DBMS_LOCK	Helps you manage contention for resources used by complex multiuser applications.	4
DBMS_OFFLINE_OG	Instantiates sites (i.e., exports data from an existing master site and imports it into the new master site).	15
SNAPSHOT	Instantiates a new snapshot using an export of a master table.	14
DBMS_OUTPUT	Displays information to your screen.	6
DBMS_PIPE	Lets applications communicate with routines external to the database.	3
DBMS_RANDOM	Provides a random number generator.	10
DBMS_RECTIFIER_DIFF	Compares replicated tables at two master sites and synchronizes them if necessary.	15
DBMS_REFRESH	Administers snapshot groups at a snapshot site.	14
DBMS_REPCAT	Performs many advanced replication operations, including maintenance, snapshots, and conflict resolution.	14. 15. 16
DBMS_REPCAT_ADMIN	Creates administrator accounts for replication.	15
DBMS_REPCAT_AUTH	Grants and revokes “surrogate SYS” privileges for administrator accounts, and grants and revokes propagator accounts for Oracle8.	15
DBMS_REPUTIL	Enables and disables replication at the session level.	15
DBMS_ROWID	Works with ROWID formats, both extended (Oracle8 only) and restricted (traditional Oracle7).	9
DBMS_SESSION	Modifies and inspects session roles and settings, and manipulates session memory and package states.	11
DBMS_SHARED_POOL	Allows PL/SQL objects and SQL cursors to be pinned (kept) in the Oracle shared pool; used mainly for memory management.	12
DBMS_SNAPSHOT	Maintains snapshots and snapshot logs.	14
DBMS_SPACE	Analyzes space in tables, indexes, and clusters, and provides information about segment free list sizes.	12
DBMS_SQL	Executes dynamically constructed SQL statements and PL/SQL blocks of code.	2
DBMS_SYSTEM	Lets administrators set trace events in other users’ sessions (for debugging).	11
DBMS_TRANSACTION	Provides a programmatic interface to a number of transaction-oriented SQL statements.	4
DBMS_UTILITY	Performs miscellaneous operations such as freeing unused memory, calculating elapsed program time, etc.	10
UTL_FILE	Reads and writes information in server-side files.	6
UTL_RAW	Works with raw data, performing such operations as concatenation, byte translation, etc.	9
UTL_REF	Provides a PL/SQL interface to select and modify objects in an object table (Oracle8 only).	9

Part II

This part of the book collects together packages that are used predominantly by application developers. However, this designation should most definitely not keep DBAs away from these packages. The DBMS_SQL package, for example, can be and has been used by DBAs to construct powerful, efficient scripts for maintaining database objects and extracting information about a database.

Chapter 2

The DBMS_SQL package offers the ability to execute dynamically (at runtime) constructed SQL statements, including DDL, and PL /SQL blocks of code. DBMS_SQL is simultaneously one of the most complex, useful, and rewarding of the built-in packages. It may take some time for you to get comfortable with the way to apply this technology. Once you are up and running, however, you will be amazed at the feats you will be able to perform!

Chapter 3

It seems that everything these days has something to do with communication. Messaging technologies are consequently receiving lots of attention, but they are nothing new for Oracle. Oracle has already provided, through the DBMS_PIPE and DBMS_ALERT packages, mechanisms for communication between database sessions. For instance, using database pipes with DBMS_PIPE, an application can communicate with a service routine external to the database. Or, debuggers that capture PL /SQL errors can utilize the fact that DBMS_PIPE is asynchronous with database transactions, getting the errors logged whether the transaction issued a COMMIT or a ROLLBACK. DBMS_ALERT is a little different in that it allows synchronous notification to multiple users that specific database events have occurred.

Chapter 4

Complex, multiuser applications managing new types of resources (objects, BLOBs, etc.) will require the ability to manage contention for those resources. The Oracle database manages concurrent, multiuser contention for data using sophisticated locking mechanisms. Well, Oracle has now provided developers with the “keys” to those locking mechanisms through the DBMS_LOCK package. Watch out, though. This deceptively powerful package might also put your applications to “sleep”! DBMS_TRANSACTION provides a programmatic interface to a number of transaction-oriented SQL statements.

Chapter 5

Oracle8 offers a new capability called Oracle Advanced Queuing, which will make it much easier for developers to build applications requiring deferred execution of activity. Oracle is positioning Oracle AQ as an alternative to the queuing mechanisms of teleprocessing monitors and messaging interfaces. Oracle AQ will serve as a foundation technology for workflow management applications, both those delivered by Oracle Corporation itself and those implemented by third parties. Two packages, DBMS_AQ and DBMS_AQADM, make Advanced Queuing available from within PL /SQL programs.

Chapter 6

The built-in packages offer a number of ways to generate output from within your PL /SQL program. While updating a database table is, of course, a form of “output” from PL /SQL, this chapter shows you how to use DBMS_OUTPUT to display information to your screen and UTL_FILE to read and write information in server-side files.

Chapter 7

It can be difficult to tell what is going on while an application is running. Wouldn’t it be nice to know that the big performance problem was because users were spending 90% of their time in a module that someone else wrote? Seriously, though, one key to providing accurate, quantitative information about utilization and resource consumption is to instrument applications such that they can be tracked externally. The DBMS_APPLICATION_INFO package lets an application register itself in a way that can be monitored at runtime through the V$SESSION and V$SQLAREA virtual tables.

Chapter 8

With Oracle8, Oracle finally offers native, robust support for large objects. Oracle LOBs can be stored in a column in a table or as an attribute of an object type. The database supports objects of up to four gigabytes of data, including character text, graphic images, video, or “raw” data. The DBMS_LOB package provides procedures and functions to access and manipulate LOBs within PL /SQL programs.

Chapter 9

This chapter introduces you to several packages that specialize in working with specific types of Oracle data. The DBMS_ROWID package allows you to work with the two different ROWID formats available in Oracle8: extended (new to Oracle8) and restricted (traditional Oracle7 ROWIDs). The UTL_RAW package offers a set of functions allowing you to perform concatenation, substring, bit-wise logical analysis, byte translation, and length operations on RAW data. The UTL_REF package, new in Oracle8 Release 8.1, provides a PL /SQL interface to select and modify objects (instances of an object type) in an object table without having to specify or know about the underlying database table.

Chapter 10

You can’t find a neat category for eveything, can you? This chapter collects together a variety of useful packages you are sure to dip into on a regular basis. DBMS_UTILITY is the actual “miscellaneous” package. It offers programs to free unused user memory, parse comma-delimited lists, calculate the elapsed time of PL /SQL programs, and much more. You never know what you’ll find popping up next in DBMS_UTILITY! DBMS_DESCRIBE contains a single procedure, DESCRIBE_PROCEDURE, which you can use to get information about the parameters of a stored program. DBMS_DDL contains programs to recompile stored code, analyze objects in your schema, and modify the referenceability of object identifiers in Oracle8. DBMS_RANDOM supplies PL /SQL developers with a random number generator.

Part III

This part of the book groups together packages used to monitor, modify, or manage server-side resources in various ways. These packages will be of definite interest to DBAs; however, developers will also benefit from becoming familiar with them. The packages include facilities for managing session and shared pool memory, monitoring internal space utilization in segments, and executing PL/SQL procedures automatically in background processes.

Chapter 11

Oracle technologies allow for a great deal of user customization and security. Language preferences can be specified at the session level using the NLS options of the ALTER SESSION command. Roles can be used to distinguish groups of users from each other and to modify application behavior accordingly. The DBMS_SESSION package contains programs that can modify and inspect session roles and settings from within PL /SQL. This package also contains programs for manipulating session memory and package states; these programs are very instructive to understand, even if not often used. This chapter also describes the DBMS_SYSTEM package, which lets administrators set various trace events in other users’ sessions. This can be invaluable when tracking down difficult application performance or database issues.

Chapter 12

With the built-in packages, Oracle is exposing information about database internals—information that is not directly visible in the catalog. The DBMS_SPACE package gives DBAs an analysis of the amount of space both used and free within a table, index, or cluster segment. It also provides information about segment free list sizes, of special interest to Oracle parallel server administrators. On the memory side, the DBMS_SHARED_POOL package gives DBAs some measure of control over the Oracle System Global Area’s (SGA’s) shared pool. By pinning large packages into the shared pool, expensive runtime memory management (and even errors) can be avoided.

Chapter 13

The job queue is a powerful facility introduced with Version 7.2 of Oracle that gives DBAs and developers the ability to schedule noninteractive execution of PL /SQL programs. It’s ideal for handling regular administrative tasks like analyzing tables and for automatically kicking off long-running batch jobs without requiring operator attention. The DBMS_JOB package is your interface to this facility, and it’s a little tricky to use. This chapter explains how the job queue works and how to get it to work for you.

Part IV

This part of the book describes the packages used for simple and advanced replication. It provides details about each package’s procedures and functions, and explains how to use them together to create a replicated environment.

Chapter 14

The packages DBMS_SNAPSHOT, DBMS_REFRESH, and DBMS_OFFLINE_SNAPSHOT, and some of the programs in DBMS_REPCAT, embody the functionality to maintain snapshots, snapshot groups, and snapshot logs. This includes refreshing snapshots, changing refresh intervals, adding and removing snapshots from snapshot groups, and purging uneccessary data from snapshot logs. You can use DBMS_OFFLINE_SNAPSHOT to instantiate a new snapshot using an export of a master table instead of using the CREATE SNAPSHOT command. In some cases, this type of instantiation is quicker because of network constraints.

Chapter 15

This chapter explains how to use the DBMS_REPCAT, DBMS_REPUTIL, DBMS_OFFLINE_OG, DBMS_REPCAT_ADMIN, DBMS_REPCAT_AUTH, and DBMS_RECTIFIER_DIFF packages to create and administer your replicated databases. DBMS_REPCAT and DBMS_REPUTIL contain the bulk of the procedures required to use the advanced replication option (formerly referred to as “symmetric replication”). Use these procedures to define and modify replication groups, replicated objects, master sites, and snapshot sites.

Chapter 16

Inevitably, conflicts will arise in a replicated environment. You can configure Oracle to automatically detect, correct, and report many of the forseeable conflicts by using procedures in DBMS_REPCAT to create and maintain resolution methods.

Chapter 17

The advanced replication option relies heavily on DBMS_DEFER to propagate data changes among master sites, but you can also use it yourself to queue deferred remote procedure calls (RPCs). DBMS_DEFER_QUERY is primarily a diagnostic tool that reports on queued RPCs. Use this package to determine the values of passed parameters and more. DBMS_DEFER_SYS contains the procedures you will need to add and remove default destinations for your RPCs, schedule propagation, and manage transactions.