A major benefit of going to a file instead of outputting to the screen is that you can run the program uninterrupted by screen outputs, and examine the file at your leisure. You can even build a package or start a trigger to a procedure to read this file into a database, which you can then query. The order output to the file is the order in which the code is executed.

The DEBUG.EMPTY Procedure
The DEBUG.EMPTY procedure simply erases the contents of the DEBUG.TXT file by opening a handle to the file in replace mode (`W') and then closing the file, which creates an empty file. The format for the procedure DEBUG.EMPTY is

PROCEDURE EMPTY;

Using DBMS_OUTPUT as a Debugging Tool

The DBMS_OUTPUT package is described in great detail on Day 18, "Writing to Files and the Display." This package will either pass information to a buffer that can be retrieved, or it can display information to the screen. When debugging a process, if I use DBMS_OUTPUT, I always output to the screen.

Because PL/SQL really has no output, previous chapters used the DBMS_OUTPUT.PUT_LINE procedure when they demonstrated constructing PL/SQL. However, in order to see any output to the screen, from SQL*Plus, you must type

INPUT

SET SERVEROUTPUT ON

To disable sending output to the screen, you would type at the SQL*Plus prompt

INPUT

SET SERVEROUTPUT OFF

If you do a lot of programming and application development in Oracle, I highly recommend that you add SET SERVEROUTPUT ON to your login script. This rest of this discussion centers on the DBMS_OUTPUT.PUT_LINE procedure.

The Syntax for the PUT_LINE Procedure
The format for this procedure is

PROCEDURE PUT_LINE(data_to_display IN VARCHAR2(DATE,NUMBER));

Because the function is overloaded, the data_to_display can be of type VARCHAR2, DATE, and NUMBER.

How is DBMS_OUTPUT utilized for debugging purposes? I use this extensively in small procedures in the development process, not just to debug, but also to monitor the values of variables as they enter and execute the procedure. By displaying the values to the screen with comments and knowing the outputs based upon your inputs, you will be able to narrow down, if not identify, the problem area.

You will now create a scenario with a logic error by first creating the function from Listing 14.5. Enter and execute this code to create the function RAISE.

INPUT
Listing 14.5. 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:      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
The purpose of the newly created function RAISE is to calculate the annual raises for hourly employees only based upon a raise level of 1_4, and all others. The new pay rate is then computed by taking the pay rate passed from the procedure multiplied by the percentage increase determined by the raise level, also passed from the function. You then return the new pay rate to the calling procedure.

Defining the Background
ABCDEF Corp. designs alphabet products for young children. Recently, they have begun to use consultants for applications development because they lack the knowledgeable resources. The Human Resources department has had no experience with consultants. To handle payroll, they enter the consultant's pay rate information into the hourly employee database. Each year, raises are given to all hourly employees but not to consultants, who do not receive raises because they are compensated by a generous hourly rate.

The Symptoms of the Problem
Raises have been processed last week. Consultants have been walking around with a funny smile and a gleam in their eyes. The payroll seems to be over the budget. Someone notices that the amount spent on consultants has increased substantially. An Oracle consultant is brought in to identify and fix the problem.

Narrowing the Problem
You know there is a payroll problem, and you know that it occurred after the last round of raises. You can now concentrate on the payroll modules. The next step is to find the source of data, which is the hourly payroll tables. The next step would be to take one of each type of data, in this case one of every type of raise level, which is 1 through 6. You would then write down the old pay rate and the new pay rate. After contacting Human Resources and having them verify the new pay rates from the initial inputs, you determine that levels 1_4 and 6 are calculating properly, but all level 5 rates are showing a raise when level 5 users should not be getting any raise. Further sampling of level 5 employees reveals that all level 5 employees are consultants who are getting raises when they shouldn't be getting any raise. No wonder they were so happy! You can now test the RAISE procedure with sample data, using DBMS_OUTPUT to track the variables.

Now you need to create the procedure that calls the function RAISE. You also need to track your variables with the DBMS_OUTPUT package. Enter and execute the code in Listing 14.6. Make sure you have typed from the SQL*Plus prompt SET SERVEROUTPUT ON, and press Enter to make sure all output is displayed to the screen.

INPUT
Listing 14.6. Calling the RAISE function.

 1: DECLARE
 2:      v_paylevel INTEGER := 5; -- level for raise
 3:      v_payrate NUMBER := 55.25; -- hourly pay rate
 4:      v_newrate NUMBER ; -- new pay rate adjusted for raise
 5: BEGIN
 6:      DBMS_OUTPUT.PUT_LINE(`Values before RAISE function: Payrate: ` ||
 7:      v_payrate || ` Raise Level: ` || v_paylevel || ` New Rate: ` ||
 8:      v_newrate);
 9: /* Calculate the new pay rate by calling the RAISE function */
10:      v_newrate := RAISE(v_paylevel,v_payrate);
11:      DBMS_OUTPUT.PUT_LINE(`Values after RAISE function: Payrate: ` ||
12:      v_payrate || ` Raise Level: ` || v_paylevel || ` New Rate: ` ||
13:      v_newrate);
14: END;

In a normal environment, you would have an hourly employee table that you would access row by row with a cursor, and then insert the updated values back into the table. To keep things simple, you will assume that your one test case is one row from this table.

When you execute this code, your output should be

OUTPUT

Values before RAISE function: Payrate: 55.25 Raise Level: 5 New Rate:
Values after RAISE function: Payrate: 55.25 Raise Level: 5 New Rate: Â56.355

ANALYSIS
Right away, you know that the problem occurs in the function RAISE. One other item that you should note is this: What is the problem with New Rate? You should never have an uninitialized variable. To do so could cause problems at any point in time. I