1. Programming in PL/SQL

   1. Chapter 1 Introduction to PL/SQL

      1. What Is PL/SQL?
      2. The Origins of PL/SQL
      3. So This Is PL/SQL
      4. About PL/SQL Versions
      5. Resources for PL/SQL Developers
      6. Some Words of Advice

   2. Chapter 2 Creating and Running PL/SQL Code

      1. SQL*Plus
      2. Performing Essential PL/SQL Tasks
      3. Calling PL/SQL from Other Languages

   3. Chapter 3 Language Fundamentals

      1. PL/SQL Block Structure
      2. The PL/SQL Character Set
      3. Identifiers
      4. Literals
      5. The Semicolon Delimiter
      6. Comments
      7. The PRAGMA Keyword
      8. Labels

2. PL/SQL Program Structure

   1. Chapter 4 Conditional and Sequential Control

      1. IF Statements
      2. CASE Statements and Expressions
      3. The GOTO Statement
      4. The NULL Statement

   2. Chapter 5 Iterative Processing with Loops

      1. Loop Basics
      2. The Simple Loop
      3. The WHILE Loop
      4. The Numeric FOR Loop
      5. The Cursor FOR Loop
      6. Loop Labels
      7. Tips for Iterative Processing

   3. Chapter 6 Exception Handlers

      1. Exception-Handling Concepts and Terminology
      2. Defining Exceptions
      3. Raising Exceptions
      4. Handling Exceptions
      5. Building an Effective Error Management Architecture
      6. Making the Most of PL/SQL Error Management

3. PL/SQL Program Data

   1. Chapter 7 Working with Program Data

      1. Naming Your Program Data
      2. Overview of PL/SQL Datatypes
      3. Declaring Program Data
      4. Programmer-Defined Subtypes
      5. Conversion Between Datatypes

   2. Chapter 8 Strings

      1. String Datatypes
      2. Working with Strings
      3. String Function Quick Reference

   3. Chapter 9 Numbers

      1. Numeric Datatypes
      2. Number Conversions
      3. Numeric Functions

   4. Chapter 10 Dates and Timestamps

      1. Datetime Datatypes
      2. Getting the Date and Time
      3. Interval Datatypes
      4. Datetime Conversions
      5. Date and Timestamp Literals
      6. Interval Conversions
      7. Interval Literals
      8. CAST and EXTRACT
      9. Datetime Arithmetic
      10. Date/Time Functions

   5. Chapter 11 Records

      1. Records in PL/SQL

   6. Chapter 12 Collections

      1. Collections Overview
      2. Collection Methods (Built-Ins)
      3. Working with Collections
      4. Nested Table Multiset Operations
      5. Maintaining Schema-Level Collections

   7. Chapter 13 Miscellaneous Datatypes

      1. The BOOLEAN Datatype
      2. The RAW Datatype
      3. The UROWID and ROWID Datatypes
      4. The LOB Datatypes
      5. Working with LOBs
      6. Predefined Object Types

4. SQL in PL/SQL

   1. Chapter 14 DML and Transaction Management

      1. DML in PL/SQL
      2. Bulk DML with the FORALL Statement
      3. Transaction Management
      4. Autonomous Transactions

   2. Chapter 15 Data Retrieval

      1. Cursor Basics
      2. Working with Implicit Cursors
      3. Working with Explicit Cursors
      4. BULK COLLECT
      5. SELECT ... FOR UPDATE
      6. Cursor Variables and REF CURSORs
      7. Cursor Expressions

   3. Chapter 16 Dynamic SQL and Dynamic PL/SQL

      1. NDS Statements
      2. Binding Variables
      3. Working with Objects and Collections
      4. Dynamic PL/SQL
      5. Recommendations for NDS
      6. When to Use DBMS_SQL
      7. NDS Utility Package

5. PL/SQL Application Construction

   1. Chapter 17 Procedures, Functions, and Parameters

      1. Modular Code
      2. Procedures
      3. Functions
      4. Parameters
      5. Local Modules
      6. Module Overloading
      7. Forward Declarations
      8. Advanced Topics
      9. Go Forth and Modularize!

   2. Chapter 18 Packages

      1. Why Packages?
      2. Rules for Building Packages
      3. Rules for Calling Packaged Elements
      4. Working with Package Data
      5. When to Use Packages
      6. Packages and Object Types

   3. Chapter 19 Triggers

      1. DML Triggers
      2. DDL Triggers
      3. Database Event Triggers
      4. INSTEAD OF Triggers
      5. AFTER SUSPEND Triggers
      6. Maintaining Triggers

   4. Chapter 20 Managing PL/SQL Code

      1. Managing Code in the Database
      2. Using Native Compilation
      3. Using the Optimizing Compiler and Compile-Time Warnings
      4. Conditional Compilation
      5. Testing PL/SQL Programs
      6. Debugging PL/SQL Programs
      7. Tuning PL/SQL Programs
      8. Protecting Stored Code

   5. Chapter 21 I/O and PL/SQL

      1. Displaying Information
      2. Reading and Writing Files
      3. Sending Email
      4. Working with Web-Based Data (HTTP)
      5. Other Types of I/O Available in PL/SQL

6. Advanced PL/SQL Topics

   1. Chapter 22 Application Security and PL/SQL

      1. Security Overview
      2. Encryption
      3. Row-Level Security
      4. Application Contexts
      5. Fine-Grained Auditing

   2. Chapter 23 Inside PL/SQL

      1. Looking Under the Hood
      2. PL/SQL's Optimizing Compiler
      3. Dependency Management
      4. Execution Authority Models
      5. PL/SQL and Oracle Memory
      6. Server-Side PL/SQL Processing: Reprise
      7. What You Need to Know

   3. Chapter 24 Globalization and Localization in PL/SQL

      1. Overview and Terminology
      2. Unicode Primer
      3. Character Semantics
      4. String Sort Order
      5. Multilingual Information Retrieval
      6. Date/Time
      7. Currency Conversion
      8. Globalization Development Kit for PL/SQL

   4. Chapter 25 Object-Oriented Aspects of PL/SQL

      1. Introduction to Oracle's Object Features
      2. An Extended Example
      3. Object Views
      4. Maintaining Object Types and Object Views
      5. Pontifications

   5. Chapter 26 Calling Java from PL/SQL

      1. Oracle and Java
      2. Getting Ready to Use Java in Oracle
      3. A Simple Demonstration
      4. Using loadjava
      5. Using dropjava
      6. Managing Java in the Database
      7. Using DBMS_JAVA
      8. Publishing and Using Java in PL/SQL

   6. Chapter 27 External Procedures

      1. Introduction to External Procedures
      2. The Oracle Net Configuration
      3. Setting Up Multithreaded Mode
      4. Creating an Oracle Library
      5. Writing the Call Specification
      6. Raising an Exception from the Called C Program
      7. Nondefault Agents
      8. Maintaining External Procedures

7. Appendixes

   1. Appendix A Regular Expression Metacharacters and Function Parameters

      1. Metacharacters
      2. Function Parameters

   2. Appendix B Number Format Models

   3. Appendix C Date Format Models

1. Colophon

Ants are featured on the cover of Oracle PL /SQL Programming, Fourth Edition. At least 8,000 different species of ants can be found everywhere on Earth except the North and South Poles. Ants preserved in amber suggest that these insects existed 50 million years before humans.

Humans have long been fascinated by ants, because these tiny insects are accomplished builders, nurses, miners, and even farmers. Fables such as "The Ant and the Grasshopper" extol the virtues of hardworking, forward-looking ants. (Hail ants!) It is true that individual ants are able to perform amazing feats: an ant can carry up to 50 times its body weight, can travel the human equivalent of 40 miles a day, and can climb vertical heights the equivalent of Mount Everest. However, the greatest accomplishments of ants are those performed together for the good of their community.

Queen ants establish new communities, or nests, after their mating flight. On this flight the queen mates with several males. After mating, the males fall to Earth and die. The queen then finds an uninhabited nest, settles into it, and pulls her wings off. She will never fly again, and after removing her wings she is able to absorb the wing muscles as nutrients for her eggs. She will continue to lay eggs, thousands of them, for years.

During the three-stage development process, which takes about two months, the eggs, larvae, and pupae are cared for by the nurse ants, who feed, clean, and carefully move the young to warmer or cooler places in the nest, depending on the temperature. These nurse ants are, in turn, cared for by other worker ants, who feed the nurses with regurgitated food. The workers and the nurses will fight together to defend the young against enemies if the nest is invaded, either by another group of ants or by a larger animal.

Mary Anne Weeks Mayo was the production editor and copyeditor for Oracle PL/SQL Programming, Fourth Edition . Matt Hutchinson, Mary Brady, and Marlowe Shaeffer provided quality control. Ellen Troutman Zaig wrote the index.

Edie Freedman designed the cover of this book. The cover image is a 19th-century engraving from the Dover Pictorial Archive. Karen Montgomery produced the cover layout with Adobe InDesign CS using Adobe's ITC Garamond font.

David Futato designed the interior layout. This book was converted to FrameMaker 5.5.6 by Keith Fahlgren with a format conversion tool created by Erik Ray, Jason McIntosh, Neil Walls, and Mike Sierra that uses Perl and XML technologies. The text font is Linotype Birka; the heading font is Adobe Myriad Condensed; and the code font is LucasFont's TheSans Mono Condensed. The illustrations that appear in the book were produced by Robert Romano, Jessamyn Read, and Lesley Borash using Macromedia FreeHand MX and Adobe Photoshop CS. The tip and warning icons were drawn by Christopher Bing. This colophon was written by Clairemarie Fisher O'Leary.