Exception Safety for PL/SQL

One of the things I like about C++, is that there are tons of theory around the language. There are many smart people who sit around and write articles about how different language mechanisms are best used. We have a bit of it in PL/SQL, but not nearly enough.

One of the most important articles about exception safety in C++ is Abraham’s Exception Safety in Generic Components. 90% of it is totally irrelevant to PL/SQL developers because the smart people from Oracle chose not to inflict templates and generic components on us (at least yet, who knows what will happen in the next version). However, in this article Abrahams defines the different levels of exception safety that a code component can achieve, and this directly relates to PL/SQL developments.

The levels are described in terms of a PL/SQL block that is called by another, and what can the caller expect after the called block has thrown an exception when each level is implemented.

Exception safety levels, as I translate them from C++ to PL/SQL:

  • No safety: You don’t handle exceptions. When your code breaks you have no idea what is the state of your structures and data, and all kind of resources like cursors may be left open. Note that you can achieve this state by completely ignoring the possibility of errors, or by using “when others null”. Be glad that you are developing in PL/SQL and not in C++, because at least Oracle keeps some semblance of data consistency, and keeps you from memory leaks and crashes.
  • Basic Safety: You take care to close cursors or other resources when exception occurs, and that you leave the system in a valid state. Raise_application_error functions does a bit of this – cleanup of out variables if relevant. Note that you still have no idea what the block actually did – inserting one row out of three is a valid state, so are unknown changes to variables in the calling block. In fact, in PL/SQL there is very little difference between basic safety and no safety – because Oracle works hard to make sure that truly invalid states don’t occur even if you do no exception handling at all. But they can happen if you have integrity rules that you expect your code to keep and are not modeled into the database (foreign keys and similar constraints are a good example for this – if you didn’t model them into the tables, you will need to take care of them as part of basic exception safety).
  • Strong Safety: This what we usually aim for in exception handling – complete success or full rollback. Either the block did what it was supposed to do, or it left the system as if it was never called, leaving the caller with all the options open when deciding how to proceed. It is difficult to achieve this state in C++, but in PL/SQL it should be almost trivial – scope your transaction correctly, so Oracle’s built in rollback can take of most of the cleanup for you, release all opened resources, clean up changes to global variables (this can be tricky – so avoid using global variables), and you should be good.
  • No-throw Safety: The operation will always succeed and never throw an exception. Usually both impossible and unnecessary. Except maybe for code you use in exception handling and similar cleanup operations.

Knowing all this may not cause you to write safer code, but hopefully you can at least think about how unsafe is the code you write, and what it will mean to those who use it.


If you are looking for something nice to read on the weekend, here are my recommendations:

Charles Schultz has a DB with high hopes for Christmas.
Jon Emmons explains about holiday decorations.
Dizwell released Doris, the queen of Oracle-on-Linux installations.

3 Comments on “Exception Safety for PL/SQL”

  1. Sidhu says:


    good one….


  2. Very interesting post! It is always good to see people thinking about ways to improve our perspective on and approach to writing PL/SQL.

    Two comments:

    * Even if you’ve decided on your safety level, you have to figure out how to implement a nice, clean generic way of logging and raising errors. I wrote (and Quest Software offers) a freeware utility to do just: Quest Error Mananger. Visit my PL/SQL Obsession site, http://www.ToadWorld.com/SF, and click on Downloads to grab it and take a look. It should do just about everything you need, through a well defined API.

    * It is definitely possible to violate basic safety in PL/SQL. You could open a file and forget to close it, open a cursor with DBMS_SQL and forget to close it, modify the state of package-level data, and forget to clean it up – leading to memory consumption errors. I suggest you create a local subprogram called cleanup, put all your cleanup logic in there, and make sure you call it at the end of your executable section, and also in each exception handler.

    Steven Feuerstein
    PL/SQL Evangelist
    Quest Software

  3. prodlife says:

    Hi Steven,

    Thank you for your comments.

    I’ve taken a quick look at QEM, and it looks friendly enough to be easily integrated into pretty much any development project. Which means that there will be plenty of opportunities to take a better look at it.

    One thing I did notice, is that if I look at the actual implementation of the package, it seems to be doing a whole lot more than what I’ve read in the help file.

    For example, after reading the doc, I wondered why you support logging to a table and the screen but not to a file, but from the code it seems that files are also supported.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s