Resilient Oracle PL/SQL: Building Resilient Database Solutions for Continuous Operation

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