Chapter 1. Resilient Software and PL/SQL

Writing resilient code has never been more important. As of early 2023, most organizations that use substantial amounts of code and IT infrastructure are facing the ongoing need to do some or all of the following:

  • Add new features.

  • Fix bugs in existing features.

  • Keep up to date with regulatory changes.

  • Add new channels, such as mobile platforms.

  • Migrate existing systems to use more modern tools, languages, and/or platforms.

  • Adopt microservices.

  • Improve security.

  • Migrate to the cloud.

  • Reduce DevOps costs.

Even organizations that only use code—without originating it—also face most of these issues. This includes businesses that use a lot of third-party IT products and infrastructure. One key difficulty for this type of organization appears when new requirements materialize, such as a third-party security update. Imagine the extent of the difficulties if a third-party payment gateway mandates a security update. This is not to mention the need for new business features in existing products and services. Other drivers of change can include regulatory updates, other service provider updates, and so on. IT is not getting easier and, unlike wine, it tends not to improve with age.

Regulatory change can be quite a challenge. For example, adding money laundering checks to ecommerce workflows can involve the insertion of callouts to multiple third-party providers even before fulfilling orders. Another part of this type of change relates to the need for insisting on specific customer data, such as a physical address, credit card owner details where the card is registered in the name of the purchaser, and so on. Falling foul of money laundering regulations can result in heavy fines.

Data protection is another area that requires vigilance. An example is the case of a dentist with a given customer who moves away. The customer then ceases to be a patient of the dentist. How long can the dentist hold the records for the now ex-patient? Well, in European countries, there are specific rules governing how long such records can be retained.

It’s something of a cliché to say that the one thing modern enterprises can rely on is the need for constant change. However, it’s not all doom and gloom. The adoption of resilient techniques can help improve the level of confidence that organizations have in their IT solutions.

It should be noted that resilience doesn’t come about by accident or by just incorporating some new programming language or framework. One organizational aspect that appears to impede resilient solutions is that of excessive hierarchy. Imposing hard boundaries between development teams is, in my experience, inimical to the needs of resilience. Such boundaries may tend to diminish the close cooperation that is such a key part of achieving resilient development.

Resilience is a byproduct of a more considered approach to software development, doing things such as openly discussing algorithms, casual testing, anticipating exceptions, sharing knowledge, and so on. But I’m getting ahead of myself! Let’s back up a little and look at the aims of this introductory chapter.

After reading this chapter, you should be able to do the following:

  • Broadly understand what “resilient software” means.

  • Understand the requirements for resilience: what versus how.

  • Understand why PL/SQL is a good idea.

  • Appreciate why using SQL for business logic is generally a bad idea.

  • Know some of the disadvantages of PL/SQL.

  • Gain a basic understanding of PL/SQL.

  • Read existing or legacy PL/SQL code.

  • Understand the need for a PL/SQL learning and development environment.

  • Understand a basic scale of resilience.

Let’s briefly look at what resilient software is about.

Resilient Software

The dictionary definition of resilience tends to reflect the ability of a software system to operate under stress or to absorb the impact of a problem. Maintaining stability and failing gracefully are other attributes of resilience where an acceptable service level continues to be offered to the business even when problems occur.

It should also be possible to know the state of the resilient system, e.g., what is the state—consistent or otherwise—if a long batch job fails halfway through? Having reliable information about the consistency then allows us to answer questions such as whether we should restart the batch job from the beginning or whether we should pick it up where we left off. An important part of resilience is knowing the state of play, i.e., knowing the level of progress of jobs and workflows. Additionally, if failures occur, you can find out how to get back up and running, or better yet, the software automatically restarts itself.

In a nutshell, resilient software is composed of rock-solid code that runs forever (i.e., decades). Such code is very difficult to write because it has to cope with all sorts of business requirement changes, unforeseen error conditions, and potentially unexpected input data. Add to this the need for ongoing updates in the form of security fixes and new features. Finally, don’t forget that the runtime platform itself may come under strain as the workflows evolve. It takes a lot of effort to produce resilient code.

Another aspect of resilience is the need for new and legacy code to coexist in harmony. This is one of the great challenges of integration: it is essential that new code does not undermine systems and workflows that have run smoothly for many years.

Examples of Resilient Systems

Operating systems such as Linux, Windows, macOS, and so on are examples of systems that must be resilient. Much research and development effort in recent years has gone into making these operating systems more resilient than they used to be. The following are recent resilience improvements in operating systems:

  • Carefully managing the transition from operating system user mode into kernel mode and vice versa

  • Memory protection

  • Process isolation

  • Adding programming abstractions that facilitate these improvements

As you’ll see later, resilient code goes to great lengths to protect both itself and the runtime environment. This is typically achieved by making judicious use of language abstractions and other constructs.

Requirements for Resilience: What Versus How

I should note that even the world’s greatest developer will occasionally write code with bugs. Regarding bugs, PL/SQL is no different from any other programming language. In later chapters, I’ll look more closely at ways to structure PL/SQL to allow for a more resilient end result.

In the meantime, let’s establish some guiding principles or requirements. The merit of this approach is that it separates the what from the how. In other words, I will merely state what it is I want to achieve (i.e., resilient code) without making any demands on how that will occur in the implementation phase.

Differentiating between what and how helps avoid one of the most destructive of all modern software development habits: coding too soon. If you focus too much or too early on the how, then you miss out on an opportunity to elucidate exactly what you want the solution to achieve. By thinking about the what in this fashion, you can avoid complexity in the final offering.

Later in this chapter, you’ll see an example of an antipattern—the big block of SQL. We can replace the big block of SQL with a modular and more powerful block of PL/SQL.

Clearly separating the what from the how also lays the groundwork for future additions to code. This is because you have invested effort up front in articulating what it is you wish to achieve with the solution. Only then is the solution coded as part of the how stage of development.

To set the scene for what I’ll look at, the following are some broad coding requirements that can be useful in achieving 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

Do these 11 requirements just sound like commonsense programming practice? That’s exactly what they are, but they will provide a kind of mental framework for achieving resilience. We’ll revisit these requirements in detail later and illustrate them with concrete PL/SQL code examples.

Let’s dive into the motivation for learning and aiming to write resilient PL/SQL.

Motivation for Using PL/SQL: Don’t Cut a Pizza with a Wrench

I’m a big fan of stored procedures in general and PL/SQL in particular. Stored procedure technology provides many advantages over trying to implement complex database logic in application development languages such as Java, C#, JavaScript, and so on. I’ll discuss this later, but for now, just note that it’s not uncommon to see developers trying to shoehorn complex database logic into high-level languages.

The type of database logic I’m referring to here is where high-level code makes a large number of table-level changes across numerous databases, such as in a large batch job. The performance of this hybrid approach may be poor because it necessitates translation between the high-level language and the database environment. Worse yet is the issue of data consistency that can arise if a batch job only partially succeeds.

There may also be issues concerning excessive database locks, which can affect other users of the database (or databases). This is not to say that the hybrid approach is always bad. The point is that there are cases where the mix of high-level languages and complex database operations may result in a solution that is not resilient.

Perhaps even worse than this is the practice of using raw SQL (in high-level code) as a place for business logic. Using SQL in this way potentially misses the opportunity to incorporate the many useful PL/SQL abstractions and can result in brittle solutions. It’s a bad idea to try to cut a pizza with a wrench. It’s not that the wrench won’t cut the pizza; it’s just not going to lead to a good end result.

Before looking a little at some of the disadvantages of these high-level language approaches, I should note that I do also like object relational mapping (ORM) technology (such as Hibernate). I’ve used ORM in Java and C# (and even in Python) and I’ve written about it extensively over the years. ORM allows for a smooth experience of database technology in languages such as Java and C#. However, there are many cases where a PL/SQL solution may simply be a better option, particularly when we want to achieve resilient solutions.

PL/SQL code runs natively in the Oracle Database inside a dedicated runtime engine. This makes PL/SQL code a first-class citizen in the Oracle Database ecosystem. In other words, there is no need to translate from a Java (or other high-level language) layer into the database dialect. PL/SQL is itself a native Oracle Database technology, which means that it is optimized for this use and is tightly coupled with the database platform.

Another reason for favoring PL/SQL is security: the code runs inside the database. This reduces the likelihood of vulnerabilities such as leaking important data into Java logfiles. Other advantages of PL/SQL include speed and efficiency. As noted previously, with PL/SQL there is no intermediate high-level language technology layer. This is one reason why PL/SQL solutions will often substantially outperform an equivalent effort in Java or C#. The Oracle Database also works hard to optimize PL/SQL code. There are many benefits to using PL/SQL.

With a view to being as balanced as possible, is it hard to learn another method (other than PL/SQL), such as Java ORM?

Learning Java ORM

One of the reference books I used when I was learning about ORM about 10 years ago is over 800 pages long. It’s the seminal reference Java Persistence with Hibernate by Christian Bauer and Gavin King (Manning, 2007). It’s a really great book, filled with expert knowledge and lots of code examples, but it takes a great deal of energy and commitment to complete.

Nowadays, a lot of the effort in getting to grips with ORM is handled by development tools, frameworks, and programming language features and supports such as Java annotations. Getting started with this enterprise-critical technology is less work than it used to be, but it’s definitely not a trivial task.

Java-based ORM technology, such as Hibernate and Java Persistence API (JPA), necessarily aim at database portability and interoperability with a wide range of Java tools and technologies. This is a key difference between them and PL/SQL solutions. PL/SQL only has to work with Oracle products, which allows for a smaller footprint and an easier learning experience. Because PL/SQL is owned by Oracle, it is carefully managed across the different versions of the Oracle Database products.

Complex Data-Centric Workflows

Concerning the use of Java/C# ORM technologies, the main antipattern I’d avoid is using high-level languages to directly orchestrate complex data-driven workflows. In other words, the high-level language interacts with database objects. There’s no technical reason to avoid this, but it may result in poorer performance than a hybrid approach with both high-level language(s) and PL/SQL. An example of this is a large, multidatabase, nightly batch job where data is extracted from a wide range of tables, views, and ancillary systems (such as data warehouses).

There may instead be a strong combined business and technical case for doing the bulk of this type of work in a language such as PL/SQL. Why? One reason is that a PL/SQL solution can be considered to provide good separation of concerns. This is because all of the database work is handled in code that is close to (i.e., resides within) the database. It shouldn’t come as a surprise that Oracle Databases provide excellent support for PL/SQL. For example, scheduled jobs can be configured to run in isolation from other systems. Using these facilities in the database avoids the need to implement handcrafted, potentially fragile, high-level language alternatives.

Using PL/SQL skillfully can increase resilience because the heavy lifting is left inside the database. To summarize, the disadvantages of implementing extremely complex data processing batch jobs exclusively in high-level languages are:

  • Complexity

  • Brittle code

  • Future code maintenance cost

  • Poor performance

  • Poor separation of concerns

  • The fact that some high-level frameworks make use of database version–specific features

While most of these points are self-evident, the last one merits a mention. I’ve seen one case of a high-level language object mapping framework where it was only possible to get the database-centric application code to work by making use of a database driver version–specific feature.

At the time, it occurred to me that, aside from the unnecessary complexity and integration effort, the finished code was then tied to that particular version of the database driver. A change to the latter (e.g., for security reasons) might then result in breaking the application code. Mixing database infrastructure and logic in application code is not without its risks.

Tip

The rationale for using a high-level language technology ORM product might even be more political than technical. If the database business logic resides solely inside Java code, then there may be no need to communicate with the database administrators (DBAs). For security reasons, communication might still be required, though, when deploying stored procedure code to the database.

I’ve seen a case like this where a developer didn’t want to have to deal with DBAs and opted instead for a high-level language persistence layer solution. This approach was risky because it was not clear at the time of implementation if the solution would meet the organizational performance requirements. It is better to aim to deliver the best solution for the organization and not make technical decisions that are based on political considerations.

So, what are some of the advantages of using languages like PL/SQL? Here are a few:

  • Keeping database logic in the database

  • Security: PL/SQL code resides inside the database

  • Performance

  • Better management of database exceptions

  • Better management of database business logic errors

  • Encapsulation of business logic

  • Reuse (e.g., PL/SQL procedures, functions, and packages)

  • Separation of concerns

  • Easy access to programming abstractions

I’ll go through these key points in the later sections and chapters. They’re just introduced here to set the context for the discussion. However, I do want to emphasize that the first point about keeping database logic inside the database is extremely important and is often overlooked.

Let’s now look at why SQL is not a good choice for business logic. It’s also a little like trying to cut a pizza with a wrench.

Using SQL for Complex Business Logic Is a Bad Idea

With correct use of PL/SQL, you avoid mixing your application code with SQL. This also helps you to keep away from the knotty area of trying to express complex business logic in SQL scripts. SQL is not nearly as strong as PL/SQL in the area of error/exception handling. A small incoming data change can easily cause a SQL script to suddenly fail with little or no indication of the cause.

The use of SQL for business logic can produce extremely brittle solutions. I think it’s fair to view the use of SQL for business logic as something of an antipattern.

An example of this type of SQL is a multistep SQL script that attempts to merge data from a number of sources, while at the same time executing one or more complex joins and updates on other tables. This type of SQL construct can tend to grow over time as additions are made to accommodate new features and data changes. The end result can be unnecessary complexity.

As the SQL script grows over time, so too does the tendency for it to fail when, for example, someone attempts to insert rows into a table with the same primary key. This type of failed insertion may cause the whole SQL block to fail unexpectedly. In many cases, the failure is not noticed or even logged in the overall workflow.

PL/SQL helps avoid the antipattern by facilitating modular, procedural code that includes powerful exception handling.

A Cautionary Tale

I once saw a case where a large block of SQL was failing in a daily batch system (see Figure 1-1). The batch job was responsible for merging sales data from a range of source tables and views across a number of databases and other source systems. The end result was a report that was generated for use by a downstream finance team. The SQL was rather badly designed in that it was written to not expect any errors to occur.

rops 0101
Figure 1-1. An antipattern: big block of SQL with business logic and no error handling

Notice, at the top of Figure 1-1, the insertion of a PL/SQL BEGIN statement and a corresponding END with a COMMIT. In this context, the use of PL/SQL is just a mechanism for running the contained SQL script. I’ll look more closely at this as we explore the later examples, but the point is that while the example uses PL/SQL, it doesn’t really take advantage of the many benefits of the language. The intention was good but the execution left a lot to be desired.

All of the code between the BEGIN and END was just plain SQL with no error or exception handling at all. Having so many inserts, updates, deletes, and joins is really an accident just waiting to happen. Initially, the developers were delighted that their code was experiencing no errors. One could even see a little of the Dunning–Kruger effect as they began to tempt fate by saying things like “This PL/SQL stuff is pretty simple, just add a BEGIN, COMMIT, and END and you’re good to go.” The only problem was that the PL/SQL code was initially running successfully each night, i.e., no inconvenient errors. This initial success would prove to be short-lived.

Over time, with new incoming data and minor modifications to the script, the overly complex SQL block failed because of an attempted duplicate key insertion. This is a common enough error condition and one that it is prudent to prepare for in your PL/SQL code. As you’ll see in later chapters, it’s easy enough to add this type of error handling. But in the present case, once the SQL block failed, then the whole block was automatically rolled back. Sadly, the log table update was also part of the main transaction, so it too was rolled back. The difficulty with this technique is that any clues about the source of the error are also rolled back into oblivion. This would result in a great deal of developer angst.

The overall effect was that, after the first time the error appeared, it looked like one of the following had occurred:

  • The job didn’t run at all.

  • The job succeeded with no errors or data.

  • The job was rolled back.

It was hard to know which of these had happened. It’s a classic integration scenario where there is insufficient logging detail (and business workflow knowledge) to assist with any attempt at diagnosing the problem.

The job then continued to fail each night for a full week, and the issue only came to light when the downstream team began wondering why there were no sales figures for that week. In the week in question, many sales had been made and bonuses were due, but this was not known by the development team. The developers were still laboring under the misapprehension that no error had in fact occurred. After a few more days of no finance report data, the matter was escalated and it then became a development priority.

It was then quite embarrassing for the developers, who had to work late to attempt to manually cobble together the required report data. As they did not understand the business-specific data constraints and rules, this manual override approach then resulted in handcrafted changes to table data and even more errors in the final results. Worse still, some of the upstream data had changed by this time and the results were an erroneous mixture of old and new data.

Needless to say, the finance team was not impressed. Then, another downstream team from the finance group started asking for their consolidated data. It’s not hard to see that tempers then started to fray, emails started to fly, and the whole sorry affair deteriorated into a blame game. All because of some poorly crafted PL/SQL and SQL script.

This type of antipattern and the resultant panicky responses to user-reported errors does tend to undermine the faith that the end users have in the overall process. It can also undermine the confidence of the developers. This is a tough road for developers trying to produce resilient solutions and it’s entirely avoidable. In Part III, starting in Chapter 7, I’ll develop a similar but far more resilient workflow compared to the one just described.

Embracing PL/SQL Abstractions

Rather than trying to shoehorn business logic into SQL scripts, the inclusion of some fundamental PL/SQL programming abstractions allows for more powerful and resilient code constructions. For example, adding exception handling is one way of protecting your PL/SQL code from unexpected runtime exceptions, such as the attempted duplicate key insertion from “A Cautionary Tale”.

As you’ll see, well-crafted PL/SQL also facilitates observability, which helps avoid the problems just described where the critical sales data was missing. In other words, PL/SQL gives you the benefits of access to the constructs of a mature programming language. This in turn helps to move you away from such brittle SQL-based business logic and any need for handcrafted, error-prone data modifications.

Disadvantages of PL/SQL

What are some of the disadvantages of PL/SQL? All technologies have a total cost of ownership and there’s no such thing as a perfect language (or, indeed, a free lunch). A few of the disadvantages of PL/SQL are as follows:

  • PL/SQL is a legacy language.

  • PL/SQL is not a trendy language.

  • The use of PL/SQL may tie you to Oracle Database products.

  • People will ask why they can’t use SQL instead.

  • PL/SQL is often perceived as being difficult to learn.

  • Many developers don’t want to learn yet another language (i.e., developer fatigue).

These disadvantages are drawn from my own experience of PL/SQL in the various development teams I’ve worked on. Very often, a team is extremely well versed in languages such as Java, C#, and JavaScript. Having spent years becoming proficient in their language of choice, developers can typically produce code quickly and efficiently. Consequently, developers often prefer to stay in their preferred language and not have to worry about learning a new one, such as PL/SQL. In addition, time is usually strictly limited for development work. So, it’s no surprise that the bulk of developers like to remain on familiar ground.

This is understandable and it often motivates developers to search for rather creative ways of avoiding PL/SQL and doing things like mixing ORM and calling the PL/SQL stored procedures directly from Java or C#. This approach maintains their code in just the one language. However, if the data workflow is very complex, there may be severe performance issues with this mix-and-match approach. It’s also potentially a bit of an antipattern in that the high-level code and the PL/SQL become tightly coupled to each other—changing one may unexpectedly break the other. Breakages may only be detected by end users, and this can lead to disenchantment in the user community.

Calling stored procedures from Java or some other language may also reduce the security of the database. For example, stored procedure names and other important data may inadvertently get recorded in application logfiles. Another problem is that any errors or exceptions that occur in a stored procedure invoked from a high-level language may not be handled properly. As we saw earlier, there can be serious consequences when error conditions get overlooked.

When merging high-level languages with PL/SQL, perhaps worst of all is the lack of good separation of concerns. We are, in some sense, attempting to mix oil and water.

There Is a Better Way

Learning PL/SQL is not so difficult. With a little effort and a willingness to expand your programming tool set, PL/SQL skills can be acquired with relative ease. In fact, quickly learning new programming languages is a key skill for all developers. It’s also an interesting challenge and helps make for a more marketable skill set.

My advice to any developer who has a chance to learn PL/SQL is to seize the opportunity. The effort required to learn this interesting and useful language far outweighs the energy required to avoid it in favor of a more complex and potentially brittle mechanism. Your organization and its data users will thank you for creating more resilient solutions, and you’ll thank yourself when your newfound PL/SQL skills turn out to be reusable in other project work.

Let’s start with a few examples of PL/SQL just to see how easily the language can be learned. The code examples will be revisited later on to illustrate how to migrate them to a more resilient form. One last point in relation to looking at code examples: the ability to read code is one of the great skills in software development and integration. We’ll be looking to enhance this skill as the examples unfold.

Gaining a Basic Understanding of PL/SQL

More experienced PL/SQL readers can skip this section. It’s included to provide PL/SQL beginners or novices with an indication that the language can be readily understood purely on the basis of its similarity to mainstream, high-level programming languages. This content would have helped me when I first started learning PL/SQL. In the next section, you’ll begin the journey of learning to read PL/SQL written by someone else.

How to Read Existing or Legacy PL/SQL Code

Let’s now finally take a look at our very first piece of PL/SQL, in Example 1-1. What do you reckon this block of code is doing? I’ve added a few extra comments to assist the reader.

Example 1-1. Introducing PL/SQL
DECLARE
   x NUMBER := 100;
BEGIN
   FOR i IN 1..20 LOOP    -- A for loop similar to Java
      IF MOD(i, 2) = 0 THEN     -- if i is even
         INSERT INTO temp VALUES (i, x, 'i is even');
      ELSE
         INSERT INTO temp VALUES (i, x, 'i is odd');
      END IF;
      x := x + 100;
   END LOOP;
   COMMIT;
END;

The code in Example 1-1 loops from 1 to 20 and checks if the numbers are even or odd. The latter is done using the MOD function and the result is written into a table called temp. A cursory glance at the listing allows us to figure most of this out. Some confusion might also arise from these few, perhaps unfamiliar-looking lines:

  • DECLARE

  • IF

  • END IF

  • END LOOP

  • COMMIT

These are just standard PL/SQL; DECLARE is used to create new variables for later use in the code. The END IF is simply a demarcation point for the opening IF statement. The END LOOP is similarly a demarcation point for the end of the LOOP operation. The COMMIT marks the end of a transaction and is required to write any updates to persistent storage (i.e., the database).

Another item in Example 1-1 that might give you pause is the use of the rather strange-looking assignment operator:

x := x + 100;

I recall showing some PL/SQL code to a Java developer a few years ago who said, “What’s that weird-looking assignment?” The extra colon before the equals sign was the source of the confusion. It’s interesting to see what causes cognitive dissonance. This assignment statement is functionally very similar to the equivalent operation in Java (and C), except in Java there is, of course, no colon in the assignment.

In this case, we are adding 100 to the value of x. The use of the assignment operator in PL/SQL merely reflects the origins of the language. PL/SQL was first released back in 1992 and is loosely based on Ada. PL/SQL often reminds me of the old Pascal language. For any reader interested in programming language history, PL/SQL is also quite similar to the IBM proprietary language Extended Structured Query Language (ESQL). It’s quite common for the authors of different programming languages to influence each other; for example, Java has only relatively recently added support for functional programming.

So, that’s the first example of PL/SQL done and dusted. The code is pretty straightforward and readable—in my opinion, it’s more readable than a lot of the often convoluted constructs found in both modern Java and JavaScript.

Reading source code is one of the most important skills any developer can acquire. This is particularly true in the current era, when so much development work involves a lot of code integration in multiple languages using a range of frameworks, platforms, and libraries. It’s not uncommon to labor over a programming problem for days and then find that the fix lies in adding or changing a single line of code or even just modifying some configuration data. Learning PL/SQL can help in extending your skill set and in producing resilient code. Let’s look at another example.

Example 1-2 illustrates a slightly different style of writing PL/SQL, which might be more familiar to developers used to mainstream languages. Again, some comments (which start with the characters “--”) are added to help in understanding it. As before, try to figure out what the code is doing before reading on.

Example 1-2. A more familiar code syntax
declare
  n number(10) :=1;   -- Notice the lack of spaces
begin
  while n<=10   -- Notice the lack of spaces again!
  loop
    dbms_output.put_line(n);
    n:=n+1;
  end loop;
end;

Some items of note in Example 1-2 are:

  • The use of lowercase

  • Indentation using two spaces

  • The lack of spaces in the code statements

  • A call to dbms_output(), which allows you to display messages to screen

  • The use of end loop, which you also saw in Example 1-1

In Example 1-2, the keywords are all in lowercase. PL/SQL is not case-sensitive, so you can use uppercase or lowercase. The two-space indentation provides a nice, compact, and readable style, though you are free to use any number of spaces you like. The use of spaces is encouraged to improve readability for downstream maintainers of the PL/SQL code. So, it might be preferable to use this:

n number(10):= 1; -- Added an extra space

Similarly, I would always write the fourth line like this:

while n <= 10 -- Added extra spaces

The following use of spaces is also more readable:

n := n + 1; -- Added extra spaces

Any thoughts on what this PL/SQL code in Example 1-2 might be doing? Any idea why there is no COMMIT statement here?

To answer the first question, Example 1-2 is a simple while loop. Again, this is very similar to Java, C++, etc. The reason we have no COMMIT (or commit in lowercase) simply reflects the fact that there is no data to write to the database. In other words, the code in Example 1-2 is ephemeral; it has no persistent content.

This section again illustrates the importance of the ability and willingness to read PL/SQL code and to then try to understand it. In the next section, we’ll get a little bit more ambitious and introduce a really powerful feature of PL/SQL: cursors.

Note

Some of the examples in this book are drawn from sample PL/SQL programs provided by Oracle. There’s lots of code at this site to assist you in your learning journey.

Cursors

Armed with the knowledge from the previous examples, let’s look at Example 1-3, where you can begin to see more of the power of PL/SQL. Just to keep it interesting, I’ve reverted again to uppercase. Try not to be intimidated by any content you haven’t seen before.

Example 1-3. More powerful PL/SQL
DECLARE
   CURSOR c1 is 1
      SELECT ename, empno, sal FROM emp
         ORDER BY sal DESC;   -- start with highest paid employee
   my_ename VARCHAR2(10); 2 3
   my_empno NUMBER(4);
   my_sal   NUMBER(7,2);
BEGIN
   OPEN c1; 4
   FOR i IN 1..5 LOOP
      FETCH c1 INTO my_ename, my_empno, my_sal; 5
      EXIT WHEN c1%NOTFOUND;  /* in case the number requested */
                              /* is more than the total       */
                              /* number of employees          */
      INSERT INTO temp VALUES (my_sal, my_empno, my_ename); 6
      COMMIT; 7
   END LOOP;
   CLOSE c1; 8
END;

Let’s break down Example 1-3 into its component parts. Again, remember we’re just practicing reading the code and trying to imagine what it’s doing:

1

A CURSOR is used: this is a PL/SQL construct for collecting blocks of table data.

2

Some new variables are declared: my_ename, my_empno, and my_sal.

3

The new variables and their data types are added, e.g., my_ename VARCHAR2(10).

4

There’s an OPEN on the CURSOR variable c1.

5

There’s some sort of FETCH from the CURSOR into the declared variables.

6

There’s an INSERT of the fetched data into a table called temp.

7

There’s a COMMIT of the data and a finish to the LOOP.

8

There’s a close on the CURSOR c1.

These descriptions should help you make sense of the PL/SQL in Example 1-3. It turns out that a CURSOR is simply a kind of work area composed of one or more rows returned by a SQL query. A CURSOR is a PL/SQL programming construct that points to the result of a query. Oracle supports two types of CURSORs: implicit and explicit. Let’s have a quick look at these two important constructs.

Implicit CURSORs

When Oracle executes a SQL statement using SELECT, UPDATE, etc., it automatically creates an implicit CURSOR. This CURSOR is managed internally and Oracle reveals only a limited amount of information about it. For example, you can get the numbers of rows affected by the query using the following command:

SQL%ROWCOUNT

If you want to know if your query affected at least one row, you can use the following:

SQL%FOUND

If you run a query, such as SELECT * FROM <TABLE_NAME>, you can then follow it with something like this:

SET SERVEROUTPUT ON;
DECLARE
var_rows NUMBER := 0;
TYPE temp_type is TABLE of TEMP%ROWTYPE;
new_temp temp_type;
BEGIN
select * bulk collect into new_temp from temp;
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Number of rows affected: ' || SQL%ROWCOUNT);
END;

This allows you to see the numbers of rows affected by the SQL statement.

Explicit CURSORs

The other type of CURSOR is the explicit variety, which allows a lot of interaction with its lifecycle from within PL/SQL code. For example, you can do the following with an explicit CURSOR:

  1. OPEN the CURSOR for use.

  2. FETCH data from the CURSOR into variables.

  3. CLOSE the CURSOR (an important step in cleaning up after your code has run).

Explicit cursors take care of a lot of the heavy lifting involved when accessing the database. Cursors also give you type safety. You’ll see more on this later, but for the moment, the main point to note is that a knowledge of cursors and CURSOR management is a crucial part of learning PL/SQL.

Back to the CURSORs example

With that in mind about cursors, the following lines from Example 1-3 should now make a little more sense:

   CURSOR c1 is
      SELECT ename, empno, sal FROM emp
         ORDER BY sal DESC;   -- start with the highest paid employee

The result of this is to create an explicit CURSOR called c1. c1 contains one or more rows that have been read from the emp table by way of the SELECT query. The last line simply orders the resultant rows. So, how do we use the CURSOR? The first thing we need to do is open the CURSOR, i.e., OPEN c1. We then loop and repeatedly transfer the current contents of the CURSOR into the three variables:

   FOR i IN 1..5 LOOP
      FETCH c1 INTO my_ename, my_empno, my_sal;
      EXIT WHEN c1%NOTFOUND;

Notice the use of c1%NOTFOUND to exit the loop when all the constituent data has been processed.

The FETCH occurs for each row in the CURSOR. That is, I reuse the variables on each cycle of the containing loop. Finally, I insert the variable values into the temp table, followed by committing the changes to the database as follows:

INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
COMMIT

After this, I repeat the loop for the next row in the CURSOR. When all the CURSOR data has been processed, the loop ends and I close the CURSOR.

Example 1-3 contains some powerful PL/SQL content. It’s pretty concise code, and it’s almost certainly better to do this type of processing in PL/SQL rather than attempting to do this type of work in SQL script. This is because PL/SQL provides good error and exception handling, which we’ll see in detail in the upcoming chapters. The PL/SQL in Example 1-3 reveals a lot of the programming power and there’s more to come, so stay tuned.

Understanding the Need for a PL/SQL Learning and Development Environment

As with other programming languages, PL/SQL code should be written and maintained using some sort of software tool. The tool I’ll be using in the book is the venerable SQL Developer from Oracle, which has many powerful features. SQL Developer will be introduced in the next chapter as part of the description of installing a virtualized Oracle Database instance.

Before wrapping up this chapter, let’s take a quick look at the proposed scale of resilience for PL/SQL code.

The Scale of Resilience

Let’s now back up a little and have a look at Example 1-3 from a different perspective. Rather than just trying to understand the PL/SQL, we now want to instead try to derive some idea of the code resilience. Example 1-4 shows the same code.

Example 1-4. How resilient is this PL/SQL?
DECLARE
   CURSOR c1 is
      SELECT ename, empno, sal FROM emp
         ORDER BY sal DESC;   -- start with highest paid employee
   my_ename VARCHAR2(10);
   my_empno NUMBER(4);
   my_sal   NUMBER(7,2);
BEGIN
   OPEN c1;
   FOR i IN 1..5 LOOP
      FETCH c1 INTO my_ename, my_empno, my_sal;
      EXIT WHEN c1%NOTFOUND;  /* in case the number requested */
                              /* is more than the total       */
                              /* number of employees          */
      INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
      COMMIT;
   END LOOP;
   CLOSE c1;
END;

Remember the requirements enumerated earlier in relation to resilience? Here they are again in tabular form in Table 1-1. In order for our code to be designated as being resilient, it must include elements of all of these characteristics.

This is of course just a kind of thought experiment. It’s not rigorous because I’m just trying to arrive at a way of deciding whether the PL/SQL code is resilient or not. To make it a little more interesting, I’ve also introduced a score column in Table 1-1 with values ranging from 0 to 10, where 0 is the lowest possible score and 10 is the highest.

The values in the score column are my estimates of how the code stacks up. I’ll look at why these numbers were arrived at in the next few sections.

Table 1-1. Introducing a scale of resilience for PL/SQL
Requirement number Resilience requirement Score (0–10)

1

Capture all errors and exceptions

0

2

Recoverability

2

3

Observability

0

4

Modifiability

5

5

Modularity

2

6

Simplicity

5

7

Coding conventions

5

8

Reusability

2

9

Repeatable testing

2

10

Avoiding common antipatterns

0

11

Schema evolution

0

Total score

23

In Table 1-1, you see a score of 23 out of a possible 110, which is really quite low. Let’s try to see why I arrived at this disappointing result to the thought experiment.

Capture All Errors and Exceptions: Score = 0

We get a big 0 for this one, and deservedly so. The code in Example 1-4 doesn’t handle any errors or exceptions. This is perhaps the single greatest problem with this code. It can fail, and when it does, we’ll have little or no idea as to why it failed. This makes it more difficult to recover and greatly reduces the resilience of the code.

Recoverability: Score = 2

We get a low score of 2 for recoverability. Why? Well, what happens if we encounter an error when the code runs? It just bombs out with no option to handle the problem. It will be difficult in this scenario to determine if the code ran to completion or stopped halfway through. As discussed, this is poor practice and is at odds with the needs of resilience.

Observability: Score = 0

A low score for observability is also bad news. It means that we have no easy way to see if the code ran successfully. Nor can we see any data changes that occurred as a result of the code run. Indeed, given that we have a call to COMMIT, the data change may well have occurred and then been reverted because of an error. In that case, the error causes the transaction to be rolled back. With low observability, we don’t get to see any of what happens behind the scenes. It’s as if the code hadn’t run at all.

Modifiability: Score = 5

The code in Example 1-4 isn’t all bad; it is easy to understand. This makes it more modifiable than might be the case in functional programming–style code blocks in languages such as JavaScript and indeed Java. In these languages, the code can be extremely difficult to understand and maintain. By contrast, the PL/SQL in Example 1-4 can be changed with confidence; this will be very useful when I start to look at refactoring this code in later chapters.

Modularity: Score = 2

The code in Example 1-4 has a structure that is fairly easy to understand. Thus, it can be converted into a callable unit of code, such as a PL/SQL procedure. Modularizing code in this way helps in promoting reuse, such as creating packages of useful PL/SQL code. Such packages can then be shared with other developers. This has numerous benefits that will be seen later.

Simplicity: Score = 5

Example 1-4 is pretty easy to understand even though it uses a CURSOR abstraction. The CURSOR abstraction may be (unnecessarily) viewed as a complex programming construct. As we’ll see, CURSORs are a fundamental unit of PL/SQL and they provide a lot of algorithmic power and safety. Simplicity is a key requirement of resilience, because it forces us to think about the avoidance of overly complex code.

Coding Conventions: Score = 5

The code in Example 1-4 certainly looks like it might be based on a defined coding convention. Typically, this governs elements such as:

  • Spacing

  • Variable names

  • Style of comments

  • Length of code blocks

Good coding conventions are very useful and can make it easier for different teams in an organization to manage each other’s code, such as developers and/or DevOps groups.

Reusability: Score = 2

Because the Example 1-4 code isn’t structured as a procedure or function, it’s not easily reusable. One of the merits of writing reusable code is that you must aim for more generality in terms of structure, parameter names, and so on. So rather than just solving the problem at hand, your aim should always be to write the code so that other developers can use it for their work. An unexpected benefit is that your code then gets looked at by more developers, and this can result in fixes or improvements to your original work. You’ll see this later on.

Repeatable Testing: Score = 2

Again, a low score has been assigned because we don’t have any test infrastructure. Without this, we can’t be absolutely sure that the code is rock-solid. This code can only really be tested by manual means, i.e., looking at the original data and comparing it with the output data.

Avoiding Common Antipatterns: Score = 0

The code in Example 1-4 contains at least one antipattern related to the way it handles data types. Also, the lack of error handling is an egregious antipattern. I’ll come back to this later and show how to resolve these types of issues.

Schema Evolution: Score = 0

The next piece of Example 1-4 that we’ll analyze is a source of disappointment. Why do I get 0? Well, what happens to the following lines if the underlying table data definitions change?

SELECT ename, empno, sal FROM emp
   ORDER BY sal DESC;   -- start with highest paid employee
my_ename VARCHAR2(10);
my_empno NUMBER(4);
my_sal   NUMBER(7,2);

What happens if a DBA changes the definition of my_ename VARCHAR2(10) to my_ename VARCHAR2(100)? This happens quite regularly in organizations as business requirements change. It is an example of schema evolution and it has a bad effect on the code in Example 1-4, which will no longer work correctly.

Summary

This chapter’s aims were as follows:

  • Broadly understand what “resilient software” means.

  • Understand the requirements for resilience: what versus how.

  • Understand why PL/SQL is a good idea.

  • Appreciate why using SQL for business logic is generally a bad idea.

  • Know some of the disadvantages of PL/SQL.

  • Gain a basic understanding of PL/SQL.

  • Read existing or legacy PL/SQL code.

  • Understand the need for a PL/SQL learning and development environment.

  • Understand a basic scale of resilience.

Learning PL/SQL is much easier than many people might believe. The language is relatively simple and allows you to be flexible about using uppercase or lowercase characters. Spacing is similarly flexible and you can choose an indentation style that suits your needs or your house style.

I think one of the merits of PL/SQL is that it is a readable language, though some readers might not agree with me on this. One element of the language that really merits study is that of CURSORs. CURSORs demonstrate one of the major differences between PL/SQL and SQL. While SQL does use CURSORs internally, these objects are not visible to users. The use of a PL/SQL CURSOR, on the other hand, allows you to collect data in a type-safe way, and you’ll see more on this in the coming chapters.

Introducing a scale of resilience allows us to numerically analyze a block of PL/SQL. This is useful because we are not so much focused on what the code does but on other aspects of it, such as modularity, simplicity, antipatterns, and so on. The scale gives us metrics that can then inform further work on the underlying code, such as enhancing and refactoring it.

By addressing these aspects and refactoring the PL/SQL examples, you will gain many benefits, as will be seen in the coming chapters.

Get Resilient Oracle PL/SQL now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial