ANALYSIS
Amazing—you are back to errors in the first line. In this case, the DECLARE statement does not have a matching END statement properly terminated. Change line 12 to read END;—you have missed the semicolon—and then reexecute the program.

v_MyChar VARCHAR2 := `test';
             *
ERROR at line 2:
ORA-06550: line 2, column 14:
PLS-00215: String length constraints must be in range (1 .. 32767)
ORA-06550: line 2, column 14:
PL/SQL: Item ignored
ORA-06550: line 10, column 21:
PLS-00302: component `PUTLINE' must be declared
ORA-06550: line 10, column 9:
PL/SQL: Statement ignored

ANALYSIS
Now you have even more new errors. Notice that VARCHAR2 is missing how wide the string is? Also, PUTLINE should be PUT_LINE in line 10. Change the VARCHAR2 in line 2 to read VARCHAR2(20), change PUTLINE in line 10 to read PUT_LINE, and reexecute your code. Your output should now be

OUTPUT

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

The purpose of this example was to demonstrate the following:

• One syntax error fixed could lead to several new errors.
• The area of the error that Oracle flags might not necessarily be even near the location of the true error.
• Typing the data in properly saves a lot of time when testing your program.
• Syntax errors can be avoided!

For fun and learning, you might want to create a utility that searches the saved SQL file (using the UTLFILE package) and not only checks syntax, but also will fix some of the errors automatically, such as spelling keywords properly, adding missing keywords, adding missing punctuation, and so on.

Logic Errors

Unlike syntax errors, logic errors do not stop a program from compiling. After a program is compiled and tested to some extent, logic errors can still occur. Possible logic errors include

• Not using proper order of operations
• Wrong calculation used

• Loops that never terminate
• New data not handled by an exception
• Data entered out of range or incorrect datatype than originally expected

This list could easily be hundreds of pages; however, I think you get the idea. Logic errors are the hardest to debug. You will be examining debugging techniques later in this chapter when you create your DEBUG package! The main steps in debugging logic errors are to identify the problem, narrow the area where the problem exists, and if the error is not obvious, use debugging tools. The rest of this section reviews some samples of logic errors.

Order of Operations

For some reason, when people leave algebra class, the order of operations seems to be easily forgotten. Remember when students in class would ask if there are any real-world applications of math? Well, understanding the order of operations is critical not only in PL/SQL, but in every programming language, database, and spreadsheet package you might use. The order of operations simply states the order of precedence each operator is given. Table 14.1 covers just a few of the levels, with the top level being the highest priority order.

Table 14.1. Simple order of operations table.

If two or more operators are on the same priority level, then the expression is evaluated from left to right. Take the following equation, which looks as if it should add two numbers and multiply 9 by the result:

5 + 3 * 9

Whenever I ask this question in the classroom, at least 25 percent of the class tells me the answer is 72. However, the order of operations tells you that multiplication should come first. In this case, 3 * 9 = 27, and when you add 5, the true answer is 32. What if you wanted to arrive at 72? Use parentheses around the expression you want to evaluate first.

Now (5 + 3) * 9 does = 72.

I find the order of operations to be a very common problem in areas of business, finance, statistics, and scientific application programming. This could have disastrous results, especially if your nuclear plant depends upon proper order of operations. The complete table can be found in the Oracle Database Guide. Study this guide, and even make a copy of it to keep near your desk.

Nonterminating Loops

Another common problem is loops that never terminate. As an example, take a look at the code in Listing 14.2.

INPUT
Listing 14.2. Example of an infinite loop.

 1: DECLARE
 2:     v_MyNumber NUMBER := 0;
 3: BEGIN
 4:     LOOP
 5:         IF v_MyNumber = 7 THEN
 6:              EXIT;
 7:      END IF;
 8:     v_MyNumber := v_MyNumber + 2;
 9:     END LOOP;
10: END;

ANALYSIS
As you can see, this loop will never exit because v_MyNumber will never evaluate to 7. These are just a few examples of logic errors. The next section goes into some debugging techniques.

The Nontool Approach

Oracle does not have any true built-in debugger packages. These can be acquired as additions from Oracle Forms, Oracle Developer/2000, or third-party applications. You could even create your own debugging package with pipes, UTLFILE, and so on, which you will do in the section "Using Tools to Help in Debugging a Program."

Setting Up a Test Environment

Although testing might seem like common sense, you would not believe how many major corporations either don't have test environments for all their applications or simply put code into production without thoroughly testing the code in a test environment. This problem occurred at one firm that used a program to calculate the raises for employees. The managers would enter a percentage such as .05. Unfortunately, the code took the current pay rate multiplied by the percentage of the raise and assigned this to the new value of the hourly rate. So people with a 5 percent raise on $10.00/hour now were making 50 cents per hour! The formula should have been pay _rate * (1+raise). Imagine being the IT manager trying to explain this "glitch" to your coworkers.

Unfortunately, this problem is more common than it might seem. Another case concerns code that works fine when initially placed in production, but it affects code in later production processes. Whenever possible, always set up a test environment and test extensively. The testing should not be done by the programmer, but by someone else who