17-9. Redirecting utPLSQL Test Results to a File

Problem

You are interested in writing the results of a unit test to a file.

Solution

Change the setting of the setreporter option of utPLSQL so that output is redirected to a file instead of DBMS_OUTPUT. Once the configuration has been altered, execute the unit tests for which you would like to have the output captured to the file. After you've run your tests, close the file and change the configuration back to its default. In the following lines of code, all of the steps that are necessary for redirecting test results to a file are exhibited. For example, suppose that the database has a directory that has already been enabled for use with the database named FILE_SYSTEM.

When the code block in this example is executed, a file will be created within the directory represented by FILE_SYSTEM. The unit test for CALC_QUARTER_HOUR will then be executed and the results will be redirected to the newly created file. Lastly, the file will be closed and the output will be redirected back to DBMS_OUTPUT.

How It Works

One of the configurable options of utPLSQL allows for the output of your unit tests to be redirected. The choices for displaying unit test results include Output, File, and HTML. The standard Output option is Output, which causes output to be displayed within the SQL*Plus environment using DBMS_OUTPUT. The File option allows for a file to be created and unit test results to be written to that file. Lastly, the HTML option allows for unit test results to be formatted into file in the format of an HTML table. In the solution to this recipe, the use of the File output reporter is demonstrated.

Prior to redirecting unit test output to a file, you must create a database directory using the CREATE DIRECTORY statement with a privileged account. For more information about creating directories, please see the Oracle documentation that can be found at: http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_5007.htm#SQLRF01207. Once you have created a database directory, you can use it to write the results of unit tests by setting the file directory using the utConfig.setfiledir() procedure. This procedure accepts the name of the database directory as a parameter. In the solution to this recipe, the directory is named FILE_SYSTEM. To redirect the unit test output from utPLSQL, you must use the utConfig.setreporter() procedure. This procedure accepts the name of the reporter that you would like to use for displaying output. As you can see from the solution to this recipe, the File reporter is chosen to redirect the output to a file on the file system. It is also possible to create a custom reporter configuration that you can pass to the utConfig.setreporter() procedure. For more information about creating customized reporters, please see the utPLSQL documentation that can be found at: http://utplsql.sourceforge.net/Doc/reporter.html.

After the output has been redirected using utConfig.setreporter(), you can run as many tests as you wish and all of the output will be directed to a file instead of to the SQL*Plus command prompt. In the solution to this recipe, the CALC_QUARTER_HOUR function is tested. Once you have finished running your tests, you must close the output file in order to make it available for you to use. If you fail to close the file, you will be unable to open it or use it because the database will maintain a lock on the file. To close the file, use issue utfilereporter.close(). Lastly, I recommend redirecting unit test output to the default Ouput option, which will cause it to be sent to DBMS_OUTPUT. By doing so, the next person who runs a unit test will receive the functionality that he or she expects by default, as the output will be directed to the screen. It is a good idea to set the default output at the beginning of all test suites just to ensure that you know where the output will be directed. However, if you are the only person running unit tests, or if you prefer to maintain the File reporter as your default, then omit the final call to utConfig.setreporter() that is shown in this solution.

Many times it can be useful to have unit test results redirected to an output file rather than displayed within the SQL*Plus environment. For instance, if you are running unit tests during off hours and would like to see the output, then it would be helpful to have it recorded to a file that can be viewed at a later time. Similarly, if you are running several unit tests, it may be easier to read through a file rather than scrolling through SQL*Plus output. Whatever the requirement may be, utPLSQL makes it easy to redirect unit test output to a file or another device by creating a custom reporter.