Database, SQL and PL/SQL

Cleaning Up PL/SQL Practices


Prioritize and apply PL/SQL best practices to polish applications both new and old.

By Steven Feuerstein Oracle ACE Director

March/April 2004


It is not hard to come up with a list of dos and don'ts for developers. This list can rather easily become completely overwhelming, however, because it can be (a) hard to remember all the best practices, (b) challenging to implement them, and (c) perplexing to determine whether or not the developers on a team are actually complying with the best practices.

The challenge for any development organization is keeping track of the best practices and applying them.

This article explores ways to apply a prioritized list of best practices, from a practical standpoint, and then demonstrates some techniques for automated analysis of code for compliance with a wide range of recommended practices.

Embed Best Practices

As much as possible, take your best practices and implement them in reusable pieces of code, such as a generic error handling package. Then you only have to train or convince developers to use those components, and they will automatically be following the best practices.

Consider the code in Listing 1. This is fairly typical error handling logic: I am showing exactly how I am getting the job done, including writing information out to a database table. One big problem with this code is that my INSERT into the log table becomes a part of my business transaction. Roll back the transaction, and I lose my log. I really should be taking advantage of autonomous transactions!

Rather than fix each of these handler sections, however, I should build an error handling package and then apply it in each handler section. Listing 2 offers the specification of a simple example of this type of package. Listing 3 shows the application of this package to my exception section.

By relying on the standard errpkg, you no longer have to think about how to write information to the log nor do you have to take any action to propagate the exception out to the enclosing block. The errpkg does all of this for you, according to the standards defined for the application. You use a lot less time to write your exception section, and your code follows the best practices defined in your team without your having to think about what those best practices are.

Of course, not every aspect of application development can be captured in generic, reusable packages. You still have to write a lot of custom code, and you still need to pay attention to best practices for these lines of PL/SQL. Because it is hard to keep track of all the best practices, you must inevitably prioritize.

Prioritizing Practices for New Apps

Not all best practices are created equal. Some have an enormous impact on the overall structure, readability, and maintainability of your code. Others address relatively minor aspects of code quality.

Rather than try to memorize all best practices or attempt to pressure your developers to follow them all, come up with a prioritized list of best practices. Identify which are the most important to follow and how to follow them. Then create short, simple checklists that developers can post on or near their computers for easy and frequent reminders.

I offer some suggestions here for prioritized best-practices checklist items and for two scenarios: new application development and maintenance of existing or legacy applications.

When building a new application, you have the chance to do it right. The main best- practices emphasis should be on overall code structure and readability, with a dollop of reminders about critical performance enhancers. I don't have the space to delve into all of these best-practices checklist topics in great detail. I have covered some in previous Oracle Magazine and OTN articles and others in my Oracle PL/SQL Best Practices book. Here are some of my suggestions for best-practices checklist items for new applications:

Don't write SQL. Rather than write a SELECT INTO when you need some data, call a function that gets the data for you and has all of the standard error handling and optimization logic hidden inside it. Even better, use comprehensive 'data encapsulation packages' that handle most of your SQL needs.

Oracle Designer generates Table API (TAPI) packages. You can also generate these packages with PL/Generator, a free Quest Software utility available at You can also use Swyg, a new product I offer at

A checklist with specific SQL best-practices checklist reminders might offer these items:

Write tiny chunks of code. As I mentioned in the November/December 2003 issue of Oracle Magazine , I strongly recommend that you restrict the length of your executable section to 50 or 60 lines of code. It will be much easier to read and maintain this code over time. The best way to follow this best practice in new code is to employ top-down design and rely on local or nested modules. My OverloadCheck article on OTN (at explains this technique in detail.

A checklist with specific code-chunk reminders might offer these items:

  • Write no more than 50 lines of code from BEGIN to END .

  • Create local procedures and functions to hide logic.

  • Hide all formulas and business rules behind a function interface.

Write unit tests first. Before you start writing any of your code, you should design the tests that will determine whether or not the code is working. If you wait till after you have written your program, you will subconsciously write tests only for the conditions and logic that you know (or hope) function properly. By writing tests first, you concentrate on the overall design of your code base and the interface to individual programs.

Of course, writing test code is an enormous topic and challenge in and of itself, but I recommend that you take advantage of Ounit and utPLSQL: free, automated unit-testing software for PL/SQL developers, available at Disclosure: I designed and was lead developer for both of these tools.

Consider these test items for your checklist:

  • Write test cases before writing any code.

  • Write a test case to verify a bug report.

  • Write a test case to validate a successful enhancement.

  • Implement test cases using an automated framework (Ounit and utPLSQL or your own home-grown utility).

Prioritizing Practices for Maintaining

Many PL/SQL developers spend a good part of their time maintaining existing applications. This is often a challenging and frustrating job, because lots of code is poorly written, hard to understand, and largely untested. Almost every application has a 'black hole' program: some monstrously large and unstructured piece of code that everyone is afraid to touch. If you make a change on line 255, you might introduce 25 bugs throughout the program. There is no way to really know the impact of changes on the code, because no regression test is in place.

When you are faced with applying best practices to an existing application, apply changes in an incremental and iterative fashion. It is difficult for a development manager to support proactively changing an existing, running production application just because it can be written better.

Instead, you will want to apply key best practices each time you have to open up the black hole program, and then only in a limited fashion. Here are some suggestions for a best-practices checklist for applying changes in this way:

  • Write a regression test. This can be a painful exercise, but it simply must be done if you are to have any hope of maintaining a piece of code with some confidence. Before you start maintaining an existing program, sit down and think through as many conditions as you can that you can test to verify that the program is working. Implement those conditions in a test package, run that code, and confirm that the program works. Then as you make your changes, you can rerun this test and confirm that you haven't introduced any bugs. Again, Ounit and utPLSQL can offer lots of assistance in this area.

  • Carve out local modules that hide an identifiable area of functionality. Your program's executable section might be 1,000 lines long. Start at the top of the executable section, and read down through the code. When you can identify the 20 or 30 or 100 lines that, for example, validate parameters and initialize data, take out all those lines of code from the executable section and replace them with a single line of code:


    and then move all those lines into the declaration section with this local procedure context:

    PROCEDURE initialize_data IS
    ...all that code...
    END initialize_data;

    Over time, the unreadable executable section will become smaller and more accessible.

  • From a performance perspective, look for areas to apply FORALL and BULK COLLECT , which offer dramatic performance improvement in virtually every circumstance. Find all the DML statements in the program. If they appear within any kind of loop (cursor or otherwise), extract all the DML logic, replacing it with a procedure call; transfer that logic to the declaration section as a local module; and then enhance it to use bulk processing.

Validating Compliance

You buy the books; you write the checklists; you evangelize about best practices. And then all the developers on your team write their code. It is very unlikely that everyone (anyone?) will remember and follow even the prioritized recommendations.

You must therefore make a decision: make these best practices entirely optional (which means they will be largely ignored) or make an effort to ensure that people comply with them.

I suggest that you find ways to check the code developers in your team write (or you yourself write, because even with the best of intentions, you will be hard-pressed to do everything the right way!). The two best methods of validating compliance are code review and code analysis.

Eyeball that code! No piece of code should be passed to QA or run in a production environment until it has been looked at by another developer (other than the author of the code). Code review can take many forms, ranging from Extreme Programming's pair programming to a simple buddy system.

With pair programming, no programmer writes code alone. Instead, two people sit together in front of every workstation, and every line of code is looked at and evaluated by both of them. This is, obviously, an extreme step, and although it offers tremendous value, few development teams pursue this strategy.

Some organizations have a formal code review process in which all developers present their code to a gathering of other developers for feedback. Others rely on a less-formal approach: Senior programmers simply wander the landscape, stopping to look at and offer advice on another developer's code.

Analyze code with SQL. Of course, when you are working on a large application development effort, you will have tens of thousands of lines of code. It may simply become impractical to review each line of code. In this scenario, automated code analysis becomes important. Fortunately, the PL/SQL language is accessible to such analysis.

Whenever you compile a program, Oracle parses the code and stores lots of information about that program in a variety of data dictionary views. Table 1 offers a partial list of these views. Because this metadata about your code is stored in views, you can use SQL to write queries against the views to analyze the code and give you feedback about compliance with best practices.

Name Significance
USER_SOURCE Includes the lines of source code for all the programs you own
ALL_ARGUMENTS Includes information about the parameters to the procedures and functions you can call
ALL_PROCEDURES Contains the list of procedures and functions you can execute
ALL_DEPENDENCIES Is one of several views that give you information about dependencies between database objects.
Table 1. Data dictionary views containing information about a program

Let's start with a simple example. The CHAR datatype allows you to declare fixed-length strings; almost all of our work is based on VARCHAR2 , variable-length string data. CHAR -based variables can introduce bugs into code, giving false negatives on comparisons between strings, for example.

I would like to find all the instances of a CHAR declaration in my code. The following shows the code that will find all such occurrences (and perhaps more):

SELECT NAME, line, text   
FROM user_source  
' CHAR') > 0   
INSTR (UPPER(text), 
' CHAR(') > 0   
INSTR (UPPER(text), 
' CHAR (') > 0;

Standalone queries like this example demonstrate the core technique, but using them is not the best way to implement this functionality. A much better approach is to put the various queries into a set of packaged procedures that developers can easily call.

The valstd package is an example of such a standards validation package. It has the following package specification:

  PROCEDURE progwith (str IN VARCHAR2);
  PROCEDURE exception_handling;
  PROCEDURE encap_compliance;
END valstd;

The valstd package offers the following programs:

progwith : Shows all the programs with a specified string.
exception_handling : Validates a set of standards related to exception handling (built on progwith ).
encap_compliance : Identifies programs that violate the data encapsulation best practice, namely 'Never reference a table directly; instead, call procedures and functions to execute the SQL for you.'

Let's take a look at the implementation of some parts of valstd .

The progwith procedure is an extension of the 'Show CHAR occurrences' query. Listing 4 offers the implementation of this procedure. On lines 3 through 11, I create a collection (associative array) of records to hold the information I query from USER_SOURCE . On lines 13 through 19, I take advantage of BULK COLLECT in Oracle9i to fetch all matching rows into my collection in a single query statement. This concise syntax is also incredibly efficient.

Once I have retrieved my data, I can display it. On line 21, I call a local procedure named disp_header to show the header information (and hide those details). Finally, on lines 23 through 26, I display the results of my search.

The encap_compliance procedure, shown in Listing 5 is similar in structure but has a different query at its core. You will find on lines 18 through 29 a query against ALL_DEPENDENCIES that identifies all PL/SQL code objects that directly reference a table or view. If the guideline in your organization is to call predefined (and, hopefully, generated) procedures and functions that hide the SQL statements, this query will expose all programs that violate the guideline and bypass the table API.

Bells and whistles. Once you have created your standards validation package, you can add bells and whistles, such as the following:

  • Write out HTML to a file, and then publish the results on your intranet or send the information as an e-mail (using Oracle 10g's new UTL_MAIL package) to the developer responsible for a given program.

  • Run your validation programs nightly in a job scheduled with DBMS_JOB or, in the world of Oracle 10g, the new DBMS_SCHEDULER package and framework. Every morning when a developer logs in and checks e-mail, that person will see a report on any violations of standards and can then efficiently move through the list of violations and fix them.

Taking Best Practices Seriously

We all want to write better, more efficient, and more easily maintainable code. The challenge is figuring out how to do this while still meeting our deadlines (and finding time to spend with our families).

Develop prioritized lists of those best-practices techniques everyone must follow. Then back up those lists with standard components that automatically conform to best practices, while cutting down on the volume and complexity of code developers have to write. Review the code to reinforce the best practices, share expertise, and improve the code base. Finally, construct and run analysis utilities that will sweep through the many thousands of lines of code to identify violations of best practices.

This potent combination of process, reusable code, and automated analysis can transform best practices from a dream to a practical reality.

Next Steps

READ about more PL/SQL best practices



Photography by Ricardo Gomez, Unsplash