|Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)
Part Number A95298-01
Developing Programs, 12 of 12
Even when your program compiles cleanly, you must also test the program by running it. Running a program helps you detect errors in commands with ampersand substitution, errors in logic, and errors in any nested programs.
To test a program by running it, use a full set of test data that is typical of the data that the program will process. To confirm that you test all the features of the program, including error-handling mechanisms, run the program several times, using different data and responses. Use test data that:
Each time you run the program, confirm that the program executes its commands in the correct sequence and that the output is correct. As an aid in analyzing the execution of your program, you can include
SHOW commands in the program to produce diagnostic or status messages. Then delete the
SHOW commands after your tests are complete.
When you detect or suspect an error in your program or a nested program, you can track down the error by using the debugging techniques that are described in the the rest of this section.
When you set the
BADLINE option to
YES, additional information will be produced, along with any error message when a bad line of code is encountered. When the error occurs, the error message, the name of the program, and the program line that triggered the error are sent to the current outfile.
You can edit the specified program to correct the error and then run the original program.
In a simple program called
test, the variable
myint1 is divided by zero.
DEFINE test PROGRAM PROGRAM VARIABLE myint1 INTEGER VARIABLE myint2 INTEGER myint1 = 0 myint2 = 250/myint1 END
If you run the program when the
DIVIDEBYZERO option is set to
NO, then an error occurs because division by zero is not allowed. When
BADLINE is set to
YES, the following messages are recorded in the current outfile.
ERROR: (MXXEQ01) A division by zero was attempted. Set DIVIDEBYZERO to YES if you want NA to be returned as the result of division by zero. In DEMO!TEST PROGRAM: myint2 = 250/myint1
If your program contains an error in logic, then the program might execute without producing an error message, but it will execute the wrong set of commands or produce incorrect results. For example, suppose you write a Boolean expression incorrectly in an
IF command (for example, you use
NE instead of
EQ). The program will execute the commands you specified, but it will do so under the wrong conditions.
To find an error in program logic, you often need to see the order in which the commands are being executed. One way you can do this is to create a debugging file and then examine the file to diagnose any problems in your programs.
To create a debugging file, you use the
DBGOUTFILE command. The syntax of the
DBGOUTFILE command is shown below.
The command has the following arguments:
EOFkeyword specifies that the current debugging file should be closed, and that debugging output should no longer be sent to a file.
APPENDkeyword specifies that the output should be added to the end of an existing disk file. If you omit this argument and a file exists with the specified name, then the new output replaces the current contents of the file.
NOCACHEkeyword causes the OLAP DML to write to the debugging file each time it executes a line of code. Without this keyword, file I/O activity is reduced by saving text and writing it periodically to the file.
For more information about the
DBGOUTFILE command, see the entry for the command in Oracle9i OLAP DML Reference help.
DBGOUTFILE command merely creates a file for debugging. To specify that you want each program line to be sent, as it executes, to the debugging file, set the
PRGTRACE option to
If you want the debugging file to interweave the program lines with both the program's input and error messages, then set the
ECHOPROMPT option to
For the syntax of the
PRGTRACE options, see the entry for each option in Oracle9i OLAP DML Reference help.
The following commands create a useful debugging file called
debug.txt in the current directory alias.
After executing these commands, you can run your program as usual. To close the debugging file, execute this command.
In the following sample program, the first
LIMIT command has a syntax error.
DEFINE ERROR_TRAP PROGRAM PROGRAM trap on traplabel limit month to first badarg limit product to first 3 limit district to first 3 report sales traplabel: signal errorname errortext END
ECHOPROMPT both set to
YES and with
DBGOUTFILE set to send debugging output to a file called
debug.txt, the following text is sent to the
debug.txt file when you execute the
(PRG= ERROR_TRAP) (PRG= ERROR_TRAP) trap on traplabel (PRG= ERROR_TRAP) (PRG: ERROR_TRAP) limit month to first badarg ERROR: BADARG does not exist in any attached database. (PRG= ERROR_TRAP) traplabel: (PRG= ERROR_TRAP) signal errorname errortext ERROR: BADARG does not exist in any attached database.