Day 14

Debugging Your Code
and Preventing Errors

by Timothy Atwood

No matter how good a programmer you are, inevitably you will make some coding errors. These errors are composed of syntax and logic errors. Today's lesson demonstrates how to locate these bugs and how to reduce the number of coding mistakes you make. The topics today include

• Syntax errors
• Logic errors
• Debugging without tools
• Creating a debugging package
• Preventing errors
• Available add-on tools

Syntax Errors

The most common error is a syntax error. A syntax error simply means not following the guidelines of the programming language such as form, punctuation, and so on. When the
PL/SQL code is compiled, it will generally point you in the area that needs correcting. However, this indication is not always perfect because the error could be from one or more lines above, which might be missing something as simple as a comma or a semicolon. The code in Listing 14.1 demonstrates syntax errors. The best way to debug syntax errors is through running the code and making the corrections until the code has compiled successfully. Simply look for the asterisk (*) where the compiler thinks the error occurred. In this section, you will attempt to debug this code, which is full of syntax errors.

INPUT
Listing 14.1. Practice correcting syntax errors.

 1: DECALRE
 2:     v_MyChar VARCHAR2 := `test';
 3:     v_NUMBER NUMBER;
 4:     Date DATE = SYSDATE;
 5:     v_counter INTEGER;
 6: NEGIN
 7:     DBMS_OUTPUT.PUT_LINE(`This is a Test')
 8:     DBMS_OUT.PUTPUT_LINE("Of Syntax Error Debugging");
 9:     For v_COUNTER IN 1..5 LOOP
10:         DBMS_OUTPUT.PUTLINE(`You are in loop: || v_counter);
11:     END-LOOP;
12: END
/

OUTPUT
When you execute the code in Listing 14.1, your output will look like the following:

DECALRE
*
ERROR at line 1:
ORA-01756: quoted string not properly terminated

ANALYSIS
The Oracle error is looking for a proper string, which is a string encapsulated by single quotes. Even though it singled out the first line, is the first line a proper string, or is there a line without a beginning or end single quote? Remember, errors do not have to be located in the area the compiler suggests. In line 10, there is a string missing a single quote. Change the line to read

INPUT

DBMS_OUTPUT.PUTLINE(`You are in loop: `|| v_counter);

After you have corrected the changes, reexecute the code. Your next error is

OUTPUT

DECALRE
*
ERROR at line 1:
ORA-00900: invalid SQL statement

ANALYSIS
Oracle, after checking for properly terminated strings, now starts checking top down. The first error is a misspelling of the keyword DECLARE in line 1. Make the change and reexecute the code. You will now receive many errors:

OUTPUT

Date DATE = SYSDATE;
              *
ERROR at line 4:
ORA-06550: line 4, column 15:
PLS-00103: Encountered the symbol "=" when expecting one of the
Âfollowing:
:= . ( @ % ; not null range renames default
The symbol ":= was inserted before "=" to continue.
ORA-06550: line 8, column 5:
PLS-00103: Encountered the symbol "DBMS_OUT" when expecting
     one of the following:
:= ; not null default
The symbol ":=" was substituted for "DBMS_OUT" to continue.
ORA-06550: line 9, column 5:
PLS-00103: Encountered the symbol "FOR" when expecting
     one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> cursor
form
The symbol "begin" was subst
ORA-06550: line 11, column 8:
PLS-00103: Encountered the symbol "-" when expecting one of the
Âfollowing:
loop

ANALYSIS
Just changing one line now affects several other lines. The first error states that you are trying to compare one variable to another in a declaration area. In addition, you can correct two problems. You have defined a variable date of type DATE. You should never use keywords in variable names. Line 4 should read

v_Date DATE := SYSDATE;

Upon reexecution, your next set of errors is

OUTPUT

DBMS_OUT.PUTPUT_LINE("Of Syntax Error Debugging");
    *
ERROR at line 8:
ORA-06550: line 8, column 5:
PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting
     one of the following:
:= ; not null default
The symbol ":=" was substituted for "DBMS_OUTPUT" to continue.
ORA-06550: line 9, column 5:
PLS-00103: Encountered the symbol "FOR" when expecting
     one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> cursor
form
The symbol "begin" was subst
ORA-06550: line 11, column 8:
PLS-00103: Encountered the symbol "-" when expecting one of the
Âfollowing:
loop

ANALYSIS
Now you can narrow down the problems. In line 8, the program tries to assign a value to DBMS_OUTPUT.PUT_LINE because it still thinks it is in the declaration section. You need to make two corrections. Change NEGIN to BEGIN and change the double quotes in line 8 to single quotes, so the line now reads

DBMS_OUTPUT.PUT_LINE(`Of Syntax Error Debugging');

Now, when you reexecute the code, the list of errors is

OUTPUT

DBMS_OUTPUT.PUT_LINE(`Of Syntax Error Debugging');
    *
ERROR at line 8:
ORA-06550: line 8, column 5:
PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting
     one of the following:
:= . ( % ;
The symbol ":=" was substituted for "DBMS_OUTPUT" to continue.
ORA-06550: line 11, column 8:
PLS-00103: Encountered the symbol "-" when expecting one of the
Âfollowing:
loop

ANALYSIS
You still have an error on line 8. This is because the line above it is missing the semicolon. You can also fix the error on line 11. The END-LOOP statement should be END LOOP. Fix both errors (add the semicolon at the end of line 7, and remove the dash from END-LOOP), and reexecute the code.

DECLARE
*
ERROR at line 1:
ORA-06550: line 13, column 0:
PLS-00103: Encountered the symbol "END" when expecting
     one of the following:
; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "END" to continue.