has no knowledge and just tries to break the program. Users make the best beta-testers! If a test environment is not available, back up the data before proceeding. Also, make the changes at off-hours so as to not affect employees.

Setting Up Test Data

After you have set up your test environment, you need to test the code with sample data. One method to determine test data is to come up with a spreadsheet with a list of all possible values, or range of values, and then manually calculate the output. The whole purpose of programming is to work with the inputs, and output the desired results. Use test data that might not be used currently in the system, but could possibly be entered by the user, and so on. For example, if a program uses only positive numbers, enter a negative number as test data. The program should reject the entry—but only if this capability has been implemented. Furthermore, if you do not know the industry implicitly, seek help from your end users to provide you with test data. The final test would be to copy data in actual production, and see if you mirror the output.

Setting up test data and testing all possible outcomes is critical in debugging any application. A major insurance company had a programmer who sent only two examples through in a test environment, where the desired outcome was reached. Unfortunately, the code caused major problems in production that took two months to identify and an additional three months to fix; it has been two additional months and the data that was corrupted is still not fixed. Test everything from inputs to error handling and exceptions. The little extra time for more thorough testing will benefit you greatly down the road.

Narrowing Down the Location of a Bug

Suppose you do encounter a case in which outputs do not match the desired output. What steps do you take next? No matter what, you need to narrow down the search area, especially since large-scale applications have millions of lines of code. The steps I would take to troubleshoot for a logic error bug are as follows:

1. What is the overall process?
2. Where, when, and how frequently does the error occur?
3. What outputs are invalid?
4. What inputs and calculations make up those outputs?
5. What does work? (This question can help in determining the cause.)
6. Define the problem.
7. Use one of the many tools to trace your inputs, intermediate computations, and outputs.
8. If you're having a tough time, step away from the problem.

9. If you're having an extremely tough time, don't be afraid to ask for help. Software bugs have been discovered this way!
10. Document the solution.

What if the error is a syntax error in a new procedure you created and the compiler does not seem to pinpoint the true location of the error? What I do is to comment out a block of code until the procedure compiles. Then, I uncomment the area until I receive the same error, which usually identifies the problem rather quickly!

What Is the Overall Process?
Before you can troubleshoot, you should have some idea of the overall process and how it relates to the business. If you have no reinsurance knowledge, it will make troubleshooting that much more difficult. Even if you don't know the process, at least seeing the entire process should clarify and support your findings in troubleshooting.

Where, When, and How Frequently Does the Error Occur?
You should know where in the system the problem is occurring. What forms are involved? What data is involved? When does the problem occur? How frequently does this problem occur? Every time a user clicks the Send button? Every time a form is saved and the data is inserted into the table? Only when uniform #23 is inserted into the basketball database? All of these questions will help to determine the root problem.

What Outputs Are Invalid?
When attempting to define the problem, if it is not a systems crash but an error on output, attempt to define all outputs that are invalid. Such questions for a banking industry could be: Which accounts get a service fee when they are not supposed to? How much is the service fee? (You can use this information to see which variable references this value in a table.) How often does the error occur? What was the last transaction that occurred before the service fee? (Perhaps a trigger is causing the problem when updating the table.) What date does the error occur? (If the date is fixed, this will help to narrow down the problem area.) In reality, there should be no "random" problems ultimately even though the problems might initially seem random. You should eventually see a pattern evolve, which should lead you directly to the problem.

What Inputs and Calculations Make Up Those Outputs?
If you know a bank fee is accessed, you should now start a trace to which modules, programs, triggers, procedures, and so on are involved with processing a late fee. What tables do your inputs of a service fee come from? Knowing all possible locations can now help you trace the problem more effectively.

What Does Work?
The question "What does work?" might seem like an odd idea, but believe it or not, it is very effective. If you suspect that a procedure is bad because the data you pass to the procedure is not processing the data properly, check the other modules that access this procedure. If they

all have the same problem, it is the module. If all of them process properly, and you pass the same number of parameters, maybe it is something in your module. If the range of values you pass is different than that of the other modules accessing the procedure, it could be an out-of-range error in the procedure.

Defining the Problem
Usually, this is the most difficult part. If you have worked your way through proper troubleshooting and the asking of questions, you should now be able to determine the root cause of the problem, and where to start your search to fix the problem. Most people try to define the problem first, and take away the symptoms with "workaround" coding rather than finding the true root cause, which could resurface at any time. It could even be something as simple as this: New values added to a lookup table are out of a valid range specified in the PL/SQL coding. Another possibility is that the new range goes to a default, which is not the proper path for the data to take.

Tracing Your Inputs, Intermediate Computations, and
Outputs
You can use the DBMS_OUTPUT package to output the values of variables as the code executes, which will allow you to pinpoint the problems fairly quickly. You can also write a debugging package similar to the one in this book, which outputs text to a file for viewing. Sometimes you do not want data output to the screen because it moves too quickly, or you are using forms to access PL/SQL and want the user to take you through the problem. By outputting to a file, you can go back and examine the text until you find the problem. Tracing outputs will be discussed in greater detail in the section "Writing a Debugging Package."

Stepping Away from the Problem
Have you ever had the solution to the problem stare you in the face but you did not see it? All too often, we get so involved in trying to find and eliminate the bug that we get too frustrated and start to repeat steps that we have already eliminated. It usually helps to take a break and get away from the problem. I personally have deleted data files in a subdirectory accidentally when trying to fix a bug. The fingers are sometimes quicker than the mind! (Yes, I did have a backup!)

Don't Be Afraid to Ask for Help
If after examining the code, it appears that you have followed all punctuation and syntax, and you have a complete understanding of the function package, procedure, and so on, don't be afraid to ask another consultant or the product manufacturer for help. I worked at a company that was using a program that supposedly compiled with computing standards, until a repeating bug proved that the error was in how the program handled the standard, not an error in the coding. Sometimes an extra set of eyes can pinpoint the problem. In addition, you might learn some new tips and tricks to speed up development or troubleshooting the next time around.