Resilient Oracle PLSQL Building Resilient Database Solutions for Continuous Operation (Stephen Morris) (Z-Library)

Resilient O ra cle PL/SQ L Resilient O ra cle PL/SQ L Stephen B. Morris Resilient Oracle PL/SQL Building Resilient Database Solutions for Continuous Operation

DATA “Stephen provides a good method. Even if you don’t implement this completely, you’ll find tips and tools that can help you with your projects.” —Patrick Barel ACE Director, Senior Oracle Developer, Qualogy “I highly recommend this book. It offers invaluable advice for avoiding and simplifying complex troubleshooting issues.” —Sayan Malakshinov Oracle ACE “The book is superb and very practical. The feature orientation is both novel and easily applied.” —Yash Agrawal Consultant and contractor Resilient Oracle PL/SQL Twitter: @oreillymedia linkedin.com/company/oreilly-media youtube.com/oreillymedia As legacy and other critical systems continue to migrate online, the need for continuous operation is imperative. Today, code has to handle data issues as well as hard external problems, including network outages, storage systems, power, and ancillary systems. This practical guide provides system administrators, DevSecOps engineers, and cloud architects with a concise yet comprehensive overview of using 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 combining the toolkit with a requirements-driven, feature-oriented approach and learn how to produce resilient solutions 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 safe operation, maintenance, and modification of complex PL/SQL systems • Learn the benefits of thinking about features rather than just use cases Stephen B. Morris is an independent writer and consultant based in Ireland. Widely experienced in enterprise development and networking applications, he’s been writing code professionally for 30 years, and his technology journey has taken him to various industries, including telecoms, finance, healthcare, and government. US $79.99 CAN $99.99 ISBN: 978-1-098-13411-2

Stephen B. Morris Resilient Oracle PL/SQL Building Resilient Database Solutions for Continuous Operation Boston Farnham Sebastopol TokyoBeijing

978-1-098-13411-2 [LSI] Resilient Oracle PL/SQL by Stephen B. Morris Copyright © 2023 Omey Communications Limited. All rights reserved. Printed in the United States of America. Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://oreilly.com). For more information, contact our corporate/institutional sales department: 800-998-9938 or corporate@oreilly.com. Acquisitions Editor: Andy Kwan Development Editor: Angela Rufino Production Editor: Katherine Tozer Copyeditor: Justin Billing Proofreader: Audrey Doyle Indexer: Ellen Troutman-Zaig Interior Designer: David Futato Cover Designer: Karen Montgomery Illustrator: Kate Dullea May 2023: First Edition Revision History for the First Edition 2023-05-10: First Release See http://oreilly.com/catalog/errata.csp?isbn=9781098134112 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. Resilient Oracle PL/SQL, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc. The views expressed in this work are those of the author and do not represent the publisher’s views. While the publisher and the author have used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the author disclaim all responsibility for errors or omissions, including without limitation responsibility for damages resulting from the use of or reliance on this work. Use of the information and instructions contained in this work is at your own risk. If any code samples or other technology this work contains or describes is subject to open source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use thereof complies with such licenses and/or rights.

Always and ever for Siobhán.

Table of Contents Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi Part I. Creating a PL/SQL Toolbox 1. Resilient Software and PL/SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Resilient Software 5 Examples of Resilient Systems 5 Requirements for Resilience: What Versus How 6 Motivation for Using PL/SQL: Don’t Cut a Pizza with a Wrench 7 Learning Java ORM 9 Complex Data-Centric Workflows 9 Using SQL for Complex Business Logic Is a Bad Idea 11 A Cautionary Tale 11 Embracing PL/SQL Abstractions 13 Disadvantages of PL/SQL 14 There Is a Better Way 15 Gaining a Basic Understanding of PL/SQL 15 How to Read Existing or Legacy PL/SQL Code 15 Cursors 18 Understanding the Need for a PL/SQL Learning and Development Environment 21 The Scale of Resilience 21 Capture All Errors and Exceptions: Score = 0 23 Recoverability: Score = 2 23 Observability: Score = 0 23 Modifiability: Score = 5 23 Modularity: Score = 2 23 v

Simplicity: Score = 5 24 Coding Conventions: Score = 5 24 Reusability: Score = 2 24 Repeatable Testing: Score = 2 24 Avoiding Common Antipatterns: Score = 0 24 Schema Evolution: Score = 0 25 Summary 25 2. Installation of a Containerized Oracle Database Instance and SQL Developer. . . . . . . 27 A Virtualized Oracle Database Installation 27 Getting Started with Docker 29 Configuring Your Oracle Database 35 Updating the User Password 37 Getting Started with SQL Developer 37 Recap of the Basic Docker Workflow 42 Running SQL Developer 43 A Simple Schema 45 Running Some PL/SQL Code 46 Three Docker Gotchas 49 1. Docker Case-Sensitivity 49 2. Connecting to the Database Too Soon 49 3. A Docker Issue Caused by the Dreaded Windows Updates 50 An Alternative to the Command-Line Use of Docker 50 Another Alternative to the Command-Line Use of Docker 52 Summary 53 3. Taking SQL Developer for a Drive. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Fixing the Pesky PL/SQL Error 55 Installing a PL/SQL Procedure in the Database 60 Installing the Stored Procedure in the Database 61 Executing the PL/SQL Procedure 62 The Takeaway: Errors Are Good Teachers 65 Summary 66 4. Applying the Scale of Resilience to the PL/SQL Code. . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Scale of Resilience Requirement 1: Capture All Errors and Exceptions 69 The Changes for Exception Handling 70 Running the Updated PL/SQL 71 Scale of Resilience Requirement 2: Recoverability 79 Scale of Resilience Requirement 3: Observability 80 What to Do If Logging Hits an Exception? 82 Updated Schema 82 vi | Table of Contents

Scale of Resilience Requirement 4: Modifiability 85 Scale of Resilience Requirement 5: Modularity 88 A Logging Package 94 Creating a Logging Package 95 Integrating the Logging Facility 98 Scale of Resilience Requirement 6: Simplicity 101 Scale of Resilience Requirement 7: Coding Conventions 102 Scale of Resilience Requirement 8: Reusability 103 Scale of Resilience Requirement 9: Repeatable Testing 105 Installing utPLSQL 106 Creating a utPLSQL Unit Test in SQL Developer 108 Running a utPLSQL Unit Test 110 Scale of Resilience Requirement 10: Avoiding Common Antipatterns 119 Code That Is Too Long 121 Overly Complex Code 121 Lack of Exception/Error Handling 122 Exceptions That You Decide to Live With 122 Lack of Modularity 122 Crashes with No Known Root Cause 123 Scale of Resilience Requirement 11: Schema Evolution 124 Scale of Resilience Change Summary 127 Summary 129 Part II. Feature-Driven Development 5. Calling PL/SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Two PL/SQL Integration Models 133 Calling PL/SQL Using a Job 134 Calling PL/SQL Using a Java Program 138 Setting Up a Simple Java Application 138 Running the Java Application 140 Who Owns the Java Error? 143 Reading Database Metadata 144 Some Java PL/SQL Gotchas 145 Oracle Database Password Gotcha 145 Summary 146 6. Introducing Feature-Driven PL/SQL Development. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 What Is a Feature? 147 A SQL Developer Feature 148 Examples of Features 151 Table of Contents | vii

Anatomy of a Feature 154 Articulating the Common Purpose of the Feature 155 Allowing the Definition of the Useful Work the Feature Can Do 156 Defining the Feature Helpers and Invocation Mechanism 157 Defining Tests to Exercise the Feature and Its Components 157 Identifying the Requirements for the Feature and Its Components 157 Reimagining the Log Procedure as a Feature 158 Considering the update_employees Procedure as a Feature 160 Testing Features or Vertical Slices 161 Business-Critical PL/SQL Code 162 Other Aspects of Features 163 Features and System Health 163 Features Versus Endpoints 164 Summary 166 Part III. Synthesis of the PL/SQL Toolbox and Feature-Driven Development 7. A Process-Centric, Feature-Based Mini Project. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 Requirement 1: Define the Requirements 170 Requirement 2: Build a Basic Invoice Outline 170 Requirement 3: Create a Schema 173 Requirement 4: Test the Schema 180 Requirement 5: Feature Description 181 Requirement 6: The PL/SQL Toolbox Elements Needed to Build a Solution 182 Requirement 7: Tests 182 Requirement 8: Building a Resilient PL/SQL Solution 182 Requirement 9: Verifying the Solution by Invoking It from a High-Level Language 183 Summary 183 8. From What to How: Building Feature Components from the Specification. . . . . . . . 185 The Requirements 186 Define the Requirements 189 Build a Basic Invoice Outline 190 Create the Schema 191 A PROJECTS Table 194 Test the Schema 196 Insert Some Data and Verify the Constraints 196 Verify That an Insert into the PROJECTS Table Works as Expected 197 Feature Workflow Description 198 Create a Project Invoice Data Set 198 viii | Table of Contents

Invoke the Feature from Java Code 199 Invoke the PL/SQL Procedure Passing the Required Data 199 Insert a Call to the New Invoice Creation PL/SQL Procedure 199 Run the Draft PL/SQL Code First in SQL Developer 201 Insert a Row in the INVOICES Table 202 Add Two Helpers 204 Performance Note: PL/SQL Versus SQL 211 Quick Project Status Report 212 Project Feature Status Report Revisited 215 Summary 217 9. Building the Complete Feature: Final Project Integration. . . . . . . . . . . . . . . . . . . . . . 219 Revisiting the Scale of Resilience 219 Revisiting the Schema with Reference to the Model 221 Back to Error and Exception Management 225 Resilience Requirements Checklist 236 Recoverability 236 Observability 237 Modifiability 237 Modularity 237 Tests 243 Build a Basic Invoice Creation Tool 246 Add Logging 246 Call the Logging PL/SQL Procedure from Java Code 248 Summary 251 10. Conclusion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253 Some Takeaways 255 Avoiding Egregious Antipatterns 257 Docker: A Technology to Learn Oracle Database 258 Errors as Teachers and Reading Code 258 Legacy Code and the Scale of Resilience 259 PL/SQL and High-Level Language Integration 259 Features as Coarse-Grained Placeholders 259 Summary 260 A. Troubleshooting a Native Oracle Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261 B. Additional Options for Oracle Database Logging. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271 Table of Contents | ix

Preface Relational databases remain one of the most successful technologies of all time. There have been numerous challenges to relational databases over the years. For example, object-oriented databases came along in the late 1990s and were anticipated to sup‐ plant the dominant relational model. Nearly a quarter century later, relational systems are in as strong a position as ever, and there are many reasons for this continued success. Also among the more recent newcomers are the NoSQL products, such as MongoDB. However, for a variety of reasons, relational databases continue to hold their promi‐ nent position in the IT landscape. A solid knowledge of relational databases, such as those from Oracle, IBM, Microsoft, and others, remains a good addition to the IT skill set. The same is true for the open source relational databases, such as MySQL. Pretty much every developer job I’ve done has required at least some knowledge of SQL. Some jobs have also required knowledge of stored procedure languages, such as PL/SQL. As you’ll see in the book, there is often a great deal of confusion about the role of SQL and PL/SQL in the context of high-level languages, such as Java, C#, JavaScript, and so on. I discuss some of the more common antipatterns that can arise in these multi‐ language domains. For a variety of reasons, there is often a strong motivation for adopting solutions based exclusively on embedded SQL and object relational mapping solutions rather than using PL/SQL. This may, in turn, result in the inadvertent use of antipatterns and consequently brittle code. I look closely at Oracle PL/SQL with a view to helping lay the foundations for creat‐ ing resilient database solutions. The need for resilient systems has never been greater in our increasingly data-driven and data-centric world. xi

Who This Book Is For If you’re interested in learning something about databases and PL/SQL, this book is a good place to start. It takes a first-principles approach, so not much foundational knowledge is required. The focus is on a good practice in PL/SQL, and the use of requirements analysis and metrics should be helpful to readers of all technical levels. One particular audience that would find the book useful is employees of organiza‐ tions that use a mix of languages, such as Java, JavaScript, C#, and so on. Often, such organizations also use one or more database technologies including PL/SQL. While their systems run PL/SQL every day, the developers may not use PL/SQL exclusively. In other words, PL/SQL is not the main development language. Because the developers use PL/SQL on an infrequent basis, they may not employ optimal solutions in terms of language constructs and abstractions. The results are often disappointing and far from resilient. This can also add to growing DevOps costs with little hope of improvement. The latter may even be exacerbated with the advent of autonomous technology inside Oracle products. In other words, the move by Ora‐ cle toward autonomous mechanisms may have the unwelcome effect of placing a spotlight on poorly crafted database solutions. I believe this book has much to offer people who are looking at PL/SQL for the first time as well as experienced PL/SQL developers. In addition to batch-style PL/SQL solutions, I also look at the knotty area of calling PL/SQL from within Java. Requirements Orientation Throughout the book, I try to formulate simple, broad design and coding require‐ ments. These requirements are articulated in advance of diving into the details of implementation, which provides a blueprint for the code that follows and is a good general practice in terms of guiding the coding procedure. Articulating good requirements is a powerful skill in software development in general and this helps with programming in any language, not just PL/SQL. As I’ve often stated in the past in my writing, requirements analysis and design are a necessary pre‐ requisite to solid coding. It is an old adage that the sooner you start to code, the longer your project will take. This is as true in 2023 as it was in the old days. Some of the requirements orientation adopted in these pages can be considered to be in the style of nonfunctional requirements. That is, the requirements are not specific to a given business problem or feature change. Rather, the resilience requirements described here are a guide to creating a solution that is resilient overall. xii | Preface

Toward Strategic Coding Over the course of many years of IT work in numerous industries, it’s my opinion that the bulk of modern coding tends to be tactical in nature. Many organizations have feature requirement lists that are so long they are effectively unachievable. This is quite a serious threat to the underlying business, which needs these new or modi‐ fied features to fulfill its work. A coding approach that resembles firefighting can easily become the norm, particu‐ larly where a weak process exists. This type of model is rarely sustainable and may even contribute to excessive developer turnover, which may in turn exacerbate the problem. Adopting a more requirements-driven mindset can allow for a more strategic approach to coding. In the PL/SQL code examples, I aim to illustrate this mode of working. A key aspect of strategic coding is embracing certain nonfunctional require‐ ments. As we’ll see, examples of strategic coding include coding for observability and modifiability. Beyond these needs, the strategic coding style embraces simplicity, modularity, and other attributes. Why is code simplicity important? To answer this question, one need look no further than the average codebase. Most codebases now incorporate at least one mainstream framework. I first started using frameworks such as Spring around 2007 and I was struck by the complexity. It’s just my opinion, but I believe this framework eventually became so complex that it had to be substantially reworked as part of Spring Boot (which I’ve also used). However, Spring Boot attempts to hide much of the old Spring complexity. I’ve worked on Spring Boot projects where integration required debug‐ ging into the framework itself. This does not lend itself to simple coding and often involves a group of developers working together to try a variety of options to get the code to work. Simple, it is not. As part of a strategic PL/SQL coding style, Parts II and III describe feature-driven development used as a means of modeling a macroscopic view of system capability. I believe that feature-driven development provides a way of getting underway with resilient coding. Resilient Software Is a Journey, Not a Destination Every new requirement and the associated code has an impact on the resiliency of the overall solution. It’s difficult to simply request resilient software. We can’t just say “Please ensure that this new feature is resilient.” Why not? Well, we don’t yet know what resilience is and how it applies to our code. Preface | xiii

To begin to tackle these complex issues, the book starts by carefully describing what resilience is about. Even the largest trillion-dollar organizations are struggling to pro‐ duce resilient code. The difficulty is that resilience is generally judged by the degree of success of the interaction of multiple systems of software during periods of stress on those systems. So, system X may be resilient, but if system Y is not resilient, then it’s likely that the overall system (X + Y) will not be resilient. Disaster recovery cycle times for large corporate entities can now (in 2023) be in the range of 45 minutes or less. That’s not a whole lot of time to get your code back up and running even in a depleted or impaired state where upstream or downstream sys‐ tems are temporarily unavailable. To ameliorate this difficulty in defining resilience, I introduce a scale of resilience. This is a simple set of rules that can be used to inform the design and coding effort. We aim to eliminate these five common antipatterns: • Complexity • Brittle code • Future code maintenance cost • Poor performance • Poor separation of concerns That sounds like an extravagant claim! How do we tackle these antipatterns? To do this, we need some sort of scale of resilience: 1. Ability to capture all errors and exceptions 2. Recoverability 3. Observability 4. Modifiability 5. Modularity 6. Simplicity 7. Coding conventions 8. Reusability 9. Repeatable testing 10. Avoiding common antipatterns 11. Schema evolution So, what’s in a scale of resilience? xiv | Preface

A Scale of Resilience I introduce a scale for numerically designating the resilience of a database solution. While being purely empirical, such a scale illustrates the difference between brittle use of PL/SQL and a more resilient approach. By improving the score for a given block of PL/SQL, the code becomes more resilient. Using the scale in an iterative fashion can help you to arrive at a powerful approach to PL/SQL refactoring. There are other reasons for trying to make your PL/SQL solutions more resilient. For example, Oracle is increasingly introducing additional technology in order to improve the underlying resilience of its products. Examples of such resilience include (among others): • Self-healing elements • Autonomous database technology • Avoiding oversubscription in CPU, network, and memory use By embracing resilience in your PL/SQL work, you are to some extent keeping up to date with Oracle’s efforts in this area. This makes your code a better citizen in systems built using these Oracle products. Your resilient code will sit more comfortably in the increasingly resilient Oracle ecosystem. Resilient Solutions and Disaster Recovery In our increasingly interconnected world, IT disasters are an unfortunate fact of life. Outages are commonplace. One aspect of the quality of an organization’s IT is the speed with which it recovers from a disaster. An example is a data center going offline for some reason, which can cause significant loss of service to an organization. This is the macroscopic level of resilience. Large organizations stand to lose a lot from outages. These can be financial losses and/or reputational losses. In either case, many such organizations will have disaster recovery targets, such as x minutes of allowed downtime per year. Even for very large organizations, the value of x can be surprisingly low. During a disaster recovery, get‐ ting code back up and running quickly is highly important. It’s important to realize that you don’t want your code negatively affecting the value of x. For example, if your code fails to run after an outage resolution, it could be crucial to have adequate source code logging in place in order to track down the issue. This is especially so for the case when the problem is in a dependent downstream or upstream system which is outside of your direct control. If your logging provides a solid clue to the root cause of an issue, then it is entirely possible that your colleagues will thank you for this information during an outage remediation. Preface | xv

Every line of code can contribute positively or negatively to the overall resilience of an organization. This is the microscopic level of resilience. While this is a vast area and worthy of a book in its own right, I will look in depth at the topic of writing resil‐ ient PL/SQL and its importance in helping the overall organization produce resilient solutions. A Diagram-Driven Narrative I’m a strong believer in the power of diagrams as a means to clearly describe ideas and code workflows. The book leans heavily on screenshots in order to illustrate the real workings of the code described. I hope that this narrative style helps readers. Conventions Used in This Book The following typographical conventions are used in this book: Italic Indicates new terms, URLs, email addresses, filenames, and file extensions. Constant width Used for program listings, as well as within paragraphs to refer to program ele‐ ments such as variable or function names, databases, data types, environment variables, statements, and keywords. Constant width bold Shows commands or other text that should be typed literally by the user. <Constant width between brackets> Shows text that should be replaced with user-supplied values or by values deter‐ mined by context. This element signifies a tip or suggestion. This element signifies a general note. xvi | Preface

This element indicates a warning or caution. Using Code Examples Supplemental material (code examples, exercises, etc.) is available for download at https://github.com/stephenbjm/plsql-resilience. If you have a technical question or a problem using the code examples, please send email to support@oreilly.com. This book is here to help you get your job done. In general, if example code is offered with this book, you may use it in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission. We appreciate, but generally do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Resilient Oracle PL/SQL by Stephen B. Morris (O’Reilly). Copyright 2023 Omey Communications Limited, 978-1-098-13411-2.” If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at permissions@oreilly.com. Legal Notices All screenshots from Oracle products (SQL Developer, etc.) are subject to the follow‐ ing notice from Oracle: Copyright © Oracle and its affiliates. Used with permission. The SQL scripts used in Part III are derived from content on GitHub at https:// github.com/oracle-samples/db-sample-schemas and are subject to the following notice from Oracle: Copyright © 2019 Oracle. Screenshots from IntelliJ IDEA are subject to the following: Copyright © 2022 Jet‐ Brains s.r.o., used with permission. JetBrains IntelliJ IDEA and the IDEA logo are registered trademarks of JetBrains s.r.o. Preface | xvii

O’Reilly Online Learning For more than 40 years, O’Reilly Media has provided technol‐ ogy and business training, knowledge, and insight to help companies succeed. Our unique network of experts and innovators share their knowledge and expertise through books, articles, and our online learning platform. O’Reilly’s online learning platform gives you on-demand access to live training courses, in-depth learning paths, interactive coding environments, and a vast collection of text and video from O’Reilly and 200+ other publishers. For more information, visit https://oreilly.com. How to Contact Us Please address comments and questions concerning this book to the publisher: O’Reilly Media, Inc. 1005 Gravenstein Highway North Sebastopol, CA 95472 800-889-8969 (in the United States or Canada) 707-829-7019 (international or local) 707-829-0104 (fax) support@oreilly.com https://www.oreilly.com/about/contact.html We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at https://oreil.ly/resilient-oracle-pl-sql. For news and information about our books and courses, visit https://oreilly.com. Find us on LinkedIn: https://linkedin.com/company/oreilly-media. Follow us on Twitter: https://twitter.com/oreillymedia. Watch us on YouTube: https://youtube.com/oreillymedia. Acknowledgments My editor, Corbin Collins, guided this project and helped me with advice on a multi‐ tude of thorny issues, such as the different handling of links between the print and electronic versions of the book. Thanks Corbin! xviii | Preface