Resilient Oracle PL/SQL

Resilient Oracle PL/SQL

by Stephen B. Morris
Released May 2023
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781098134112

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.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

As legacy and other critical systems continue to migrate online, the need for continuous operation is imperative. Code has to handle data issues as well as hard external problems today, including outages of networks, storage systems, power, and ancillary systems. This practical guide provides system administrators, DevSecOps engineers, and cloud architects with a concise yet comprehensive overview on how to use PL/SQL to develop resilient database solutions.

Integration specialist Stephen B Morris helps you understand the language, build a PL/SQL toolkit, and collect a suite of reusable components and patterns. You'll dive into the benefits of synthesizing the toolkit with a requirements-driven, feature-oriented approach and learn how to produce resilient solutions by synthesizing the PL/SQL toolkit in conjunction with a scale of resilience.

  • Build solid PL/SQL solutions while avoiding common PL/SQL antipatterns
  • Learn why embedding complex business logic in SQL is often a brittle proposition
  • Learn how to recognize and improve weak PL/SQL code
  • Verify PL/SQL code by running data-driven, in-database tests
  • Understand the safe operation, maintenance, and modification of complex PL/SQL systems
  • Learn the benefits of thinking about features rather than just use cases
  • Define good requirements for PL/SQL and hybrid solutions involving PL/SQL and high level languages

Publisher resources

View/Submit Errata

Table of contents

  1. Preface
    1. Who This Book Is For
    2. Requirements Orientation
    3. Toward Strategic Coding
    4. Resilient Software Is a Journey, Not a Destination
    5. A Scale of Resilience
    6. Resilient Solutions and Disaster Recovery
    7. A Diagram-Driven Narrative
    8. Conventions Used in This Book
    9. Using Code Examples
    10. Legal Notices
    11. O’Reilly Online Learning
    12. How to Contact Us
    13. Acknowledgments
  2. I. Creating a PL/SQL Toolbox
  3. 1. Resilient Software and PL/SQL
    1. Resilient Software
    2. Examples of Resilient Systems
      1. Requirements for Resilience: What Versus How
      2. Motivation for Using PL/SQL: Don’t Cut a Pizza with a Wrench
      3. Learning Java ORM
      4. Complex Data-Centric Workflows
      5. Using SQL for Complex Business Logic Is a Bad Idea
    3. A Cautionary Tale
      1. Embracing PL/SQL Abstractions
      2. Disadvantages of PL/SQL
      3. There Is a Better Way
    4. Gaining a Basic Understanding of PL/SQL
      1. How to Read Existing or Legacy PL/SQL Code
      2. Cursors
    5. Understanding the Need for a PL/SQL Learning and Development Environment
    6. The Scale of Resilience
      1. Capture All Errors and Exceptions: Score = 0
      2. Recoverability: Score = 2
      3. Observability: Score = 0
      4. Modifiability: Score = 5
      5. Modularity: Score = 2
      6. Simplicity: Score = 5
      7. Coding Conventions: Score = 5
      8. Reusability: Score = 2
      9. Repeatable Testing: Score = 2
      10. Avoiding Common Antipatterns: Score = 0
      11. Schema Evolution: Score = 0
    7. Summary
  4. 2. Installation of a Containerized Oracle Database Instance and SQL Developer
    1. A Virtualized Oracle Database Installation
    2. Getting Started with Docker
    3. Configuring Your Oracle Database
    4. Updating the User Password
    5. Getting Started with SQL Developer
    6. Recap of the Basic Docker Workflow
    7. Running SQL Developer
    8. A Simple Schema
    9. Running Some PL/SQL Code
    10. Three Docker Gotchas
      1. 1. Docker Case-Sensitivity
      2. 2. Connecting to the Database Too Soon
      3. 3. A Docker Issue Caused by the Dreaded Windows Updates
    11. An Alternative to the Command-Line Use of Docker
    12. Another Alternative to the Command-Line Use of Docker
    13. Summary
  5. 3. Taking SQL Developer for a Drive
    1. Fixing the Pesky PL/SQL Error
    2. Installing a PL/SQL Procedure in the Database
    3. Installing the Stored Procedure in the Database
    4. Executing the PL/SQL Procedure
    5. The Takeaway: Errors Are Good Teachers
    6. Summary
  6. 4. Applying the Scale of Resilience to the PL/SQL Code
    1. Scale of Resilience Requirement 1: Capture All Errors and Exceptions
      1. The Changes for Exception Handling
      2. Running the Updated PL/SQL
    2. Scale of Resilience Requirement 2: Recoverability
    3. Scale of Resilience Requirement 3: Observability
      1. What to Do If Logging Hits an Exception?
      2. Updated Schema
    4. Scale of Resilience Requirement 4: Modifiability
    5. Scale of Resilience Requirement 5: Modularity
      1. A Logging Package
      2. Creating a Logging Package
      3. Integrating the Logging Facility
    6. Scale of Resilience Requirement 6: Simplicity
    7. Scale of Resilience Requirement 7: Coding Conventions
    8. Scale of Resilience Requirement 8: Reusability
    9. Scale of Resilience Requirement 9: Repeatable Testing
      1. Installing utPLSQL
      2. Creating a utPLSQL Unit Test in SQL Developer
    10. Running a utPLSQL Unit Test
    11. Scale of Resilience Requirement 10: Avoiding Common Antipatterns
      1. Code That Is Too Long
      2. Overly Complex Code
      3. Lack of Exception/Error Handling
      4. Exceptions That You Decide to Live With
      5. Lack of Modularity
      6. Crashes with No Known Root Cause
    12. Scale of Resilience Requirement 11: Schema Evolution
    13. Scale of Resilience Change Summary
    14. Summary
  7. II. Feature-Driven Development
  8. 5. Calling PL/SQL
    1. Two PL/SQL Integration Models
    2. Calling PL/SQL Using a Job
    3. Calling PL/SQL Using a Java Program
      1. Setting Up a Simple Java Application
      2. Running the Java Application
      3. Who Owns the Java Error?
      4. Reading Database Metadata
      5. Some Java PL/SQL Gotchas
      6. Oracle Database Password Gotcha
    4. Summary
  9. 6. Introducing Feature-Driven PL/SQL Development
    1. What Is a Feature?
      1. A SQL Developer Feature
      2. Examples of Features
    2. Anatomy of a Feature
      1. Articulating the Common Purpose of the Feature
      2. Allowing the Definition of the Useful Work the Feature Can Do
      3. Defining the Feature Helpers and Invocation Mechanism
      4. Defining Tests to Exercise the Feature and Its Components
      5. Identifying the Requirements for the Feature and Its Components
    3. Reimagining the Log Procedure as a Feature
    4. Considering the update_employees Procedure as a Feature
    5. Testing Features or Vertical Slices
    6. Business-Critical PL/SQL Code
    7. Other Aspects of Features
      1. Features and System Health
      2. Features Versus Endpoints
    8. Summary
  10. III. Synthesis of the PL/SQL Toolbox and Feature-Driven Development
  11. 7. A Process-Centric, Feature-Based Mini Project
    1. Requirement 1: Define the Requirements
    2. Requirement 2: Build a Basic Invoice Outline
    3. Requirement 3: Create a Schema
    4. Requirement 4: Test the Schema
    5. Requirement 5: Feature Description
    6. Requirement 6: The PL/SQL Toolbox Elements Needed to Build a Solution
    7. Requirement 7: Tests
    8. Requirement 8: Building a Resilient PL/SQL Solution
    9. Requirement 9: Verifying the Solution by Invoking It from a High-Level Language
    10. Summary
  12. 8. From What to How: Building Feature Components from the Specification
    1. The Requirements
    2. Define the Requirements
    3. Build a Basic Invoice Outline
    4. Create the Schema
    5. A PROJECTS Table
    6. Test the Schema
      1. Insert Some Data and Verify the Constraints
      2. Verify That an Insert into the PROJECTS Table Works as Expected
    7. Feature Workflow Description
      1. Create a Project Invoice Data Set
      2. Invoke the Feature from Java Code
      3. Invoke the PL/SQL Procedure Passing the Required Data
      4. Insert a Call to the New Invoice Creation PL/SQL Procedure
      5. Run the Draft PL/SQL Code First in SQL Developer
      6. Insert a Row in the INVOICES Table
      7. Add Two Helpers
      8. Performance Note: PL/SQL Versus SQL
      9. Quick Project Status Report
      10. Project Feature Status Report Revisited
    8. Summary
  13. 9. Building the Complete Feature: Final Project Integration
    1. Revisiting the Scale of Resilience
    2. Revisiting the Schema with Reference to the Model
    3. Back to Error and Exception Management
    4. Resilience Requirements Checklist
    5. Recoverability
    6. Observability
    7. Modifiability
    8. Modularity
    9. Tests
    10. Build a Basic Invoice Creation Tool
    11. Add Logging
    12. Call the Logging PL/SQL Procedure from Java Code
    13. Summary
  14. 10. Conclusion
    1. Some Takeaways
    2. Avoiding Egregious Antipatterns
    3. Docker: A Technology to Learn Oracle Database
    4. Errors as Teachers and Reading Code
    5. Legacy Code and the Scale of Resilience
    6. PL/SQL and High-Level Language Integration
    7. Features as Coarse-Grained Placeholders
    8. Summary
  15. A. Troubleshooting a Native Oracle Installation
    1. Installation Procedure
      1. Container Versus Pluggable Databases
      2. Table Structure
      3. Is the Oracle Service Running?
  16. B. Additional Options for Oracle Database Logging
    1. The sys.dbms_system.ksdwrt Procedure
    2. The dbms_adr and dbms_adr_app Packages
    3. The Error Stack
  17. Index
  18. About the Author

Product information

  • Title: Resilient Oracle PL/SQL
  • Author(s): Stephen B. Morris
  • Release date: May 2023
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781098134112