077-081

Previous Table of Contents Next


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.


Previous Table of Contents Next


High Performance Oracle Database Automation
High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL
ISBN: 1576101525
EAN: 2147483647
Year: 2005
Pages: 92

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net