Bad Where Clause

A developer dropped some tables in their schema. They did this manually outside of our application suite. The applications expect the tables to exist. The result is that our applications blow up when you select certain data. I traced this problem back to the developer who actually dropped the table. I decided to write a script to find all occurrences of the problem. It was supposed to correct the application data based on the absence of the database table. I did a couple quick tests and all looked good. However I am getting into the practice of writing unit test scripts to fully verify my code. I was surprised to find that my script was actually deleting a lot more data than it should be doing.

I stared at the script for a long time. And I kept running through my unit tests again and again. But the script kept eliminating all the application data, good and bad alike. This was troubling. As a software engineer, logic problems like this should be trivial for me. It did not help that we had a lot of distractions at the office. However I am trained to be able to work in any conditions and produce correct code. So I just added some restrictions to the WHERE clause in my script. Then the script did not delete all the application data. However I still did not understand what was going on. The only thing I could do was go over the code line by line until I saw the light.

This was the pseudo code for the script:

Cursor to loop through app application records
Check if table exists
If table does not exist
Delete application data

It does not get much simpler than this. Then I finally did spot the error. The where clause in my check was like WHERE column = table.column. It should have been WHERE column = cursor.column. At that point I felt pretty stupid. I spent the whole afternoon looking for that one. I write about it here to warn you in the rare case that you write or stumble upon such a problem in your own journey.

Pro*C to Stored Proc

Our development team is currently coding some new features into the system. A senior developer got assigned a couple tasks to complete. I performed the design for one of these tasks. And I provided the design to the developer. I purposefully left the database technology out of the design. This was because I figured it would be easiest for the developer to use a technology they were familiar with. The task got broken up and assigned to one other developer as well. The first guy decided to use Pro*C to access the database. He figured this was the easiest for him.

The application being modified already uses Pro*C for some of its database access. So a decision to use Pro*C did not seem unusual. And I knew that this particular developer was familiar with it. However the other developer requested that the common code be put in a PL/SQL stored procedure. That way it could be written once. This stored procedure could be called from the Pro*C function. And since this guy was the team lead, he made the final call. The other developer expressed reservation about this decision. As a result, the coding task got reassigned to me.

Personally I love writing code in PL/SQL. And I figured it was no big deal to call a stored procedure in Pro*C. You just do something like this:

EXEC SQL EXECUTE
BEGIN
pkg.proc(:var);
END;
END-EXEC;

There were other examples of this pattern in the code for the application being modified. So I coded in those hooks. I also wrote the common PL/SQL back end stored procedure. The procedure was actually quite easy to code. I knew we made the right decision when the database table being accessed got some column name changes. I just modified the stored procedure and everything worked fine.

Maybe the original developer did not want to agree with our team lead. I normally do not take this stance. It is fun to write code. A good developer should be able to write the code in any language and with any technology. Those details are usually not important. Calling a stored procedure in a package from Pro*C is also easy. I think I might give the link to this blog post to the original developer. He might learn a thing or two. And I am not talking about Pro*C syntax.

Real DBAs

We have two types of DBAs on our project. There is the DBA who works exclusively on our team, but has limited admin rights. Then there are the computer center DBAs that run the show. These latter folks are the security police for our system.

There are benefits and drawbacks to this two tiered DBA setup. Right now we are suffering from some of the drawbacks. I have written about this on my Software Maintenance Blog with a post entitled Database Lockdown.

I encourage you to check it out. It is most relevant, not only because we run an Oracle 10g database on the back end, but it might affect your work.