C H A P T E R  14

Coding Conventions and Error Handling

by Stephan Petit

Can you wear a Tuxedo and Birkenstocks at the same time? Yes, you can! Anyone who spent some time at a particle physics laboratory knows it is technically possible. However, some may say that such an outfit clashes with the conventions of elegance. You may also like to wear a combination of green and blue clothes. (Personally I prefer mixing red and black). We can argue endlessly about our respective taste: everyone is right; everyone is wrong. At the same time, conventions, written or not, help with living and working together. They have their own history and reasons.

It is often fun to write code and we all want to make it as good as possible: efficient in terms of execution, compact, and if possible, with style. But we do not usually write code just for ourselves. If you ever had to read a piece of code written by somebody else in order to understand in details what it does or to debug it or to modify it, you probably thought “hmm, I wouldn't have done it that way.” It works but it can be hard to understand—a bit like listening to a cousin who speaks with a strong accent. The goal of this chapter is to propose one way of writing PL/SQL. I will first discuss the advantages of coding conventions, then propose specific formatting conventions. I will then discuss error handling and provide examples of error handling conventions. At the end, you will find a template summarizing the conventions outlined in this chapter

Why coding conventions?

Why even bother? Do we really need coding conventions? Wouldn't it be better for each person to let their own artistic talents flourish, giving free reign to everyone on naming of variables, indenting of loops, and more? Such freedom may sound enticing, but it's the road to ruin—or at least the road to not getting home for dinner on time because you're stuck in the office debugging some inscrutable code. The following are some benefits that accrue from creating structure and adhering to a good set of rules:

Quick understanding: It is easier, hence quicker, to read code that was written following conventions. With just a little bit of practice, the eyes of the reader immediately identify patterns in the code. The structure appears clearly, the various types of elements and the logical blocks become obvious. It also has the advantage of minimizing the possibility of misunderstanding. It is better to say “I like chocolate” than “I do not deny that I don't dislike chocolate,” even if both sentences mean the same thing.

Reliability: Once your code is complete, it is nice to have it reviewed by a colleague in order to find possible bugs, inconsistencies, and make sure that its logic is clear. Code that is easy to read and easy to understand benefits from a better review and then is potentially more reliable. From my experience, I know it is also easier to find a reviewer for my work when he or she knows that reading my code won't be a pain because it is an expression of my twisted style.

Maintainability: Maintaining code over time is a challenge. You may find even your own code to be difficult to understand when you revisit it a few months later. It gets worse when a whole package has been written by several individuals. Making sure a common style is being used during the life of a system is of great help when it comes to maintain it.

Security: Coding conventions can enhance the level of security of a system. For instance, a systematic use of bind variables can insulate against code injection threats. This aspect is described in more detail in a following paragraph.

Trainability: When half of the team in charge of a system changes almost every year, it is crucial to train every single newcomer as quickly as possible. When everyone speaks the same way, training is faster. It may happen that a newcomer is reluctant to learn a new way of using a language he or she is already mastering. Dare to impose the standards used by the whole team and if possible, ask the newcomer to work on existing modules. Finding the same coding style everywhere will quickly appear as an advantage.

Speed in coding: Why reinvent the wheel every time you start coding a new module? Using coding conventions removes several questions you may ask yourself about the style of your code. For instance, when modifying an existing procedure, should you follow the style used by the original author or should you use your own? When everyone uses the same style, such a dilemma simply vanishes.

Error handling: Making sure that all errors are gracefully trapped and correctly treated is another tricky part of delivering good software. Therefore, it is important to have a systematic approach to errors and a standardized way of reporting them. Coding conventions can bring a powerful solution to this aspect of software programming.

Formatting

Let's begin by looking at detailed conventions concerning how code is physically formatted. Formatting is one aspect of convention that picky people prefer. Dare to be picky. Following some simple rules makes code easier to read, reuse, and maintain.

The following sections describe some formatting rules that have worked well for me over the years. I follow them. My team follows them. Please though, do not take them as gospel. Feel free to adopt or adapt them, even invent your own! What is important is that everyone walks the same way.

Case

Use upper case for all SQL and PL/SQL keywords, names of procedures, functions, exceptions, and constants.

Use lower case for variables, comments, table names, and column names.

Comments

There are two ways of writing comments in the middle of some code: every single line beginning with -- or a whole block of text between /* and */. I decided to use the syntax --. I think it nicely highlights the text itself, so you don't need to look for where it starts and where it ends. Moreover, it has some technical advantage: you still can use /*... */ to comment a block of code for debugging reasons, even when this block contains some comments (prefixed with --) which you can't do if your code uses the /*... */ syntax. It is slightly heavier to use but can be very handy. You choose. Any comment?

The following is an example of how I comment code within a block:

Comparisons

Use <> instead of !=. The <> syntax avoids confusion with = when quickly reading,

Indentation

If ever there was a programming topic to cause controversy, it is probably the topic of how to indent your code. Programmers in all languages have been doing battle with this issue for decades. COBOL programmers perhaps have it easiest. At least in their language, much of the critical indentation is proscribed by the language specification.

The following sections address specific aspects of indenting PL/SQL code. The overall goal is to ease the reading of the code and visually identify logical blocks. I chose to use two blanks. One thing: avoid tabulations as they can be interpreted in different ways by various software development environments and printers.

General Indentation

Use two blanks to indicate that a block is a child of another one, like so:

Select Statements

Use one line per fetched column, one line per variable, and one line per element of the statement. This approach takes some space but eases the reading and the understanding of the whole statement. See in the following example a useful way of aligning various items:

This example shows how table aliases can be used:

Insert Statements

Use one line per column and one line per value. Always indicate the names of the columns in order to avoid ambiguity. This example shows how things can be aligned:

Update and Delete Statements

They look pretty much the same. Use one line per column and value, and one line per element of the statement.

If Statements

Use one line per element. For better reading, it's useful to add a comment after the END IF keyword, especially when many lines separate it from the matching IF. Here is an example of a series of IF blocks:

Case Statements

Use one line per element, like so:

Cursors

Use lower case for naming cursors and the suffix _cursor. Note the alignment of keywords on the left, as in the previous examples, improving the readability of the statement.

For…loop Statements

Use the l_ prefix for naming the variable used in the loop. When looping on a cursor, use the name of the cursor and the suffix _rec. Understanding where values come from is then easier when reading the code, as in this example:

String Concatenation

Use one line per concatenated element. Reading and modifying are easier this way. Building a string from several parts looks like this:

Dynamic Code

Dynamic SQL can be an open door to SQL or statement injection. Using coding templates can help in forcing the use of bind variables, which is one solution against such a threat. The following example shows how it can be dangerous to execute a statement built by concatenating strings, one of them given by an end user (as a parameter, for instance).

Imagine a module that registers books reservations at a library. It may contain some dynamic code, for instance because the table into which the data are inserted depends on the type of book. It may look like this:

The user is simply asked to input the requested book ID. In case of a gentle and standard user, the generated statement would look like the following, with “123456” the given input:

But if the end user has bad intentions, he or she can use the same input to inject malicious code. Giving the following input “123456); delete from books where (1=1”, the generated statement is this one:

The operation to be executed would then be made of two statements: a harmless one followed by a very nasty instruction emptying a table.

The following is a rewrite of the code using a bind variable. The use of the bind variable prevents any user-entered string from being interpreted as part of the statement itself. The risk of SQL or statement injection is eliminated.

Packages

Although it is possible to compile standalone stored procedures and functions in a database, it can quickly become a nightmare to search for one module in a large, flat list. Instead, use packages to group procedures and functions by semantic field or by usage. It is quicker to scan a correctly named package that is most probably containing what you are looking for than it is to scan a list of all functions and procedures in your whole schema.

Separate the kernel packages (the ones directly dealing with the data) from the ones that are responsible for user interface. More than helping their management and usage, this will allow you to fine tune execution grants, such as having wide access on interface package, more restricted access on kernel packages used as APIs, and no access at all to the very core of your application.

Name your packages using upper case (avoid double quotes around object names, making them case sensitive) and meaningful names. Use prefixes to indicate the type of modules that a package contains. For example, you might use the following prefixes:

KNL_ for a package containing kernel modules.

GUI_ for a package containing modules dealing with user interface.

Your package names might look as follows:

PACKAGE KNL_BOOKS; for a package containing modules dealing with books.

PACKAGE GUI_LIBRARY; for a package containing modules dealing with a library's user interface.

Stored Procedures

Stored procedures (standalone or in a package) should be considered black boxes that perform a given task. The term “black box” means that they are expected to cope with any kind of input (valid or not), and then do their job or leave the database in the same state as when they were called (for example, in case of error). Usually you want procedures to report any problem encountered in doing the work.

It is better to dedicate one procedure to one single task. This is the basis for good modularity in your application. It is better to have several small modules that you can use and reuse as elementary bricks than to have a few large and heavy modules that are difficult to reuse.

The various kinds of tasks can be separated as follows: retrieving records, checking information, inserting or modifying data, handled by kernel or API packages, producing display for a user interface, handled by interface packages.

Next I'll show you a way of naming stored procedures according to the kind of task they perform. This procedure naming policy, in combination with the package separation naming policy, will ensure an easier understanding of the source code. Moreover, it can also act as a guide for good modularity.

Naming

Use upper case and meaningful names. Use a prefix that indicates the type of the procedure (the $ sign is here as a cosmetic element to visually make a difference with package names and to separate the type prefix from the name). For example, the following are some prefix examples:

CHK$ for a procedure checking data.

GET$ for a procedure retrieving information from the database or calculating something.

EXE$ for a procedure inserting, modifying, or deleting data.

DSP$ for a procedure displaying information in a user interface.

And the following are some actual procedure names developed using the preceding list of prefixes:

PROCEDURE CHK$BOOK_AVAILABITLIY; for a procedure checking the availability of a book.

PROCEDURE GET$BOOK_AUTHOR; for a procedure retrieving the author of a book.

PROCEDURE EXE$STORE_BOOK; for a procedure storing data about a book.

PROCEDURE DSP$BOOK_INFO; for a procedure displaying information about a book in a user interface.

Also consider developing the habit of adding a comment repeating the name of the procedure after the final END closing the declaration. Doing so eases the task of reading packages containing a long list of modules, as you can see here:

Parameters

Use lower case and the prefix p_ to name parameters. It is then easier to distinguish them from local variables within the body of the procedure. Use one line per parameter and a fixed distance between the names, the in/out attribute, type, and the default assignment value, like so:

Notice how using one line per parameter makes the code easy to read. It is also easy add or remove one parameter if necessary, specially when the comma is at the beginning of each line.

Calls

Always prefix the procedure name with its package name, even when calling a procedure within the same package. It eases reading and debugging and makes the relocation of a piece of code into another package a piece of cake.

Always use the => syntax to pass parameter values. List parameters one value to the line. The => syntax makes the call unambiguous regarding the ordering of parameters; each parameter receives the value it is intended to receive. Moreover, the syntax also avoids any ambiguity when calling overloaded modules (some call them polymorphic modules). Here's an example:

Local Variables

Use lower case and the prefix l_ to name local variables. It is then easier to distinguish them from parameters within the body of the procedure. Use one line per declaration and a fixed distance between the names and the types, like so:

Constants

Use upper case and the prefix C_ to name your constants, like so:

Create a constant as soon as a value (a string or a number) is used at more than one place in the code. In other words, avoid hardcoded values. Using constants instead of hardcoded values multiple times will avoid typos and hours spent trying to understand why a module is not behaving as expected. For instance, imagine that you want to check that a button is pressed in a user interface. Using a constant for the name of the button and for checking what was clicked ensures consistency between the interface and the behavior of the application. Here's an example:

Types

Use upper case and the prefix T_ to name your types, like so:

Global Variables

Use lower case and the prefix g_ to name global variables, as in the following example:

Local Procedures and Functions

It is possible to declare procedures or functions that are local to a given procedure. Use upper case and the prefix LOCAL$ in their names. A local procedure would look like this:

Procedure Metadata

Even if you use software version management tools (such as Subversion), it can be useful to keep some dates and names in your source code. Other in-code documentation is also helpful. I suggest adding a block of comments such as the following before the declaration of each module. Having the name of who did what change and for what reason can help when it comes to modifying a module. It also acts as part of the technical documentation of the system.

Functions

My coding conventions for functions are the same as for procedures. Only the naming is different. I name my functions using the prefix STF$ (standing for Stored Function), like the following:

Functions are usually designed to return a single value. It may be useful to define one constant meaning that something went wrong. Note that functions can also have OUT parameters but I think it is mixing with the role of procedures. Therefore, I usually limit my use of functions to very safe processes or to cases that absolutely require them.

Error Handling

One of the key elements of robust, maintainable, and scalable software is modularity and avoidance of logic duplication. A strong system relies on a series of elementary bricks, each one having a unique function. Put together, these bricks implement more complex functionalities. Should a module fail, it must not crash the module that calls it. The first step of error handling, then, is to create a confinement hull that is designed to prevent any damage outside the procedure that fails. The second step is to report this error to the caller, so it can be nicely taken into account and propagated to the highest level without harm. The third step is to make sure that the state of the system remains consistent after an error; in other words, that no action was partially done.

Error Trapping

PL/SQL has a powerful error trapping mechanism in the form of exceptions. The one with the widest spectrum is called OTHERS. As its name says, it is designed to catch everything that was not otherwise trapped. I strongly recommend protecting all stored procedures by an exception handler for OTHERS. Doing so is the chance to log or output any valuable information for later debugging, such as which module failed with which parameter values. It won't prevent the application from crashing but you will know where and how. See the “Error Reporting” section for more details.

Should you need to add a user defined exception, use upper case, a meaningful name, and the prefix L_. You can use a different prefix if you prefer. My own convention here is to use L_.

The following is a skeleton procedure showing how I typically structure the error handling logic:

Also, while not a coding convention per se, do give thought to checking all input parameters. At least be sure not to fully trust them. Just because a module has a parameter called p_book_id does not mean the value passed into that parameter will always be a genuine book ID.

Error Reporting

Once you have built a system of modules, you want to know when one of them fails in order to correctly treat the problem. Transmitting error messages becomes vital towards having a stable architecture.

Error messaging can have two aspects: one for machines and one for humans. Error messaging for machines is based upon codes that can be easily interpreted, like (ORA-)01403 standing for “No data found.” Error messaging for humans is based on clear text explaining the given error. I recommend creating your own set of errors (codes and explanations) in a specific table. Make your errors specific to your application. For example, create a message such as “No book with such a title” and associate that message with a code of your choosing. Such a list of errors is useful to programmers and to end users. Make sure your messages are clear and friendly.

In order to propagate errors from one module to another, I use two output parameters called p_exitcode and p_exittext. The first is for machines; the second one is for humans. I ensure those parameters are present in every single stored procedure that I write. By convention, an exit code equal to 0 means that the execution of the procedure went fine. A code different from 0 indicates that an error occurred, with the code number indicating just which error that was.

Stop the execution as soon as a problem is detected; it's not worth going further. You may want to use a generic exception called L_PB_FATAL (standing for Local Problem Fatal, but you may choose any dramatic name you wish) that is raised as soon as there is no reason to carry on.

The following is an enhanced version of the procedure skeleton from the previous section. This version of the skeleton implements the additional parameters, and also the exception, that I've just described.

Any call to a procedure following the convention illustrated in this skeleton should be followed by a check on the exit code and an appropriate action in case of problem. Then you can decide whether to take the appropriate emergency action or to stop the execution and propagate the error using the same mechanism, like so:

Error Recovery

Following the black box paradigm, a procedure that inserts, updates, or deletes information should leave the database in a consistent state. Should the procedure fail, it has to rollback to the state the system was in when entering the procedure. Use upper case to name savepoints and make sure to include rollback calls in the global exceptions of the procedure, like so:

Test First. Display Second.

If you are writing a module to drive a user interface, it is better to first check and get all the required data first, then to display the interface. That way, all the possible errors are trapped before anything is displayed. You can then easily avoid having a message like “An error occurred” appear in a middle of a page or a screen for the user to see. Also, if you do need to display an error message, you can do so without confusing the user. If you begin display data only to interrupt with an error, users can become flustered and frustrated.

Summary

Coding conventions are not intended to solve all the problems you may encounter while implementing a system or maintaining software. However, they are very handy in order to standardize large source code bases and to ease the maintenance and sharing of modules. Following coding standards is a sign of respect for your fellow programmers and project leaders. As a programmer, you may find them difficult to use in the beginning, because they may not match your own style. Believe me; it takes only a short time before a given set of standards become your natural way of coding. Moreover, once you start managing a large set of modules, what a pleasure it is to “plug and play” them in a consistent way in order to build complex mechanisms! As a project leader, you may find difficult to impose standards upon a whole team, but the gain in quality is worth it.

The following is one final template. It summarizes in one place many of the conventions described in this chapter.