You are previewing Expert Oracle PL/SQL.
O'Reilly logo
Expert Oracle PL/SQL

Book Description

Experienced PL/SQL programmers and Oracle developers will learn to master Oracle's procedural extension to industry-standard SQL. Using this authoritative resource, readers will become experts at developing robust, high-performance enterprise applications with PL/SQL. Detailed examples describe the practical aspect of each feature, and provide ideas to the reader of how they can best exploit the functionality.

Table of Contents

  1. Copyright
  2. About the Authors
  3. About the Technical Editor
  4. Acknowledgments
  5. Introduction
    1. What This Book Is Not
    2. Conventions
    3. Examples
  6. I. Advanced Concepts, Internals, and Debugging Concepts
    1. 1. Introduction to Advanced Concepts, Patterns, and Techniques
      1. 1.1. What Is an Expert?
      2. 1.2. 10g Release 2 New Features
        1. 1.2.1. Obfuscation of PL/SQL Source Code
        2. 1.2.2. Conditional Compilation
        3. 1.2.3. Asynchronous Commit
        4. 1.2.4. Data Mining—Using Predictive Analytics
        5. 1.2.5. String Comparisons in PL/SQL
        6. 1.2.6. DBMS_OUTPUT.PUT_LINE
      3. 1.3. Summary
    2. 2. PL/SQL Internals
      1. 2.1. A Database Architecture Primer
        1. 2.1.1. User and Server Processes
        2. 2.1.2. The Process Global Area
        3. 2.1.3. An Instance
        4. 2.1.4. The Database
      2. 2.2. A PL/SQL Architecture Overview
        1. 2.2.1. Early vs. Late Binding
      3. 2.3. The PL/SQL Compiler
        1. 2.3.1. Compilation Steps
        2. 2.3.2. Interpreted vs. Native Compilation
      4. 2.4. PL/SQL Virtual Machine
      5. 2.5. Summary
    3. 3. Debugging PL/SQL Applications
      1. 3.1. Introducing Error-Handling Mechanisms
        1. 3.1.1. Differences Between Compile and Runtime Errors
        2. 3.1.2. The Standard Syntax for Exception Management
      2. 3.2. Discussing the Error Stack and Demonstrating Stack Management
        1. 3.2.1. Introducing and Demonstrating Error Stack Management
        2. 3.2.2. Introducing and Demonstrating 10g New Error Stack Formatting
      3. 3.3. Error Management in Database Triggers
      4. 3.4. Summary
  7. II. Invoker's Rights, Java Libraries, and Object Patterns
    1. 4. Invoker's-Rights Architecture
      1. 4.1. Introducing Definer's-Rights and Invoker's-Rights Concepts
        1. 4.1.1. Definer's-Rights Concepts
        2. 4.1.2. Invoker's-Rights Concepts
      2. 4.2. Understanding the Architectures
        1. 4.2.1. Understanding Definer's-Rights Architecture
        2. 4.2.2. Understanding Invoker's-Rights Architecture
      3. 4.3. Comparing and Contrasting Implementation Strategies
        1. 4.3.1. Definer's-Rights
        2. 4.3.2. Invoker's-Rights
      4. 4.4. Summary
    2. 5. Extending PL/SQL with Java Libraries
      1. 5.1. Java Architecture in Oracle
      2. 5.2. Oracle JDBC Connection Types
        1. 5.2.1. The Client-Side Driver, or JDBC Thin Driver
        2. 5.2.2. The Oracle Call Interface Driver, or Middle-Tier Thick Driver
        3. 5.2.3. The Oracle Server-Side Internal Driver, or Server-Tier Thick Driver
      3. 5.3. Building Java Class Libraries in Oracle
        1. 5.3.1. Building Internal Server Java Functions
        2. 5.3.2. Building Internal Server Java Procedures
        3. 5.3.3. Building Internal Server Java Objects
        4. 5.3.4. Troubleshooting Java Class Library Build, Load, Drop, and Use
      4. 5.4. Mapping Oracle Types
      5. 5.5. Summary
    3. 6. Implementing PL/SQL Objects
      1. 6.1. Introducing Objects and Object Types
        1. 6.1.1. What Is an Oracle Object?
      2. 6.2. Instantiating Object Types
      3. 6.3. Extending PL/SQL Object Types to Compound Object Types
      4. 6.4. Using PL/SQL Object Types as a Subsystem Façade
      5. 6.5. Summary
  8. III. Optimizing PL/SQL Solutions
    1. 7. Threading PL/SQL Execution
      1. 7.1. Introducing Parallel PL/SQL Concepts and Components
        1. 7.1.1. Introducing and Demonstrating the DBMS_JOB Package
        2. 7.1.2. Introducing, Comparing, and Contrasting DBMS_PIPE and DBMS_ALERT
        3. 7.1.3. Introducing and Discussing Threading Concepts for Parallel Programs
      2. 7.2. Demonstrating How to Build a Parallel PL/SQL Application
        1. 7.2.1. Demonstrating a Thread-of-Control
        2. 7.2.2. Demonstrating Threads-of-Execution
        3. 7.2.3. Demonstrating Parallel Execution
      3. 7.3. Summary
    2. 8. High Performance PL/SQL
      1. 8.1. PL/SQL Optimization in Oracle 10g
      2. 8.2. SQL Tuning Review
        1. 8.2.1. Tracing and TKPROF
        2. 8.2.2. Indexes
        3. 8.2.3. The CBO and Statistics
      3. 8.3. Finding PL/SQL Performance Problems
        1. 8.3.1. Compiler Warnings
        2. 8.3.2. PL/SQL Profiler
      4. 8.4. Native Compilation
      5. 8.5. Performance Tuning Strategy
        1. 8.5.1. SQL Steps
        2. 8.5.2. PL/SQL Steps
      6. 8.6. Summary
  9. IV. Text Management Using PL/SQL
    1. 9. Introduction to Information Retrieval
      1. 9.1. Information Retrieval Overview
        1. 9.1.1. IR Models
        2. 9.1.2. Text Processing
        3. 9.1.3. Queries
      2. 9.2. Intro to Oracle Text
        1. 9.2.1. Oracle Text and IR
        2. 9.2.2. Features Overview
        3. 9.2.3. Index Creation
        4. 9.2.4. Query Processing
      3. 9.3. Building a Search Application Using PL/SQL
        1. 9.3.1. PL/SQL Search and Retrieval
        2. 9.3.2. PSP Search
      4. 9.4. Summary
    2. 10. Introduction to Regular Expressions
      1. 10.1. Foundations of Regular Expressions
        1. 10.1.1. History
      2. 10.2. Oracle and Regular Expressions
        1. 10.2.1. Features Overview
        2. 10.2.2. Metacharacters
        3. 10.2.3. Regex Operator and Functions
      3. 10.3. PL/SQL and Regular Expressions
        1. 10.3.1. Common Uses
        2. 10.3.2. Development Tools
      4. 10.4. Summary
  10. V. PL/SQL Server Pages and Database Management
    1. 11. Leveraging the PL/SQL Toolkit
      1. 11.1. Criteria for the Selection Between PL/SQL Toolkit and PSPs
      2. 11.2. Describing and Configuring the Standalone HTTP Server
      3. 11.3. Describing and Configuring the Standalone Oracle HTTP Server
        1. 11.3.1. Describing and Configuring the mod_plsql Cartridge
      4. 11.4. Configuring the Oracle HTTP Server
        1. 11.4.1. Configuring the Oracle 9i HTTP Server
        2. 11.4.2. Configuring the Oracle 10gR1 HTTP Server
      5. 11.5. Building and Accessing PL/SQL Toolkit Stored Procedures
        1. 11.5.1. Developing and Running No Formal Parameter Procedures
        2. 11.5.2. Developing Formal Parameter Procedures
        3. 11.5.3. Understanding Advantages and Limitations
      6. 11.6. Building and Accessing PL/SQL Server Pages (PSPs)
        1. 11.6.1. Developing and Running No Formal Parameter PSP Procedures
        2. 11.6.2. Developing Formal Parameter PSP Procedures
        3. 11.6.3. Understanding Advantages and Limitations
      7. 11.7. Summary
    2. 12. Managing the Database
      1. 12.1. Database Performance
        1. 12.1.1. DBMS_STATS: Table and Index Statistics
      2. 12.2. Moving Data
        1. 12.2.1. DBMS_DATAPUMP: Import and Export Data
        2. 12.2.2. DBMS_FILE_TRANSFER: Move Binary Files
        3. 12.2.3. UTL_COMPRESS: Compress Database Files
      3. 12.3. Data Security
        1. 12.3.1. Etarvqitcrja
        2. 12.3.2. The DBMS_CRYPTO Encryption Toolkit
      4. 12.4. Summary
  11. VI. Appendixes
    1. A. Regular Expression Metacharacters and Functions
      1. A.1. POSIX Bracket Expressions
      2. A.2. POSIX Metacharacters
      3. A.3. Non-POSIX Metacharacters
      4. A.4. Functions
    2. B. Oracle Text Supplied Packages
      1. B.1. Package Creation
      2. B.2. The Packages
        1. B.2.1. CTX_ADM
        2. B.2.2. CTX_CLS
        3. B.2.3. CTX_DDL
        4. B.2.4. CTX_DOC
        5. B.2.5. CTX_OUTPUT
        6. B.2.6. CTX_QUERY
        7. B.2.7. CTX_REPORT
        8. B.2.8. CTX_THES
      3. B.3. The Most Common Packages
        1. B.3.1. CTX_DDL Examples
        2. B.3.2. CTX_REPORT Examples
    3. C. PL/SQL Web Toolkit Packages
      1. C.1. The Packages
        1. C.1.1. HTF
        2. C.1.2. HTP
        3. C.1.3. OWA_CACHE
        4. C.1.4. OWA_COOKIE
        5. C.1.5. OWA_CUSTOM
        6. C.1.6. OWA_IMAGE
        7. C.1.7. OWA_OPT_LOCK
        8. C.1.8. OWA_PATTERN
        9. C.1.9. OWA_SEC
        10. C.1.10. OWA_TEXT
        11. C.1.11. OWA_UTIL
        12. C.1.12. WPG_DOCLOAD
    4. D. Basic Primer on Java and Java Database Connectivity (JDBC)
      1. D.1. Java and JDBC Architecture
      2. D.2. Configuring the Oracle Java Environment
      3. D.3. Testing a Client-Side or Thin-Driver JDBC Connection