Quseful #3: Don't put COMMIT; in your code!

Steven Feuerstein — Tue, 05 Jun 2007 13:16:00 GMT

Qusefuls #1 and #2 were published on Steven Feuerstein's personal blog at feuerthoughts.blogspot.com.

About Qusefuls

A Quseful is a Quick and Useful (as opposed to Quick and Dirty) tip on now to write programs in the Oracle PL/SQL language more effectively. I originally published Qusefuls on my blog (feuerthoughts.blogspot.com), but am now switching the "home" for Qusefuls to my ToadWorld blog. Each Quseful contains a description of the tip ("What's the point?"), some PL/SQL code you can install in your environment to implement the tip ("Show me the code!"), an example of how to use the code to help you get your job done ("How do I use it?").

What's the point?

First, here is my recommendation to you:

Never call COMMIT or ROLLBACK; directly in your code. Instead, call a program that will do the commit for you, and design that program so you can dynamically turn commits/rollbacks on and off, without changing the application code.

Why would I say that? Because a COMMIT; in your code is an example of hard-coding, and as we all know, hard-coding is bad. "Hard-coding?" you ask, "What is Steven talking about?"

Everyone knows what hard-coding is: you put a literal value directly in your code instead of "hiding it" behind a variable, constant or function name. As in:

IF l_employee.salary > 10000000 THEN

must_be_ceo ();

END IF;

And we all know that this is a bad thing to do; even if it doesn't seem as though that literal value could ever change, we know that it will, and then we have to track down every occurrence of the number and change that. So that's the easy part of hard-coding. The hard part is recognizing all the different kinds of hard-coding that can appear in your code. For example, I suggest to you that every time you write COMMIT; or BOLLBACK; in your code, you have hard-coded the transaction boundary. That is, once you commit, you cannot undo your changes. And once you rollback, those changes are gone. Lost forever.

"Well, duh!" you are likely thinking. "That's the whole point of those statements. Now you are just being silly."

Not at all. This is one of those situations that seem so clear at first glance, but upon closer inspection, one realizes that it is a bit more complicated. Suppose I have created a program to adjust the popularity ratings of my company's products, partitioned by gender. The specifications for this program call for a commit, so I write the following:

PROCEDURE adjust_ratings (gender_in IN VARCHAR2)

IS
BEGIN

.... execute many queries and DML statements ....

COMMIT;

END adjust_ratings;

It is then time to test my program (which I will do with Quest® Code Tester for Oracle). I must write the code to set up the various tables on which the program depends (and to which it writes). Some of these tables have hundreds of thousands of rows of data, so it is not at all practical to load it from scratch each time. In fact, what really makes the most sense is to be able to run her program, look at the changes to the tables, and then (assuming something is still wrong) issue a rollback after running adjust_ratings to return the state of the data back to its starting point.

No problem! I just go into my code and make this change:

PROCEDURE adjust_ratings (gender_in IN VARCHAR2)

IS
BEGIN

.... execute many queries and DML statements ....

-- Don't commit while testing COMMIT;

END adjust_ratings;

Now I can run my tests, rollback, run some more tests, without having to go through an elaborate, time-consuming setup process. And when I have fully tested the program and is sure it works? I change the program back to its original state:

PROCEDURE adjust_ratings (gender_in IN VARCHAR2)

IS
BEGIN

.... execute many queries and DML statements ....

COMMIT;

END adjust_ratings;

So let's recap those steps:

1. Write program.

2. Modify program for testing.

3. Test program until you are sure it works.

4. Then change the program.

What's wrong with this picture? You are not supposed to change your code after you finish testing! Sure, it's not a big deal to comment out and in the COMMIT; statement, but what if there are dozens of such statements in your code? How will you make sure that have changed them all?

Oh and as for "commit as hard-coding," do you see now what I mean? It seems so unambiguous at first, but once we look at the requirements for testing one's code, that inflexible transaction boundary becomes an obstacle. Sometimes we want the commit to take place, but at other times, we'd really rather it didn't do the commit.

So what should you do, instead? Call a program to do the committing for you. I have written such a program, in the my_commit package.

Show me the code!

Sorry, rather than show you all the code here (very clumsy), I offer the source code and any supporting files in this zip file.

You can also download my entire "demo zip", containing all the scripts and reusable code that are part of my regular trainings. The zip for this Quseful is inside that zip as well.

Here are the files in the Quseful3.zip:

my_commit.pks - the my_commit package specification
my_commit.pkb - the my_commit package body
Q##MY_COMMIT.qut - a Quest Code Tester test definition export that you can import this into an installation of Code Tester, in order to confirm for yourself that my_commit works as advertised.

How do I use it?

To take advantage of my_commit, I would change my procedure as follows:

PROCEDURE adjust_ratings (gender_in IN VARCHAR2)

IS
BEGIN

.... execute many queries and DML statements ....

my_commit.perform_commit ();

END adjust_ratings;

By default, committing is enabled, and perform_commit will do the commit; here is the implementation of this utility:

PROCEDUREperform_commit(context_inINVARCHAR2:=NULL)

BEGIN

trace_action('perform_commit', context_in);

IFcommitting()

THEN

COMMIT;

ENDIF;

END;

It contains a built-in tracing facility that you can turn on to "watch" commits. But the main thing is the conditional statement that only commits when the package setting is enabled.

So when I test my code, I can disable saving and then run the program. Shown below are the steps inside SQL*Plus. Check out the test definition export in the download zip to see how this is done in Quest Code Tester setup logic.

SQL> exec my_commit.turn_off

SQL> exec adjust_ratings ('MALE')

And after I am done analyzing the results, I can simply rollback and test again.

Do you like it? Do you use it?

I'd love to hear what you think of this utility and, in particular, if you found it useful. So please don't hesitate to post a comment on this blog or send me a note at steven.feuerstein@quest.com.

Steven Feuerstein's Blog

Quseful #3: Don't put COMMIT; in your code!