Keep Business Rules out of Triggers

My old team needed some assistance with a problem that was happening in production. Rows from a database table were mysteriously disappearing when the users ran a certain application. The developers swore that their code did not delete these records.

Since I trust nobody, I scanned the source code of all the database triggers in the system. Sure enough, there was a trigger on another table that deleted the records when a certain field got set to a value. Oh snap. This is just poor design. That's not what triggers are for.

There are two lessons to learn from this. One is to practice solid design and keep business rules out of the database triggers. The second is that the client server developers need to keep an eye on the code in the database triggers.

Promotion Woes

I had to fix a problem they were having using a back end process in production. Seems a processing command was resulting in an Oracle error. I investigated and found that the key stored procedure was commented out of the package. So I reenabled the procedure and thought we were good to go.

I got a call from our DBA. He said my code changes would not work in some of the other database schemas. When I got in to work, I found that the other schemas were missing a key column in the staging database table. That should never happen. We got a database configuration management issue.

So I thought we could just add the column everywhere. Some more analysis showed me this would not correct the whole problem. In the end I decided to recode the stored procedure to use another technique to derive the necessary data using some assumptions. This whole episode made me a bit leery about the state of our databases.

The Dreaded ORA-01848

I got a phone call and email from the customer this morning. The new version of our software was bombing. The error was ORA-01848: day of year must be between 1 and 365. Ouch. That looks like such an amateur programming error.

So I traced where we were doing the date stuff in our code. Turns out we were trying to compose a DATE value. The input to the date was some data in the database. Yep. The customer test data was invalid. I pointed this out to them. Some of their days were well over 365 in value.

I got another call in the afternoon. They were still getting the ORA-01848. I walked them through to check whether the bay dates were fixed. They were. Must be something else. Made sure they were going to the right database. Yep. But there was only one place in the code where we did this date stuff.

Finally it hit me. Maybe some of their dates were too small. Yep. Had some records where the day was 000 (minimum must be at least 001). Now I am crossing my fingers. The moral of the story might be for me to validate all the data before using it in any date calculations.

Block Safety

I got a task to go find all transactions that have failed due to a bug, and retry them at night. It sounds simple enough. The retry really means to reimplement the logic that the application would have done at the hands of a user.

My only real concern was what would happen if one of those transactions ran into a problem at night. Sounds like a job for exception processing. At first I just stuck an EXCEPTION handler in the middle of the loop. Of course that resulted in compilation errors.

Well what was I to do? Had to recall that the EXCEPTION is a part of a BEGIN-END pair. So I slapped a BEGIN-END block in the middle of my loop. Within that I added my exception handler. Now the code is rock solid. That is, even if there are some errors, this script will go on.

Home Made Locks

I am working on some high priority problems my previous team is having. The customers are mad and want a fix yesterday. The applications keep aborting with the same error messages. I traced this down in the code to some object locking code.

This was Oracle Pro*C code that implements locks with records in a locks database table. When the users need to obtain a lock, they try to insert a record in this table. If anybody else has already inserted a similar record (has a lock), then the second lock immediately fails.

When the lock fails, the application displays an error message and aborts. This really irks our customer. So the first order of business is to make sure the application does not abort in this scenario. Next I figure we need the application to wait around a while until the lock might become free. why not let the Oracle database manage this contention? I figure somebody has already solved the queued lock problem. Why try to implement it again in our own code, especially when our implementation will be substandard.

Database Links

I need to create a lot of test data for unit testing some new code. Now I already had some good data in another database. So I used a tool to create some scripts that insert the data into the new database. There were some problems with this technique. My database tables have a lot of columns in them. The creation of insert scripts by the tool puts the whole insert statement on one line. This line turns out to be much too long for SQL*Plus to process. So I started to manually break up the lines. There had to be a better way.

Database links came to the rescue. You create the objects by specifying which database to connect to. You also need to provide login credentials when creating the link. Then you can perform queries that access the tables from the other database through the link. This was awesome. The link itself becomes an object in your schema. I hear you can even connect to non-Oracle database. I have no need for that feature though.

You do need to make sure the remote database you are linking to is in the tnsnames file on your server. Remember to do a COMMIT after you run the query through the link to release the rollback segment in the other database. The link operates like a pointer. If you really wanted to, a link can be used as a poor man's replication technology. If you want to get some information on your links, check out the DBA_DB_LINKS view.

Commitment Woes

I ran a bunch of routines in a stored procedures to generate some data. Then I queried that database from the SQL*Plus tool I previously used to create the data. The checks showed the new data was there. Finally I logged into my operating system account, and ran some shell scripts that ran my whole test suite.

The funny thing is that I could not see the results over in my SQL*Plus session. That was strange. The shell scripts call code that commit the changes. I had to take a break to analyze this oddity. Then it came to me. My SQL*Plus work had not been committed in the first place. Therefore the test suite in a separate session could not see the uncommitted changes. Doh!

If you thought that one was bad, listen to this story. I made some changes to the database. Then I called a stored procedure to run some jobs to process that data. Worked good the first time. Then I repeated the process. Changed up some data, called the exact stored procedure. This time the changes I made did not take. WTF? I even did a commit but got wrong results. A lot of inspection got me the answer. The stored procedure was in a package that initialized some internal variables. This package initialization happens once per session. My jobs were being run by one SQL*Plus session. I needed to log out and log back in to get the package initialization to rerun and process the new data. Ouch. That's a tricky one.