Document the Solution
You should document the solution, on paper, in the program (if possible), and ideally in an Oracle database of troubleshooting solutions. This will help you if the problem reoccurs and you can't remember what you did to fix it. You are also on your way to building an expert system that might be of some value to other clients or end users. This is probably one of the most important processes you should complete after you have solved the problem. If you're too busy to document right after solving the problem, you may live to regret the decision if a similar error occurs, which uses more time trying to solve the problem again. Make the time!

Using Tools to Help in Debugging a
Program

After you have narrowed down the problem to a set of modules, procedures, and so on, you can break out some troubleshooting tools. Two such tools demonstrated in this chapter are the creation of a debugging package and the use of DBMS_OUTPUT, both of which I use extensively.

Writing a Debugging Package

You will create a debugging package called DEBUG that will allow you to

• Take the system date and time, comments, and the contents of a variable and write these to a file while the program executes. This format will be output as a comma-separated value file (CSV) to allow you to import it into a table for queries, or simply to view the file with a text editor.
• Reset the file (erase the file) to start a new debugging process.

Execute the first part of the DEBUG package in Listing 14.3, which defines the procedures available to the package.

INPUT
Listing 14.3. Defining the DEBUG package components.

 1: CREATE OR REPLACE PACKAGE DEBUG AS
 2:      PROCEDURE OUT(p_Comments IN VARCHAR2, p_Variable IN VARCHAR2);
 3: /* Procedure OUT is used to output a comment of your
 4:    choice, along with the contents of the variable.  The

                                                                      continues

Listing 14.3. continued

 5:    Procedure OUT statement defines the format of the function */
 6:      PROCEDURE Erase;
 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: END DEBUG; -- End Definition of package DEBUG

After you have executed the code, you should see on the screen

OUTPUT

Package Created

If you do not see this message, review your code for syntax errors. You can now enter and execute the final part of the DEBUG package from Listing 14.4.

INPUT
Listing 14.4. Creating the actual DEBUG package components.

 1: CREATE OR REPLACE PACKAGE BODY DEBUG AS
 2:      PROCEDURE OUT(p_Comments IN VARCHAR2,p_Variable IN VARCHAR2) IS
 3:           v_MyFHOUT UTL_FILE.FILE_TYPE; -- Declare File Handle
 4: BEGIN
 5: /* Use A to append all output being sent to the file */
 6:
 7:      v_MyFHOUT := UTL_FILE.FOPEN(`c:\','debug.txt','a');
 8:
 9: /* This outputs the System Time and Date formatted in MM-DD-YY HH:MM:SS
10:    followed by any comments you want to output and the contents of the
11:    variables.  Notice 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,'"'||
15:           TO_CHAR(SYSDATE,'mm-dd-yy HH:MM:SS AM')
16:            || `","Comment: ` || p_Comments ||
17:             `","Variable Contents: ` || 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
29:
30:
31:      PROCEDURE Erase IS
32:           v_MyFH UTL_FILE.FILE_TYPE; -- Create File Handle
33:      BEGIN

34: /* Open file to overwrite current file contents.  Doing this
35:    erases the contents of the original file completely */
36:
37:      v_MyFH := UTL_FILE.FOPEN(`c:\','debug.txt','w');
38:
39: -- Close the file handle which points to debug.txt
40:      UTL_FILE.FCLOSE(v_MyFH);
41:
42:      EXCEPTION
43: -- Create Exception to simply display error code and message
44:           WHEN OTHERS THEN
45:                DBMS_OUTPUT.PUT_LINE
46:                    (`ERROR ` || to_char(SQLCODE) || SQLERRM);
47:                NULL;
48:      END Erase; -- End Procedure Erase
49:
50: BEGIN
51:     Erase; -- Erase contents of the file
52:
53: END DEBUG; -- End procedure DEBUG

Again, after you have executed the code in Listing 14.4, your output should appear as

OUTPUT

Package body created.

You can now examine the components of the newly created DEBUG package.

The DEBUG.OUT Procedure
The DEBUG.OUT procedure enables you to print to a file called DEBUG.TXT the system date and time, a comment, such as where you are in the code and the name of the variable, and the contents of the variable. All the procedure does is accept two parameters: the comment specified by the end user, and the variable you are tracking. You then append to the file DEBUG.TXT—every time you hit the DEBUG.OUT statement—those three elements in a comma-separated value formula.

The Syntax for the DEBUG.OUT Procedure

PROCEDURE OUT(p_Comments IN VARCHAR2,p_Variable IN VARCHAR2)

The parameter p_Comments is a string of type VARCHAR2, which will hold any comments you want to use to identify the location at the time of output to the file and the variable name. The p_Variable parameter is used to pass the actual variable, whose contents will be written to the DEBUG.TXT file.

One flaw with this process is that it does not identify where the variable information is coming from. You would simply add the comments such as: Mortgage Module Before Calculation or Add Qualified Mortgage Applicant Module.