Reporting

Before reporting tools were available for Oracle, reports were written using SQL*Plus. A simple report could take a full day to write; complex reports could take a week or more. SQL*Plus includes some very robust controls that support the development of reports, even though most reports are now generated using Oracle Reports or other reporting tools. The script shown in Listing 3.5 generates a report about the code stored in an Oracle data dictionary.

Listing 3.5 An SQL report on code stored in the data dictionary.

clear computes
clear breaks

compute avg of length on type
break on type report skip page

spool code_rep.txt

SELECT type, name, max (line) “length”
FROM   ALL_SOURCE A
WHERE  A.owner = upper ('&&1')
AND    A.line = (SELECT max (B.line)
                FROM   ALL_SOURCE B
                WHERE  A.owner = B.owner
                AND    A.name  = B.name
                AND    A.type  = B.type)
GROUP BY type, name;

spool off

EXIT

This report computes the average number of lines for each type of stored PL/SQL object. By calculating the average number of lines for each type of object, the report (theoretically) indicates the level of modularity in the code (the lower the average number of lines, the more modular the code). The output of the script is shown in Figure 3.1.

Figure 3.1  Sample output for Listing 3.5.

Testing

The most monotonous part of application development is testing, but testing is vital to the success of a project. The use of a testing script provides the following three advantages over typical ad hoc testing:

•  Definition of assumptions—The script defines its assumptions about the code being tested. The act of writing the test also clarifies the developer’s assumptions about the code, which leads to a better piece of code.

•  Repeatability—A test can be repeated multiple times during testing. The script can also be repeated at any point in the future when code is modified. When the code is modified, the script repeats the same steps and creates the same conditions without requiring any additional work on the part of the developer.

•  Reliability—The test is absolutely reliable. While a script might contain some errors, once a script is debugged, the results are dependent only on the code that the script tests.

A well-written test script performs these four tasks:

•  Setup—The script creates data that supports a test condition. Often, a script is only one of many testing scripts for a piece of code, with each script validating one of many conditions that must be tested.

•  Prediction—The script predicts the outputs of the code (or the conditions that will exist after the code is run), based on the data created during the setup portion of the script.

•  Execution—The script executes the code that it is designed to test.

•  Checks—The script reports and/or checks the outputs of the procedure.

Listing 3.6 illustrates a test for the procedure Calculate_GPA().

Listing 3.6 A test for the Calculate_GPA() procedure.

-- *****************************************************************
-- This script is a unit test for the procedure Calculate_GPA. The
-- following conditions are established by the script:
--
--    1) A student is created with a 4.0 GPA.
--
-- The script makes the following assumptions about the data in the
-- system:
--
--    1) The DEGREE_PLANS and STATES tables are fully populated.
--
-- This script is run from the SQL*Plus prompt by executing the
-- command:
--
--    @calculate_gpa.ut
--
-- The script must be run as a user with SELECT and INSERT
-- privileges on the STUDENTS and ENROLLED_COURSES table.
--
-- *****************************************************************

set termout off
set verify off
set pause off
set feedback off
set lines 80
set pages 0

set serveroutput on
spool calculate_gpa.utr

SELECT 'Creating data in STUDENTS table.'
FROM   DUAL;

INSERT
INTO   STUDENTS
       (ssn,
        first_name,
        last_name,
        middle_name,
        street_address,
        apartment_number,
        city,
        state_code,
        zip_code,
        home_phone,
        degree_plan,
        overall_gpa,
        most_recent_gpa,
        financing_num)
VALUES ('000000000',
        'John',
        'Schmoe',
        'Jacob',
        '613 North Peach Lane',
        NULL,
        'Raymond',
        'MS',
        '39154',
        '6018570900',
        'BA ANTHROPOLOGY',
        NULL,
        NULL,
        FINANCING_SEQ.NEXTVAL);

--
-- Create the grades for the student.
--
SELECT 'Creating course data.'
FROM   DUAL;

INSERT
INTO   ENROLLED_COURSES
       (course_id,
        ssn,
        course_grade)
VALUES (1934,
        000000000,
        'A');

INSERT
INTO   ENROLLED_COURSES
       (course_id,
        ssn,
        course_grade)
VALUES (2103,
        000000000,
        'A');

--
-- Call the Calculate_GPA procedure.
--
SELECT 'Calling procedure.'
FROM   DUAL;

BEGIN
  Calculate_GPA (nSSN => 000000000);
END;

--
-- Retrieve the newly calculate GPA from the STUDENTS table.
--
SELECT 'Retrieving GPA.'
FROM   DUAL;

DECLARE
   nGPA   number := 0;

BEGIN
   SELECT overall_gpa
   INTO   nGPA
   FROM   STUDENTS
   WHERE  ssn = 000000000;
   DBMS_Output.Put_Line ('GPA is: ' || to_char (nGPA));

   IF (nGPA != 4.0) THEN
     DBMS_Output.Put_Line ('** ERROR: GPA does not match ' ||
                           'expected results! **');
   END IF;
END;

spool off

EXIT

This script sets up a test condition by inserting rows into the STUDENTS table and ENROLLED_COURSES table. The script then calls the Calculate_GPA() procedure to calculate a new GPA for the student. Finally, the script queries the new GPA from the STUDENTS table and writes out the GPA using the DBMS_Output package; if the GPA doesn’t match the expected result, the script also writes out an error message.

Obviously, setting up a script to perform a test can take some time, but the advantages of having a reliable and repeatable test are hard to overlook. If a piece of code is complex, using a script of this type is essential to validating the code.