Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Oracle Testing
Oracle Tips by Burleson
 

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.

 
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter