Expert PL/SQL Practices for Oracle Developers and DBAs

Expert PL/SQL Practices for Oracle Developers and DBAs

by John Beresniewicz, Adrian Billington, Martin Büchi, Melanie Caffrey, Ron Crisco, Lewis Cunningham, Dominic Delmolino, Sue Harper, Torben Holm, Connor McDonald, Arup Nanda, Stephan Petit, Michael Rosenblum, Robyn Sands, Riyaj Shamsudeen
Released July 2011
Publisher(s): Apress
ISBN: 9781430234852

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

Expert PL/SQL Practices is a book of collected wisdom on PL/SQL programming from some of the best and the brightest in the field. Each chapter is a deep-dive into a specific problem, technology, or feature set that you'll face as a PL/SQL programmer. Each author has chosen their topic out of the strong belief that what they share can make a positive difference in the quality and scalability of code that you write.

The path to mastery begins with syntax and the mechanics of writing statements to make things happen. If you've reached that point with PL/SQL, then let the authors of Expert PL/SQL Practices show you how to combine syntax and mechanics with features and techniques to really make the language sing. You'll learn to do more with less effort, to write code that scales and performs well, and to eliminate and avoid defects.

These authors are passionate about PL/SQL and the power it places at your disposal. They want you to succeed, to know all that PL/SQL can offer. Let Expert PL/SQL Practices open your eyes to the full power of Oracle's world-class language for the database engine.

  • Goes beyond the manual to cover good techniques and best practices

  • Delivers knowledge usually gained only by hard experience

  • Covers the functionality that distinguishes PL/SQL as a powerful and scalable programming language for deploying logic inside the database engine

Table of contents

  1. Title
  2. Contents at a Glance
  3. Contents
  4. About the Authors
  5. About the Technical Reviewers
  6. Introduction
  7. Chapter 1: Do Not Use
    1. Row-by-Row Processing
    2. Nested Row-by-Row Processing
    3. Lookup Queries
    4. Excessive Access to DUAL
    5. Excessive Function Calls
    6. Database Link Calls
    7. Excessive Use of Triggers
    8. Excessive Commits
    9. Excessive Parsing
    10. Summary
  8. Chapter 2: Dynamic SQL: Handling the Unknown
    1. The Hero
    2. Sample of Dynamic Thinking
    3. Security Issues
    4. Performance and Resource Utilization
    5. Object Dependencies
    6. Summary
  9. Chapter 3: PL/SQL and Parallel Processing
    1. Why Parallel Processing?
    2. Laws Affecting Parallel Processing
    3. The Rise of Big Data
    4. Parallel vs. Distributed Processing
    5. Parallel Hardware Architectures
    6. Identify Your Goals
    7. Candidate Workloads for Parallel Processing
    8. The MapReduce Programming Model
    9. Before Looking to PL/SQL
    10. Processes Available for Parallel Activities
    11. Using Parallel Execution Servers for MapReduce
    12. Summary
  10. Chapter 4: Warnings and Conditional Compilation
    1. PL/SQL Warnings
    2. Conditional Compilation
    3. Summary
  11. Chapter 5: PL/SQL Unit Testing
    1. Why Test Your Code?
    2. What Is Unit Testing?
    3. Tools for Building Unit Tests
    4. Preparing and Maintaining the Unit Test Environment
    5. Building Unit Tests
    6. Broadening the Scope of the Tests
    7. Supporting Unit Test Features
    8. Running Tests from the Command Line
    9. Summary
  12. Chapter 6: Bulk SQL Operations
    1. The Hardware Store
    2. Setting for the Examples in this Chapter
    3. Bulk Operations in PL/SQL
    4. Bulk Binding
    5. Error Handling with Bulk Bind
    6. A Justification for Massive Collections
    7. The Real Benefit: Client Bulk Processing
    8. Summary
  13. Chapter 7: Know Your Code
    1. What This Chapter Will (and Will Not) Cover
    2. Automated Code Analysis
    3. When to Analyze?
    4. Performing Static Analysis
    5. Performing Dynamic Analysis
    6. Summary
  14. Chapter 8: Contract-Oriented Programming
    1. Design by Contract
    2. Implementing PL/SQL Contracts
    3. Example: Testing Odd and Even Integers
    4. Useful Contract Patterns
    5. Principles For Bug-Free Code
    6. Summary
  15. Chapter 9: PL/SQL from SQL
    1. The Cost of Using PL/SQL Functions in SQL
    2. Reducing the Cost of PL/SQL Functions
    3. Summary
  16. Chapter 10: Choosing the Right Cursor
    1. Explicit Cursors
    2. Implicit Cursors
    3. Static REF Cursors
    4. Dynamic REF Cursors
    5. Summary
  17. Chapter 11: PL/SQL Programming in the Large
    1. The Database as PL/SQL-Based Application Server
    2. Requirements of Programming in the Large
    3. Uniformity through Conventions
    4. Modularization of Code and Data
    5. Object-Oriented Programming with PL/SQL
    6. Memory Management
    7. Summary
  18. Chapter 12: Evolutionary Data Modeling
    1. Lessons from Two Decades of System Development
    2. The Database and Agile Development
    3. Evolutionary Data Modeling
    4. Refactoring the Database
    5. Creating an Access Layer via PL/SQL
    6. The Agile Manifesto
    7. Using PL/SQL with Evolutionary Data Modeling
    8. Summary
  19. Chapter 13: Profiling for Performance
    1. What Is Performance?
    2. What Is Profiling?
    3. Instrumentation
    4. Problem Diagnosis
    5. Summary
  20. Chapter 14: Coding Conventions and Error Handling
    1. Why coding conventions?
    2. Formatting
    3. Dynamic Code
    4. Packages
    5. Stored Procedures
    6. Functions
    7. Error Handling
    8. Summary
  21. Chapter 15: Dependencies and Invalidations
    1. Dependency Chains
    2. Shortening Dependency Chains
    3. Datatype Reference
    4. View for Table Alterations
    5. Adding Components into Packages
    6. Synonyms in Dependency Chains
    7. Resource Locking
    8. Forcing Dependency in Triggers
    9. Creating Triggers Disabled Initially
    10. Summary
  22. Index

Product information

  • Title: Expert PL/SQL Practices for Oracle Developers and DBAs
  • Author(s): John Beresniewicz, Adrian Billington, Martin Büchi, Melanie Caffrey, Ron Crisco, Lewis Cunningham, Dominic Delmolino, Sue Harper, Torben Holm, Connor McDonald, Arup Nanda, Stephan Petit, Michael Rosenblum, Robyn Sands, Riyaj Shamsudeen
  • Release date: July 2011
  • Publisher(s): Apress
  • ISBN: 9781430234852