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
Author(s): Stephen Morris
Edition: 1
Publisher: O'Reilly Media
Year: 2023
Language: English
Commentary: Publisher PDF | Revision History: 2023-05-10: First Release
Pages: 305
City: Sebastopol, CA
Tags: Oracle; PL/SQL; SQL; Antipatterns; Anti-patterns
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
