| |
 |
|
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. |
