INPUT
Listing 14.13. Keywords not capitalized.

 1: declare
 2:     v_MyNumber number := 0; -- five spaces to set apart variable names
 3: begin
 4:     loop -- five spaces to separate where procedure begins and ends
 5:         if v_MyNumber = 7 then -- five spaces for new block of code
 6:              exit;
 7:     v_MyNumber := v_MyNumber + 2; -- Part of Loop Block
 8:     end loop;  -- Aligned under matching loop statement
 9: end;

ANALYSIS
In the code in Listing 14.13, it is difficult to pick out the datatypes of the variables and what type of statements exist in this block of PL/SQL code.

Using Mixed Case for Variable Names
To identify code, output, keywords, or variable names, you can easily distinguish variable names by using mixed case. By using MyVariable, you can pick the variable name out faster than you could pick out myvariable or MYVARIABLE. Refer to Listing 14.14 for an example of poor formatting of variables.

Preceding Variable Names with v_ or p_
By preceding variables with a first letter and an underscore, you can quickly identify variables, and what type of variables you are coding. You would use v_ for regular variable names, p_ for parameters, and so on. Listing 14.14 shows both improper use of case and not preceding variable names with a letter followed by an underscore.

INPUT
Listing 14.14. Poor formatting of variables.

 1: DECLARE
 2:     mynumber NUMBER := 0; -- five spaces to set apart variable names
 3: BEGIN
 4:     LOOP -- five spaces to separate where procedure begins and ends
 5:         IF mynumber = 7 THEN -- Five spaces for new block of code
 6:              EXIT;
 7:     mynumber := mynumber + 2; -- Part of Loop Block
 8:     END LOOP;  -- Aligned under matching loop statement
 9: END;

Keeping One Statement per Line
Because the semicolon (;) is terminating, you could easily have multiple statements on one line. For instance, you could code

NULL;END LOOP;END;

The function, procedure, or other item would function properly. But you'll run into trouble if you try to troubleshoot this or comment out a statement that might be causing the error.

Summary

Today's lesson discussed the methodology of debugging an application. When debugging, you must deal with two kinds of errors—logic errors and syntax errors. Syntax errors are a result of improper formatting, missing punctuation, misspelled keywords, and so on. Logic errors do not cause your program to stop execution (usually); they allow the code to compile, but provide for the wrong type of processing. Other common problem areas that cause logic errors are improper order of operations when performing calculations and infinite loops. Oracle does not feature any built-in debugging package for trying to locate logic errors. You could purchase these tools, use DBMS_OUTPUT, use pipes, or create your own debugging package. Finally, proper commenting and formatting of code enables you to review and understand code much faster than no comments or no formatting.

Q&A

Q What debugging packages does Oracle provide with its standard relational database package?
A Currently, none are provided, although you can purchase tools from Oracle or other third-party support.
Q What can I use to debug applications?
A DBMS_OUTPUT and pipes are two methods of debugging. You can also create your own debugging package to track variables.
Q If all operations are on the same level, how does Oracle know which calculations to process first?
A If all calculations are at the same level, the order of evaluation is from left to right.
Q What simple punctuation can easily override the natural order of operations?
A Using parentheses (). Be careful when nesting parentheses.
Q Are comments needed in your application if you have sufficient separate documentation?
A Yes, absolutely. Documentation tends to get misplaced or never gets updated when a coding change is made. Document not only what each procedure, function, trigger, and so on is doing, but also document changes and updates as they occur.
Q Must we really document solutions to problems?
A Documenting solutions to problems helps you troubleshoot the same problem if it occurs in the future in the same application, a different application, and so on.
Q Why does proper formatting help in debugging code?
A Proper formatting allows you to view code quickly and assess what the code is doing. If you do not line up END IF statements in a multiple IF...THEN clause, it will be difficult to see when the first statement ends, the second ends, and so forth.

Workshop

The following workshop will give you the opportunity to test your knowledge of debugging code and to practice correcting errors. The answers to the quiz and exercises can be found in Appendix A, "Answers."

Quiz

1. True or False: Logic errors are easier to debug than syntax errors.
2. Missing a semicolon is what type of error?
3. Provide the answer to the calculation 6 + 4/2 = ?
4. True or False: Oracle comes with a built-in debugging package.
5. True or False: Proper planning reduces errors in the future.
6. True or False: Commenting code is a waste of time.
7. True or False: Formatting code is not necessary.

Exercises

1. Use the DEBUG package to troubleshoot the code in Listing 14.2.
2. Use the DBMS_OUTPUT package to troubleshoot the code in Listing 14.2.