Checking PL/SQL with Teamscale

14. March 2017

Throughout this blog, there have been several posts explaining how you can use Teamscale to keep your code-base clean and prevent quality decay. Since we have a lot of enterprise customers, we are often confronted with the question whether our approach works only with modern languages and infrastructure used by small software vendors and start-ups, or if we can also offer analysis for mature technologies mainly used in the enterprise.

My colleague Christian already reported on Teamscale’s analysis capabilities in the ABAP (and more recently SAP HANA) world. In this post, I give some examples of checks we recently developed for PL/SQL and which are included in Teamscale 3.2, which is published around the same time as this post.

Examples for Checks in PL/SQL

As introduced recently on this blog, Teamscale features a flexible source code parsing and analysis facility and allows developing custom static code checks with a few lines of code. Of course, when adding new checks to Teamscale that are local to a single code file, this is what we also use internally.

Don’t use SELECT *

Let’s start with a check that may have severe performance impact and at the same time is very easy to explain and to implement: Select only those columns that you really need. In particular, do not use SELECT *. While it is very convenient to just select all columns and let the database take care of which columns actually exist, doing so has two major drawbacks: (1) The SELECT statement does not make clear which columns it is actually interested in, so everybody who has to read or change that code later must figure that out over and over again, and maybe even more importantly, (2) The database engine always has to load and deliver all columns to the caller, which might in turn ignore half of them. Especially in column-oriented databases, this has a severe performance impact. You can find a more comprehensive answer on Stack Overflow

The check itself, however, is fairly simple: Search for any SELECT statements and issue a finding if it is followed by *. This is a perfect example of great return on invest static analyses.

Use Inline SQL Only to Select Few Columns

Another example is the use of inline SQL queries as opposed to cursors. Some of our customers argue that while SQL queries inside procedures are feasible and technically possible, they should be used only if the number of selected columns is reasonably small.

The reason for this is twofold. First, extracting non-trivial SELECT statements to a cursor improves readability and reusability. Second, cursors provide exception handling (e.g., for cases in which no data is found), which is not the case for inline SQL statements.

While the general concept of this check may be useful to many other Teamscale users, the particular threshold may vary across different coding guidelines. Therefore, the check in Teamscale is parameterized with the maximum number of columns that are still ok to select in inline SQL queries:

Custom Check Parameters

Always close cursors

The figure above also shows another check, namely that for unclosed cursors. This check is slightly more complex than the ones I mentioned so far, because it works not only on a single statement, but on the procedure level. For every cursor that is opened, it checks whether it is also closed within the same procedure. According to, e.g., [1] unclosed cursors may at best have significant overhead when they have to be closed implicitly by PL/SQL, and at worst allow for memory leaks. Letting Teamscale find such instances makes it really easy to spot possible mistakes in your PL/SQL code.

Unclosed Cursor Finding in the Code

Call for Action

Would you like to learn more about Teamscale or try it on your own PL/SQL code? Get your free trial from the Teamscale website. Do you know more patterns of bad PL/SQL practice that are worth (and possible) being statically checked for? Contact us, and we’ll find a way to make that happen. Of course, you are also welcome to add your own checks to your local Teamscale instance using our custom check framework.