Previous | Table of Contents | Next |
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.
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:
A well-written test script performs these four tasks :
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.
Previous | Table of Contents | Next |