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