Debugging Your Code and Preventing Errors 377
13
1: DECLARE
2: v_MyChar VARCHAR2(20) := ‘test’;
3: v_NUMBER NUMBER;
4: v_Date DATE := SYSDATE;
5: v_counter INTEGER;
6: BEGIN
7: DBMS_OUTPUT.PUT_LINE(‘This is a Test’);
8: DBMS_OUTPUT.PUT_LINE(‘Of Syntax Error Debugging’);
9: For v_COUNTER IN 1..5 LOOP
10: DBMS_OUTPUT.PUT_LINE(‘You are in loop: ‘
11: || v_counter);
12: END LOOP;
13: END;
14: /
This is a Test
Of Syntax Error Debugging
You are in loop: 1
You are in loop: 2
You are in loop: 3
You are in loop: 4
You are in loop: 5
PL/SQL procedure successfully completed.
Finally, you get some good results. The purpose of this example is to demonstrate the
following:
• One syntax error sometimes masks others. Fixing that one will bring the others to
light.
• The line number that Oracle flags as containing the error might not necessarily be
the location of the true error.
•Taking the time to type in your code carefully saves a lot of time during program

) for strings instead of single quotes (
‘
)
Keep these common errors in mind as you write code. Also, determine what your person-
al list of common mistakes is, and keep that in mind as well. Just the act of being con-
sciously aware of these potential mistakes will lessen the chance that you will make one
of them.
The following are some other things you can do that you might find helpful:
•Format your code. Indent constructs such as loops and
IF
statements so that you
can easily follow the logic flow, and so that you can easily spot missing
END IF
s,
END
s,
LOOP
statements, and so forth.
• Double-check expressions containing parentheses immediately after you write
them. The number of left parentheses should match the number of right parenthe-
ses.
• If you are coding an
IF
statement, start by writing the
IF
and
ENDIF
lines. That
way, you know that you have the beginning and ending of the statement written
correctly. Then back up and insert the needed code between those two lines.

•Using loops that never terminate
Logic errors are the hardest errors to debug, primarily because the compiler can’t even
begin to tell you where such an error occurs. You are totally on your own when it comes
to finding and fixing logic bugs. The main steps in debugging logic errors are to identify
the problem, narrow down the location of the problem, and then fix the problem.
The next few sections talk about problems that can occur because the order of operations
is not understood, or because of loops that aren’t coded correctly. Following that, you’ll
find a section talking about things you can do to help debug logic errors.
Order of Operations
Remember when students would ask if there are any real-world applications of
math? Well, understanding the order of operations is critical, not only in algebra,
but in PL/SQL and every programming language, database, and spreadsheet package you
might use. The order of operations states the order of precedence each operator is given.
Table 13.1 covers just a few of these levels, with the top level being the highest-priority
order. Day 3, “Writing PL/SQL Expressions” covers this topic in detail.
T
ABLE
13.1
Simple Order of Operations, from Highest to Lowest
Operator Description
()
Parentheses
*
,
/
Multiplication, division
+
,
-
Addition, subtraction

5: IF v_MyNumber = 7 THEN
6: EXIT;
7: END IF;
8: v_MyNumber := v_MyNumber + 2;
9: END LOOP;
10: END;
11: /
As you can see, this loop will never exit because
v_MyNumber
will never evaluate
to 7. Since it starts at zero, and is incremented by two each time, it will go from
6 to 8--skipping 7. To fix this, you could rewrite line 5 so that it looks like this:
IF v_MyNumber >= 7 THEN
This is a much safer way to terminate a loop, because it doesn’t matter whether the
v_MyNumber
value is an exact match or not. It won’t matter if the increment in line 8 is a
2 or a 3 or a 1. Whenever the value becomes greater than or equal to 7, the loop will ter-
minate.
Debugging Approaches for Logic Errors
When you find that you have a logic error somewhere in your code, there are several
things you can do to find it:
380 Day 13
I
NPUT
A
NALYSIS
17 7982 ch13 11/30/99 1:10 PM Page 380
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Debugging Your Code and Preventing Errors 381
13

data that might not be used currently in the system, but that could possibly be entered by
the user, and so on. For example, if a program uses only positive numbers, enter a nega-
tive number as test data. In addition to testing the unusual cases, your test environment
should also include a reasonable volume of typical production data.
Setting up test data and testing all possible outcomes is critical in debugging any applica-
tion. A major advantage of having a predefined test environment is that it allows you to
document a series of tests and repeat them each time you modify your code. Taking a lit-
tle extra time to do thorough testing will benefit you greatly down the road.
17 7982 ch13 11/30/99 1:10 PM Page 381
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Narrowing Down the Location of a Bug
Suppose you 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, espe-
cially because 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. Determine the overall process.
2. Determine where, when, and how frequently the error occurs.
3. Determine what outputs are invalid.
4. Determine what inputs and calculations make up those outputs.
5. Determine what does work. (This question can help in determining the cause.)
6. Define the problem.
7. Trace inputs, intermediate computations, and outputs.
8. Step away from the problem.
9. Ask for help. Software bugs have been discovered this way!
10. Document the solution.
The next few sections talk briefly about each of these steps.
Determining 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 trouble-
shooting a reinsurance application much more difficult. If you have been called in to

Determining What Does Work
Asking 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 properly, check the other modules that access this pro-
cedure. If they all have the same problem, it is the module. If all of them process proper-
ly, 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, defining the problem 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 prob-
lem. Many people try to define the problem first, and take away the symptoms with
“workaround” coding rather than find the true root cause, which could resurface at any
time.
Tracing Inputs, Intermediate Computations, and Outputs
To help narrow down a problem to a specific module, and then to specific lines of code
within that module, you can use the
DBMS_OUTPUT
package to output the values of key
variables as the code executes. You can also write a debugging package—as you’ll see
later in this lesson—to log this information to a text file or a database table. Writing
debug output to a file eliminates the problem of having it scroll off the screen too quick-
ly, and also prevents display forms from being overwritten.
17 7982 ch13 11/30/99 1:10 PM Page 383
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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.

pinpoint most problems. If you don’t have a debugging tool available, there are still a
384 Day 13
17 7982 ch13 11/30/99 1:10 PM Page 384
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Debugging Your Code and Preventing Errors 385
13
couple things you can do. The
DBMS_OUTPUT
package can often be used to good effect.
You can use it to display the values of key variables as a procedure executes. If you want
to get a bit more involved, you can create a simple debugging package to log debugging
messages to a disk file.
Using
DBMS_OUTPUT
as a Debugging Tool
The
DBMS_OUTPUT
package is described in great detail on Day 17, “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 almost always output to the screen.)
The primary use for
DBMS_OUTPUT
when debugging is to display the values of key vari-
ables as a procedure or function executes. This is a time-honored approach to debugging.
The key is to display information that will allow you to narrow down the focus of your
search. For example, if you display a critical variable before and after a function call, and
the value was correct before the call but incorrect afterward, you should focus your
future efforts on the code that you called.

I
NPUT
17 7982 ch13 11/30/99 1:10 PM Page 385
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The statement in Listing 13.3 creates the package header, which defines the procedures
available within the package.
L
ISTING
13.3
Defining the
DEBUG
Package Components
1: CREATE OR REPLACE PACKAGE DEBUG AS
2: /* Procedure OUT is used to output a comment of your
3: choice, along with the contents of the variable. The
4: Procedure OUT statement defines the format of the function */
5: PROCEDURE OUT(p_Comments IN VARCHAR2, p_Variable IN VARCHAR2);
6:
7: /* Procedure Erase is used to erase the contents of the file.
8: Used to start a new debugging process. Good idea to call
9: this function first. */
10: PROCEDURE Erase;
11: END DEBUG; -- End Definition of package DEBUG
12: /
Package Created
After creating the package header, you can now enter and execute the package body as
shown in Listing 13.4.
386 Day 13
I
NPUT

5: /* Use A to append all output being sent to the file */
6:
7: v_MyFHOUT := UTL_FILE.FOPEN(‘c:\a’,’debug.txt’,’a’);
8:
9: /* Display System Time and Date as MM-DD-YY HH:MM:SS
10: followed by comments and the contents of the
11: variables. Each element is surrounded by quotation marks and
12: separated by a comma to create a comma separated value file */
13:
14: UTL_FILE.PUT_LINE(v_MyFHOUT,’”’||
I
NPUT
17 7982 ch13 11/30/99 1:10 PM Page 386
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Debugging Your Code and Preventing Errors 387
13
15: TO_CHAR(SYSDATE,’mm-dd-yy HH:MM:SS AM’)
16: || ‘“,”Comment: ‘ || p_Comments ||
17: ‘“,”’ || p_Variable || ‘“‘);
18:
19: /* Close the file handle which points to debug.txt */
20: UTL_FILE.FCLOSE(v_MyFHOUT);
21:
22: EXCEPTION
23: /* Create Exception to simply display error code and message */
24: WHEN OTHERS THEN
25: DBMS_OUTPUT.PUT_LINE
26: (‘ERROR ‘ || to_char(SQLCODE) || SQLERRM);
27: NULL; -- Do Nothing
28: END OUT; -- End Execution of Procedure OUT

The
DEBUG.OUT
Procedure
The
DEBUG.OUT
procedure enables you to log debugging messages to a file called
debug.txt
. The procedure automatically includes the system date and time with each
O
UTPUT
17 7982 ch13 11/30/99 1:10 PM Page 387
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
message. The procedure accepts two parameters: a debug message and the variable you
are tracking. Each time you call it,
DEBUG_OUT
appends the message and the value of the
variable to the file named
debug.txt
.
The
DEBUG.ERASE
Procedure
The
DEBUG.ERASE
procedure erases the contents of the
debug.txt
file by opening a han-
dle to the file in replace mode (
‘W’
) and then closing the file. This process creates an

7: day_in VARCHAR2(3);
8: day_work VARCHAR2(3);
9: date_out DATE;
10: date_work DATE;
11: BEGIN
12: debug.out (‘DATE_IN = ‘,
13: TO_CHAR(date_in,’yyyy mm dd hh mi ss’));
14:
15: --Grab the input day.
16: day_in := TO_NUMBER(TO_CHAR(date_in,’dd’));
17:
18: --Now, add the number of months requested by the caller
19: date_work := ADD_MONTHS(date_in, months_to_add);
20:
21: --Get the day of the month for the new date.
22: day_work := TO_NUMBER(TO_CHAR(date_work,’dd’));
23:
24: --If the day is unchanged, we are golden. If it was
25: --adjusted downward, that’s because the new month didn’t
26: --have as many days. If it was ajdusted upwards, we
27: --want to set it back to where it was.
28: IF day_in = day_work THEN
388 Day 13
I
NPUT
17 7982 ch13 11/30/99 1:10 PM Page 388
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Debugging Your Code and Preventing Errors 389
13
29: date_out := date_work;

Testing the
ADD_MON
Function
1: SQL> EXECUTE debug.erase;
2:
3: PL/SQL procedure successfully completed.
4:
5: SQL>
6: SQL> --The built-in ADD_MONTHS function keeps the date at
7: SQL> --the end of the month when it is there to begin with.
8: SQL> SELECT TO_CHAR(
9: 2 ADD_MONTHS(TO_DATE(‘29-FEB-2000’,’DD-MON-YYYY’),1),
10: 3 ‘DD-MON-YYYY’)
11: 4 FROM dual;
12:
13: TO_CHAR(ADD
14: -----------
15: 31-MAR-2000
16:
17: SQL>
18: SQL> --The ADD_MON function preserves the date as it is, except
19: SQL> --when it is out of range for the new month.
20: SQL> SELECT TO_CHAR(
21: 2 ADD_MON(TO_DATE(‘29-FEB-2000’,’DD-MON-YYYY’),1),
22: 3 ‘DD-MON-YYYY’)
A
NALYSIS
I
NPUT
/

Line 1 contains a crucial call to
DEBUG.ERASE
. This call creates an empty
debug.txt
file for use by subsequent calls to
DEBUG.OUT
. Lines 6–15 demonstrate
how the built-in
ADD_MONTHS
function operates. Because the input date
29-Feb
represent-
ed the last day of the month, the output date was adjusted so that it also represented the
last day of the month. Instead of returning
29-Mar
,
ADD_MONTHS
returned
31-Mar
. Lines
18–27 demonstrate how
ADD_MON
’s behavior is different.
ADD_MON
adds one month, but
preserves the day, resulting in the value
29-Mar
. The remaining lines test some other
cases that
ADD_MON

to reduce the amount of code to sort through when a problem occurs, but to be able to
reuse those modules in the future.
Defining Requirements and Planning Projects
When you develop a new application, you should spend a significant amount of time
defining the requirements of the users. Not only does this require some knowledge of the
business, but it should cover all possible input and desired output scenarios. 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 it is to under-
stand and plan for the application in the beginning: Doing so will save you a lot of time
and aggravation at the tail end of the project.
Always verify your understanding of the requirements with the business
users of the system. Tell them what you think you heard them say in the first
place. Make sure that they agree that you have a correct understanding of
the problem at hand.
Tip
There is one pitfall in obtaining user requirements, which I found out the hard way. I
coded an application that I felt met the user requirements for a financial department.
After reviewing the application with the end user, and discussing 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 were wrong. Not only should you relay back to the end
user what you perceive their requirements are, but if possible, you should try to verify the
process you are about to code with at least one other knowledgeable resource. 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.
17 7982 ch13 11/30/99 1:10 PM Page 391

5: p3 NUMBER;
6: BEGIN
7: IF p1 = 1 THEN
8: p3 := p2 * 1.10;
9: ELSIF p1 = 2 THEN
10: p3 := p2 * 1.05;
11: ELSIF p1 = 3 THEN
12: p3 := p2 * 1.04;
13: ELSIF p1 = 4 THEN
14: p3 := p2 * 1.03;
15: ELSIF p1 = 5 THEN
16: p3 := p2 ;
392 Day 13
I
NPUT
17 7982 ch13 11/30/99 1:10 PM Page 392
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Debugging Your Code and Preventing Errors 393
13
17: ELSE
18: p3 := p2 * 1.02;
19: END IF;
20: RETURN p3; --
21: END RAISE;
22: /
A quick glance at this code shows that there are no comments, and that the vari-
able names are not mnemonic. In order to follow the code, you would have to
first determine what
p1
,

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;
29: /
A
NALYSIS
I
NPUT
17 7982 ch13 11/30/99 1:10 PM Page 393
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
You can now follow the function, its purpose, what the variables are, and any
modifications made at a later date. What a difference commenting and proper
naming of variables makes!
Writing Assertions into Code
An assertion,in programming terms, is a test for a fact that should be true.
Assertions serve several functions. Their primary function is to prevent errors
from propagating further downstream in a process. Say you had a function that was never
supposed to return a negative value. You could actually place a check in your function to
be sure that a negative value is never accidentally returned. Listing 13.9 shows one
approach that you might take to this problem.
L
ISTING
13.9 A Function That Should Never Return a Negative Value

NALYSIS
N
EW
T
ERM
I
NPUT
A
NALYSIS
17 7982 ch13 11/30/99 1:10 PM Page 394
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Debugging Your Code and Preventing Errors 395
13
L
ISTING
13.10
An
ASSERT
Function
1: CREATE OR REPLACE PROCEDURE ASSERT (
2: condition IN BOOLEAN,
3: message IN VARCHAR2) AS
4: BEGIN
5: IF NOT condition THEN
6: RAISE_APPLICATION_ERROR (-20000,message);
7: END IF;
8: END;
9: /
This
ASSERT

where you can turn it off when you don’t need it.
Formatting Code
Another ounce of error prevention is the proper formatting of code. Here are some for-
matting suggestions that you can either adopt or modify for your specific environment:
•For each new block of code, indent two to five spaces.
•Use uppercase for keywords.
•Use mixed case for variable names.
•Precede variable names with a
v_
for variable,
p_
for parameters, and so on.
•Use one statement per line.
I
NPUT
I
NPUT
A
NALYSIS
A
NALYSIS
17 7982 ch13 11/30/99 1:10 PM Page 395
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Using Proper Indentation
Every time you start a new block of code, such as a loop, an
IF
statement, or a nested
block, you should indent to make the code more readable. Listing 13.12 shows an exam-
ple of poorly indented code.
L

7: v_MyNumber := v_MyNumber + 2;
8: END LOOP;
9: END;
10: /
Not only is the code now easier to read, but the indentation makes it obvious that
an
ENDIF
statement is missing after line 6.
396 Day 13
I
NPUT
A
NALYSIS
I
NPUT
A
NALYSIS
17 7982 ch13 11/30/99 1:10 PM Page 396
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.