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!

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.