would correct this problem by assigning New Rate to the value of Pay Rate. This way, no one would ever potentially have a NULL or a 0 hourly pay rate. This will prevent bugs in the future.

You now need to look at the function RAISE. You have narrowed your search to the code in Listing 14.7.

INPUT
Listing 14.7. The RAISE function.

 1: CREATE OR REPLACE FUNCTION RAISE (
 2:           p_paylevel INTEGER, -- parameter for input of raise level
 3:           p_payrate NUMBER)   -- parameter for input of pay rate
 4: /* The purpose of this function is to calculate ANNUAL raises
 5:    for all of the hourly employees, based upon their raise level
 6:    values 1-4 and all others.  */
 7:      RETURN NUMBER IS
 8:      v_newrate NUMBER; -- New Hourly Rate After Raise
 9: BEGIN
10:      IF p_paylevel = 1 THEN
11:           v_newrate := p_payrate * 1.10; -- Promotion Raise
12:      ELSIF p_paylevel = 2 THEN
13:           v_newrate := p_payrate * 1.05; -- Exceeds Rate
14:      ELSIF p_paylevel = 3 THEN
15:           v_newrate := p_payrate * 1.04; -- Hi Meets Rate
16:      ELSIF p_paylevel = 4 THEN
17:           v_newrate := p_payrate * 1.03; -- Meets Rate
18:      ELSE
19:           v_newrate := p_payrate * 1.02; -- All Others
20:      END IF;
21:      RETURN v_newrate; -- Returns new paylevel rate to procedure
22: END RAISE;

ANALYSIS
At least the function is commented and formatted to make debugging easy. A quick glance at the comments, or an even quicker glance at the possible pay levels, shows that a level 5 is not defined, but falls into the category of all others, who get a 2 percent raise. Although Human Resources logically assumed that using the hourly table for consultants would be an easy nonprogramming fix, it has resulted in a problem almost one year later. The quick fix is just a matter of accounting for a level 5 as seen in Listing 14.8.

INPUT
Listing 14.8. Creating the RAISE function.

 1: CREATE OR REPLACE FUNCTION RAISE(
 2:           p_paylevel INTEGER, -- parameter for input of raise level
 3:           p_payrate NUMBER)   -- parameter for input of pay rate
 4: /* The purpose of this function is to calculate ANNUAL raises
 5:    for all of the hourly employees, based upon their raise level
 6:    values 1-4 and all others.  */
 7:
 8: /* On June 24, 1997, added feature to eliminate consultant raise,

                                                                        continues

Listing 14.8. continued

 9:   which is pay level 5 */
10:
11:      RETURN NUMBER IS
12:      v_newrate NUMBER; -- New Hourly Rate After Raise
13: BEGIN
14:      IF p_paylevel = 1 THEN
15:           v_newrate := p_payrate * 1.10; -- Promotion Raise
16:      ELSIF p_paylevel = 2 THEN
17:           v_newrate := p_payrate * 1.05; -- Exceeds Rate
18:      ELSIF p_paylevel = 3 THEN
19:           v_newrate := p_payrate * 1.04; -- Hi Meets Rate
20:      ELSIF p_paylevel = 4 THEN
21:           v_newrate := p_payrate * 1.03; -- Meets Rate
22: ELSIF p_paylevel = 5 THEN
23:           v_newrate := p_payrate ; -- Consultants who get no raise
24:      ELSE
25:           v_newrate := p_payrate * 1.02; -- All Others
26:      END IF;
27:      RETURN v_newrate; -- Returns new paylevel rate to procedure
28: END RAISE;

ANALYSIS
Although the code in Listing 14.8 will do the trick, what if the problem reoccurs? At least you can document in the code the solution to the problem as seen in Listing 14.8. The reason I called this a quick fix is that it does not address long-term needs. The way to correct this long-term so that this error never occurs would be to create a table called raise_level. This would have the raise level INTEGER value along with the raise increase (or 0 if no raise to be given). On the hourly table, before the raises are calculated, I would reset all pay raise levels to no raise. I would then read in the new raise levels from a table where management has decided their employee's fate. This table can then be read in by using a cursor to update the new pay rates. I would also track the updated rates by using a pipe, which is discussed on Day 19, "Managing Database Jobs."

By using the nontool approach, you were able to narrow down the area significantly. After you identify the probable area, you can then use your tools to verify where the problem is and fix the problem. In this case you used DBMS_OUTPUT, but I would use DEBUG.OUT to output to a file to see what is going on from the function call in case other levels were affected because the test samples might have missed the one or two values that can cause a problem. You could read the data into a table and run a manual calculation compared to the new rate to see what is going on for all cases, and kick out a variance report.

Error Prevention and Planning for
Debugging in the Future

If your code needs debugging in the future, you can plan to make it easier through the use of liberal commenting and properly formatted code. To reduce the probability for errors, you

should approach the design phase by checking for all possible scenarios and outcomes. In addition, you should design your code in modules, not only to reduce the amount of code to sort through when a problem occurs, but to be able to reuse these modules from other programs or even other databases. Finally, you can purchase tools from Oracle or other third parties that will help you not only to write the code, but also to debug code when things go wrong.

Defining Requirements and Project Planning

The largest portion of an application should be defining the requirements of users. Not only does this require some knowledge of the business, but also all possible input and desired output scenarios should be covered. Someone knowledgeable in the industry should verify all calculations. What do you gain by sitting with the end users and verifying the application? You begin to understand the business and its needs, and you might be able to make suggestions that could aid in decision-making processes, reduce work time for manual processing, improve productivity, and so on. Not only that, it is easier to troubleshoot the system and identify problems before the application is placed in production. I can't stress enough how important understanding and planning for the application in the beginning are: They will save you a lot of time and aggravation at the tail end of the project.

There is one pitfall in obtaining user requirements, which I found out the hard way. Initially, I had coded an application that I felt met the user requirements for the Financial Department. When the end user was reviewing the application, when we discussed the outputs and how the calculations worked, I wound up redesigning the application to meet the new perceived needs. Unfortunately, due to a language barrier, I had the application coded correctly the first time, and the new changes implemented were wrong. Not only should you relay back to the end user what you perceive their requirements are; if possible, try to verify the process you are about to code with at least one other knowledgeable resource—if one exists! The next time I ran into a similar problem, by working with two resources, I was able to resolve any issues about what should be coded. The new application was able to recover balances never billed due to a prior misunderstanding of the process in existing legacy code.

Using a Modular Approach to Coding

When developing your applications, you should take a modular approach to make debugging easier, and also an added benefit of creating reusable code. For instance, in a payroll application, you could further divide this up into several procedures to

• Calculate gross wage
• Calculate FICA
• Calculate Federal withholdings
• Calculate state withholdings
• Take out for benefits such as flexible spending or insurance