TomBecker.net

Transactions in VB.Net and Oracle

This is going to be kinda a two part post. Each talking about an approach to the same problem. But first some background.

I am creating a questionnaire that is being saved to the database in a mutli-step process. 4 steps to be exact. If any one of those steps fail I want to be able to roll back to the beginning so I don't have a stub of a questionnaire floating around. I need that process to be atomic.

I originally wrote it so that the 4 steps were independent of each other so that it could be as flexible as possible. And then the problem of the stubs came up. So I had to make it atomic. So since I already had four functions and 4 stored procedures in Oracle I created a transaction in .Net and just added it to my command objects (Part 1 of this article), but that didn't work for me. So then I went to combining all 4 procedures into one and using Oracle transactions (Part 2).

Part 1: .Net Transactions

.Net transactions aren't actually that hard, In my case I was using the OracleTransaction class but there is a SQLTransaction class that works the same way for those of you using MS-SQL. Essentially you tie your transaction to your connection and your command object. You must use the same connection for each command object, but that is pretty much your only limitation. The most common implementation that I've seen is just passing the connection and the transaction to all the functions that needed it and that is what I did as well. Code examples below:

Private Sub Save()
Dim conn As New OracleConnection("ConnString")
Dim trans As OracleTransaction

conn.Open()
Try
 trans = conn.BeginTransaction
 saveThingOne(conn, trans)
 saveThingTwo(conn, trans)

 trans.Commit()
Catch ex As Exception
 trans.Rollback()
End Try
End Sub

Private Sub saveThingOne(ByVal conn As OracleConnection, ByVal trans As OracleTransaction)
Using comm As New OracleCommand("Save_Thing_One_Stored_Procedure", conn, trans)
   comm.CommandType = CommandType.StoredProcedure
   comm.Parameters.Add("IN_ID", OracleType.Number).Value = intID
   comm.Parameters.Add("IN_THING_ONE", OracleType.VarChar, 60).Value = strThingONe

   comm.ExecuteNonQuery()
End Using
End Sub

Private Sub saveThingTwo(ByVal conn As OracleConnection, ByVal trans As OracleTransaction)
Using comm As New OracleCommand("Save_Thing_Two_Stored_Procedure", conn, trans)
   comm.CommandType = CommandType.StoredProcedure

   comm.Parameters.Add("IN_ID", OracleType.Number).Value = intID
   comm.Parameters.Add("IN_THING_TWO", OracleType.VarChar, 60).Value = strThingTwo

   comm.ExecuteNonQuery()
End Using
End Sub

However, in my current environment we use stored procedures for all of our database interactions. And that being the case it makes using the .Net transactions impossible to use. Now I don't know if the above code would work in a MS-SQL environment, but in an Oracle environment it does not. The best reason I can figure it is because Oracle treats a stored procedure as a transaction in and of it self. So when you get to the end of the stored procedure it does a full commit automatically...hence nothing to rollback. So I had to combine all 4 procedures into one massive one and use Oracle transaction statements to make sure everything is done at once.

Part 2: Oracle Transactions

There are a lot of articles out there about Oracle transactions, but I found that most of them had all the information that I needed separated out across multiple pages. So here it is all together.

As I mentioned above an Oracle stored procedure does a commit on successful completion of the procedure. But you can also explicitly tell it to do the commit any time you want to during its execution by calling the "commit;" command. You can also rollback at any point if you want to just by calling the "rollback;" command. If you put the two together you have the beginning of a nice way to ensure an atomic transaction.

The only other new part of the stored procedure you must add is a an exception clause. This as you may guess works much like a try/catch clause in VB. You can either catch specific Oracle errors by calling them by name, or you can do a catch all and just handle all of them the same way, which is what I did below. The only thing I would warn you about though, is that if you are having trouble with your procedure and are still testing I wouldn't put the exception clause in there quite yet. Oracle will catch and handle the error and end cleanly. You will not know that your code is bombing. It took me a while to figure out why it was performing a rollback and exiting clean.

So here is the code for a transaction in Oracle:

PROCEDURE SAVE_TWO_THINGS(
IN_THING_ONE IN VARCHAR2,
IN_THING_TWO IN VARCHAR2
)
IS
BEGIN
INSERT INTO TABLEONE
(ACTIVE, THING_DESC)
VALUES
('Y', IN_THING_ONE);


INSERT INTO TABLETWO
(ACTIVE, THING_DESC)
VALUES
('N', IN_THING_TWO);

COMMIT;
EXCEPTION WHEN OTHERS THEN  ROLLBACK;
END;

Note that the 'OTHERS' keyword in my exception clause is where you would catch a specific oracle error, you can string together as many of those as you want to, but I would recommend using the 'OTHERS' as a kind of catch all at the end if you want to make sure you catch everything.

Summary: Unless you are using inline SQL to connect to your Oracle database then you're really not going to be able to use .Net transactions. So you are going to have to use the Oracle transaction. Many would argue that I should have done that from the beginning since Oracle is such a powerhouse that doing processing there isn't a big deal so any atomic transaction should be tried to be done in Oracle anyway, but I really think it is up to the programmer. It really is personal preferences.

Labels: .NET, Oracle, SQL

posted by Tom Becker at 7/16/2008