17-1. Testing Stored PL/SQL Code Without Unit Tests
Problem
You want to ensure that a block of PL/SQL code is working properly, but don't want to take the time to write a unit test.
Solution
Wrap the code in DBMS_OUTPUT statements that display or print the results of intermediate and final computations and the results of complex conditional steps and branches. This will enable you to see the path that the code is taking when the function is called with specified parameters. The following example demonstrates this tactic for placing comments into strategic locations within a PL/SQL code block in order to help determine if code is functioning as expected. For example, suppose you wish to quickly test the function we introduced in the example for Recipe 4-1. Here's how you'd modify it to quickly test the correctness of its results.
CREATE OR REPLACE
FUNCTION CALC_QUARTER_HOUR(HOURS IN NUMBER) RETURN NUMBER AS
CALCULATED_HOURS NUMBER := 0;
BEGIN
-- if HOURS is greater than one, then calculate the decimal portion
-- based upon quarterly hours
IF HOURS > 1 THEN
-- calculate the modulus of the HOURS variable and compare it to
DBMS_OUTPUT.Put_LINE('The value passed in was greater than one hour...'),
-- fractional values
IF MOD(HOURS, 1) <=.125 THEN
DBMS_OUTPUT.Put_LINE('The decimal portion < .125'),
CALCULATED_HOURS := substr(to_char(HOURS),0,1);
ELSIF MOD(HOURS, 1) > .125 AND MOD(HOURS,1) <= .375 THEN
DBMS_OUTPUT.Put_LINE('The decimal portion <= .375'),
CALCULATED_HOURS := substr(to_char(HOURS),0,1) + MOD(.25,1);
ELSIF MOD(HOURS, 1) > .375 AND MOD(HOURS,1) <= .625 THEN
DBMS_OUTPUT.Put_LINE('The decimal portion <= .625'),
CALCULATED_HOURS := substr(to_char(HOURS),0,1) + MOD(.50,1);
ELSIF MOD(HOURS, 1) > .63 AND MOD(HOURS,1) <= .825 THEN
DBMS_OUTPUT.Put_LINE('The decimal portion <= .825'),
CALCULATED_HOURS := SUBSTR(TO_CHAR(HOURS),0,1) + MOD(.75,1);
ELSIF MOD(HOURS, 1) > .825 AND MOD(HOURS,1) <= .999 THEN
DBMS_OUTPUT.Put_LINE('The decimal portion <= .999'),
CALCULATED_HOURS := (substr(to_char(HOURS),0,1) + 1) + MOD(.00,1);
ELSE
DBMS_OUTPUT.Put_LINE('The hours passed in will use standard rounding'),
CALCULATED_HOURS := ROUND(HOURS,1);
END IF;
ELSE
-- if HOURS is less than one, then calculate the entire value
DBMS_OUTPUT.Put_LINE('Less than 1 hour was passed in...'),
-- based upon quarterly hours
IF HOURS > 0 AND HOURS <=.375 THEN
DBMS_OUTPUT.Put_LINE('The decimal portion < .125'),
CALCULATED_HOURS := .25;
ELSIF HOURS > .375 AND HOURS <= .625 THEN
DBMS_OUTPUT.Put_LINE('The decimal portion <= .625'),
CALCULATED_HOURS := .5;
ELSIF HOURS > .625 AND HOURS <= .825 THEN
DBMS_OUTPUT.Put_LINE('The decimal portion <= .825'),
CALCULATED_HOURS := .75;
ELSIF HOURS > .825 AND HOURS <= .999 THEN
DBMS_OUTPUT.Put_LINE('The decimal portion <= .999'),
CALCULATED_HOURS := 1;
ELSE
DBMS_OUTPUT.Put_LINE('The hours passed in will use standard rounding'),
CALCULATED_HOURS := ROUND(HOURS,1);
END IF;
END IF;
RETURN CALCULATED_HOURS;
END CALC_QUARTER_HOUR;
When the CALC_QUARTER_HOUR function is executed with a value of 7.34, the comments will be displayed as seen in the next snippet from a SQL*Plus session.
SQL> set serveroutput on
SQL> select calc_quarter_hour(7.34) from dual;
CALC_QUARTER_HOUR(7.34)
-----------------------
7.25
The value passed in was greater than one hour...
The decimal portion <= .375
How It Works
The use of DBMS_OUTPUT statements within PL/SQL code for displaying data or information pertaining to the functionality of the code has been a great tactic for testing code in any language. As a matter of fact, it is probably one of the most widely used techniques for debugging code. The ability to see values as they are calculated or to determine how a condition is being handled can be very useful for determining whether your code is executing as it should.
In order to use DBMS_OUTPUT statements for testing your code, you must place them in strategic locations. In the example for this recipe, comments have been placed within each of the IF-ELSE blocks to display a bit of text that will tell the developer how the values are being processed within the function. This can be very useful when testing the code because a series of numbers can be passed into the function in order to determine whether the correct result is being returned. If not, then you will be able to see exactly where the code is being evaluated incorrectly.
Although using DBMS_OUTPUT statements in code can be very useful for determining where code is functioning properly, it can cause clutter, and can also create its own issues. For example, if you forget to place a quote after one of the DBMS_OUTPUT statements that you place into your code, then the code will not compile correctly, causing you to hunt for the cause of yet another issue. Also, it is a good idea to remove the output statements before code is released into production. This can take some time, which could be better spent on development. As a means for testing small units of code, using DBMS_OUTPUT statements works quite well. However, if you wish to develop entire test suites and automated unit testing then you should go on to read Recipe 17-2 regarding utPLSQL.