About This Book

By reading and using the resources in this book, you will be better able to take advantage of the PL/SQL-based functionality in the Oracle database that is the most critical for DBAs.

We don't attempt to provide comprehensive coverage of the Oracle PL/SQL language in this book. We provide a solid overview of PL/SQL in Chapter 1, but otherwise assume a basic working knowledge of the language. If you are new to PL/SQL, we suggest that you get started by reading Learning Oracle PL/SQL. From there, Oracle PL/SQL Programming, Fourth Edition, will serve as a comprehensive reference and user guide. That 1,200-page volume is the classic book on both language fundamentals and advanced features.

Oracle PL/SQL for DBAs consists of eight chapters and one appendix, as follows:

Chapter 1, Introduction to PL/SQL, provides a whirlwind tour of the PL/SQL language, touching on all of the topics DBAs will need to become familiar with, from the basics of the PL/SQL block structure, identifier construction, and program data declarations, to the use of control and error-handling statements, to the construction of procedures, functions, packages, and triggers in PL/SQL.

Chapter 2, Cursors, describes PL/SQL cursors and how you can improve database performance by taking advantage of such features as cursor reuse, cursor soft-parsing and soft-closing, and various characteristics of implicit and explicit cursors. It also describes the use of REF CURSORs, bulk fetching, cursor parameters, and cursor expressions.

Chapter 3, Table Functions, explores the use of functions that can be used as data sources for queries and that are used frequently in Extraction, Transformation, and Loading (ETL) operations. Table functions are crucial when complex logic must be performed from within SELECT statements, usually to transform data. This chapter also explores how you can use pipelining, parallelizing, and nesting table functions to reap enormous performance benefits.

Chapter 4, Data Encryption and Hashing, explains how you can use Oracle's tools to build basic encryption and key management systems to protect sensitive data. Focusing on the use of the built-in DBMS_CRYPTO (for Oracle Database 10g) and DBMS_OBFUSCATION_TOOLKIT (for Oracle9i Database) packages, it describes encryption, decryption, cryptographic hashing, and Message Authentication Code (MAC) operations. It also describes the new Transparent Data Encryption (TDE) features introduced in Oracle Database 10g Release 2.

Chapter 5, Row-Level Security, explains how you can define policies on database tables so that you can restrict which rows particular users can see or change in those tables. Using the DBMS_RLS package, you can also effectively make tables and views read-only based on user credentials.

Chapter 6, Fine-Grained Auditing, shows how you can extend traditional Oracle auditing to capture both database changes and queries. Using the DBMS_FGA package, you can not only enforce security, but also analyze patterns of SQL usage and data access. This chapter also describes how FGA interacts with Oracle's flashback query and trigger features.

Chapter 7, Generating Random Values, discusses situations in which you may need to generate random values (e.g., creating temporary passwords or web site user IDs, generating statistically correct test data, or generating keys when building an encryption infrastructure). It focuses on the use of Oracle's built-in DBMS_RANDOM package.

Chapter 8, Scheduling, describes the use of the DBMS_SCHEDULER package (introduced in Oracle Database 10g and replacing the older DBMS_JOB package) in scheduling jobs to be performed at regular intervals (e.g., collecting statistics, gathering free space information, or notifying DBAs of problems).

Appendix A, Quick Reference, provides a summary of the built-in package specifications described in this book, as well as the data dictionary views related to these packages.