Experience and Exuberance

I have every DBA's dream, a new developer, fresh out of school (overseas). Trained and experienced in Java, JSP, .NET, PL/SQL and SQL. He started about 2 months ago and was first learning one of our large apps to be an application support person before being turned over to me for training in everything oracle. Great guy, full of ideas. Nothing but good comments about him.

I am under instruction to teach him how in my mind to properly code and develop oracle based applications in our environment. Suddenly, this dream turned into a nightmare. Being a lean shop, we have normally outsource our big applications or have gotten them "of the shelf". We have a mess of different versions of the database, different contractors supporting little bits and pieces of some applications, and not others. Home grown integration programs, ETL utilities that date back to DOS 5.0 days converting data from a mainframe system that was to be replaced in 1998 (commissioned 1990) and is still ticking today. The only standard we have right now, is we have no standards but are working towards one. From someone that has been around for ages, it all makes sense - until you try to explain it to someone new. Oh Boy.

I spent about 3 hours with the fellow, going over basics. Most of which he knew, but he was polite and let me ramble on to him anyway. When I say basics - I mean basics. Don't use weak passwords, name your accounts descriptively, use packages instead of procedures/functions, primary keys, foreign keys, do the work in the database- not the front end, bind variables, bind variables, benchmark and test and document before moving from dev/test to production. I am here to help.

I started to focus in on a single system, one that interfaces with our Oracle Financials system and always has a list of changes wanted/needed to the interface. It is, based on storage and user base, the 3rd largest application in our company. The owner of the software vendor actually used to work for our company, he quit and started selling us software. He and his people write good stuff, very focused, no fluff.

The particular change I started to describe to him, is one I thought would a good introduction to the thousands of lines of interface code, we interface accounts payables invoices, fixed asset information, general ledger and even accounts receivables into and from our financials system. The users are constantly choosing the wrong financials period when they post their data. They must choose a period because they can post into many periods so we can't figure it out for them.

When they pick a wrong period - specifically a period that is closed - it involves IT support to go in, clean up the source system and the financials system and let the users send it into the proper period again. Only about 30 minutes work, but one month end we had 9 users do it wrong at different times of the day. We need something to look across into the financials system and see what period is opened and closed. Easy enough.

We go into the financials system, and work out a view on the tables that show what period is opened per module. He comprehends that all with great speed.

We then go into the source system and start to work out the best place to put the check. We decide it had better go at the very beginning of the generation phase as not to waste too much time of the users, so I show him where it should be, he understands and I let him get to work. About 2 - 3 hours worth of coding work to get it all completed on the backend and on the front end.

He sends me an email early the next morning, he documented everything, he had decided to put the code into a trigger on the table that holds the batch information instead of in the PL/SQL that does the generation because the PL/SQL is controlled by the application vendor, everything links to that table for the header/details, a single record is created no matter how much data is being sent. Seems to be a reasonable place to put it. I go over his documentation (not the code) and it seems to make sense. I have him clear it with one of the "power users" and then give the go ahead to put it into test and let some users at it.

A couple of days go by and I inquire with him on why I haven't heard from him.
He says he can't figure it out, he runs his code in an anonymous block and returns in about 2-3 seconds. Yet he puts it into the trigger on the batch table, creates a batch and he has waited hours for the batch to return. Interesting, ok, well I ask him to create a test schema, take a copy of the tables and lets work on it in there. We spend an hour or so and I can't see any problems. I watch him put it into the test database, looks good. We generate a batch and well... minutes turn to tens of minutes. I look at the code... looks great, copy it out, works great.

WTF? I look into it it, I just can't figure it out. So far we have spent about a 5 days on these 16 lines of code. Everything works. I take a step back. Think about things. Stop thinking like developer, go to DBA mode.

Go to DBA_objects and search for objects changed in the last 5 days. Find the before insert for each row trigger on the batch table, and what's this, changes to the before insert for each row table on the invoice detail table. Take a look at the trigger. There is an exact duplicate of his perfectly working code in the trigger. So, basically, for every record put into this invoice detail table, it queries our financials system to make sure the period attached to the invoice is valid and open. Take a look, the test batch he was trying to generate would have over one hundred thousand detail lines.

"Whoops there it is".

Quick fix and a recompile and his code now works wonderfully well. No perceptible difference in speed to the users and they now can't generate to a closed period.

My experience should have told me to keep better track of his exuberance.

He is still a great guy, and now really focused on what he has open in what window.