Oracle Database 11g PL/SQL Programming Workbook

Oracle Database 11g PL/SQL Programming Workbook

by Michael McLaughlin, John Harper
Released February 2010
Publisher(s): McGraw Hill Computing
ISBN: 9780071770415

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Ramp Up Your PL/SQL Programming Skills

Master PL/SQL through the hands-on exercises, extensive examples, and real-world projects inside this Oracle Press guide. Filled with best practices, Oracle Database 11g PL/SQL Programming Workbook covers all the latest features and enhancements of the language. Mastery checks at the end of each chapter reinforce the material covered, and sample code from the book is available for download. Even experienced Oracle professionals will benefit from this practical resource.

  • Understand the Oracle development architecture and the mechanics of connections
  • Work with data types, structures, blocks, cursors, and PL/SQL semantics
  • Write, deploy, and use functions, procedures, and packages
  • Manage transactions and more
  • Use dynamic SQL statements in real-world applications
  • Support online transaction processing and data warehousing applications with external tables
  • Find syntax samples and best practices to solve problems
  • Write, deploy, and use object types

For a complete list of Oracle Press titles, visit www.OraclePressBooks.com

Table of contents

  1. Cover Page
  2. Oracle Database 11g PL/SQL Programming Workbook
  3. Copyright Page
  4. Dedication
  5. Contents
  6. Acknowledgments
  7. Introduction
  8. PART I PL/SQL Fundamentals
    1. 1 Oracle Development Overview
      1. History and Background
      2. Architecture
        1. Two-Tier Model
        2. Three-Tier Model
      3. Downloadable Code
      4. Summary
        1. Best Practice Review
        2. Mastery Check
    2. 2 PL/SQL Basics
      1. PL/SQL Blocks
        1. Prototypes
        2. Basic Anonymous Block
        3. Hello World Anonymous Block
        4. Hello Somebody Anonymous Block
        5. Hello Somebody Named Block
        6. Nested Blocks
      2. Variables: Types, Assignments, and Operators
        1. Text Data Types
        2. Date and Timestamp Types
        3. Number Types
        4. Composite Variable Types
      3. Control Structures
        1. IF Statement
        2. Case Statement
        3. Iterative Structures
      4. Bulk Operations
      5. Downloadable Code
      6. Summary
        1. Best Practice Review
        2. Mastery Check
    3. 3 Transaction Scope
      1. The Database Acid Test
        1. Atomicity
        2. Consistency
        3. Isolation
        4. Durability
      2. Multiversioned Concurrency Control
      3. Savepoints, Commits, and Transaction Rollbacks
        1. Savepoints and Rollback
        2. Commit
      4. DML Locking and Isolation Control
        1. Concurrency
      5. Invoker and Definer Rights
        1. Downloadable Code
      6. Summary
        1. Best Practice Review
        2. Mastery Check
    4. 4 Error Management
      1. Error Types
        1. Compilation Errors
        2. Semantics Errors
      2. PL/SQL Instrumentation
        1. Downloadable Code
      3. Summary
        1. Best Practice Review
        2. Mastery Check
  9. PART II PL/SQL Programming
    1. 5 Functions
      1. Function Architecture
        1. Pass-by-Value Functions
        2. Pass-by-Reference Function
        3. Function Model Choices
        4. Transaction Scope
        5. Call Notation
      2. Function Development
        1. Deterministic Clause
        2. PARALLEL_ENABLE Clause
        3. Pipelined Table Clause
        4. RESULT_CACHE Clause
        5. System Reference Cursors
        6. Recursive Functions
        7. Pass-by-Reference Functions
      3. Downloadable Code
      4. Summary
        1. Best Practice Review
        2. Mastery Check
    2. 6 Procedures
      1. Procedure Architecture
        1. Pass-by-Value Procedures
        2. Pass-by-Reference Function
        3. Transaction Scope
        4. Call Notation
      2. Procedure Development
        1. Pass-by-Value Procedures
        2. Pass-by-Reference Procedures
        3. Downloadable Code
      3. Summary
        1. Best Practice Review
        2. Mastery Check
    3. 7 Collections
      1. Scalar Collections
        1. Varrays
        2. Nested Tables
        3. Associative Arrays
      2. Object Type Collections
        1. SQL Nested Tables
        2. PL/SQL Nested Tables
      3. Record Type Collections
      4. Downloadable Code
      5. Summary
        1. Best Practice Review
        2. Mastery Check
    4. 8 Packages
      1. Package Architecture
        1. Greater Organization
        2. Program Persistence and Memory Residency
        3. Larger Program Size
      2. Package Specifications
      3. Bodiless Packages
      4. Package Bodies
        1. Dependency Resolution
      5. %TYPE and %ROWTYPE
      6. Modular Programming
      7. Overloading
      8. Greater Security
      9. Definer and Invoker Rights
      10. Downloadable Code
      11. Conclusion
        1. Best Practices Review
        2. Mastery Check
    5. 9 Triggers
      1. Trigger Architecture
      2. Trigger Firing Order
      3. Trigger Types
        1. DML Triggers
        2. DDL Triggers
        3. System Event Triggers
      4. Security and Oracle Fine Grained Auditing
      5. Distributed Data Integrity
      6. Downloadable Code
      7. Conclusion
        1. Best Practices Introduced
        2. Mastery Check
    6. 10 Objects
      1. Basic Declaration, Implementation, and Use
        1. Define and Implement Object Types
        2. Implement Getters and Setters
        3. Implement Static Methods
      2. Comparing Objects
        1. MAP Function Comparison
        2. ORDER Function Comparison
      3. Inheritance and Polymorphism
      4. Object Collections
      5. Downloadable Code
      6. Summary
        1. Best Practice Review
        2. Mastery Check
  10. PART III PL/SQL Advanced Programming
    1. 11 Dynamic SQL
      1. Native Dynamic SQL
        1. Dynamic Statements Without Bind Variables
        2. Dynamic Statements with Bind Variables
      2. DBMS_SQL Package
        1. Dynamic Statements Without Bind Variables
        2. Dynamic Statements with Bind Variables
        3. Mixing and Matching NDS and DBMS_SQL
      3. Downloadable Code
      4. Summary
        1. Best Practice Review
        2. Mastery Check
    2. 12 External Files
      1. External Table Architecture
        1. CSV Files
        2. Position-Specific Files
        3. TSV Files
        4. Convert XML to CSV Files
        5. Clean Up Files with Java Stored Libraries
        6. Importing CLOBs
      2. Downloadable Code
      3. Summary
        1. Best Practice Review
        2. Mastery Check
  11. PART IV Appendixes
    1. A Wrapping PL/SQL
      1. Limitations of Wrapping PL/SQL
        1. Limitations of the PL/SQL wrap Utility
        2. Limitations of the DBMS_DDL.WRAP Function
      2. Using the wrap Command Line Utility
      3. Using the DBMS_DDL Command Line Utility
        1. WRAP Function
        2. CREATE_WRAPPED Procedure
        3. Summary
    2. B PL/SQL Hierarchical Profiler
      1. Configuring the Schema
      2. Collecting Profiler Data
      3. Understanding Profiler Data
        1. Reading the Raw Output
        2. Defining the PL/SQL Profiler Tables
        3. Querying the Analyzed Data
        4. Using the plshprof Command Line Utility
    3. C PL/Scope
      1. Configuring PL/Scope Data Collection
        1. Viewing PL/Scope Collected Data
    4. D PL/SQL Built-in Packages and Types
      1. Examples of Package Usage
        1. DBMS_COMPARISON
        2. DBMS_FGA
        3. DBMS_OBFUSCATION_TOOLKIT
    5. E Mastery Check Answers
      1. Chapter 1
      2. Chapter 2
      3. Chapter 3
      4. Chapter 4
      5. Chapter 5
      6. Chapter 6
      7. Chapter 7
      8. Chapter 8
      9. Chapter 9
      10. Chapter 10
      11. Chapter 11
      12. Chapter 12
  12. Index

Product information

  • Title: Oracle Database 11g PL/SQL Programming Workbook
  • Author(s): Michael McLaughlin, John Harper
  • Release date: February 2010
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071770415