We have a whole subsystem that loads data into our system on a daily basis. The loads code runs on a UNIX platform. From a high level, the requirements for the load are not overly complex. We have a number of file types with well defined formats that need to be inserted into our database each day. And there is some post load processing that occurs. The implementation of these requirements is a bit complex though. And our team has a new developer that is responsible for the loads code.The developer told me the load was failing in a test environment. So I recommended he load a file that has a known good format through the system. That could help him determine if it was a data problem or a software problem. He did some more investigation and found that one process in the load kept failing. Apparently a PL/SQL package would not compile. The package rang a bell with me. I told the developer that this package had a custom install script. The package depends on synonyms which are only created at run time for the loads program. So there is an install program which emulates the loads program by creating the synonyms first before compiling the package.
After getting information from me, the developer went on to find more problems with the PL/SQL package. I tracked down and asked the guy who wrote the package about the problems. They did not ring a bell with the developer. So this was not an easy PL/SQL package to deal with. In the end it appeared as if the developer was looking at an outdated version of the code for the package. Still it felt as if something were terribly wrong with the design of the database package. Normally you should be able to manually compile a database package by executing its source. If it is more complicated than that, the design may be at fault.
The new developer for the loads software has a lot of ideas on how to improve the loads subsystem. I expect this weird PL/SQL package may be part of the list. Any software that requires you to know the tricks is generally bad. Software should be written with maintainability in mind. The system we work on is 15 years old. As you might imagine, most of those 15 years were spent in maintenance mode. So it is of the utmost importance to do the software right in the first place. The hectic pace of software development does not always lead itself to this goal though.
