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

 

 

   
 

Creating Test Scripts
Oracle Tips by Burleson
 

Testing

Now, it’s time to write a unit test script for the function. By breaking the requirements down, we find that there are nine tests that must be performed.

  • Attempt to assign five classes to a faculty member instructor. This can be accomplished by creating four classes with the professor already assigned to teach them, then creating another class appropriate to the professor. The function should not return a value.
  • Attempt to assign three classes to a graduate student instructor. This test is similar to the previous test. Set up two classes with the graduate student as the assigned instructor, and then create a third class appropriate to the graduate student. The function should not return a value.
  • Attempt to assign four classes to a faculty member instructor. This is similar to the first test, but instead of creating four classes, create three classes that the instructor is already teaching. The function should return the ID number of the instructor.
  • Attempt to assign two classes to a graduate student instructor. This test is similar to the previous test: Set up one existing class that the graduate student is teaching and set up the data so that the graduate student should be assigned to teach one more course. The function should return the ID number of the instructor.
  • Attempt to assign a class with a course_level greater than the instructor’s approved_class_level by creating a class appropriate to the instructor with the exception of the class level and running the function. The function should return without value.
  • Attempt to assign a class with a course_level less than the instructor’s approved_class_level by creating a class appropriate to the instructor. The function should return the instructor’s ID number.
  • Attempt to assign a course with a course_level less than the instructor’s min_class_level by creating a class appropriate to the instructor and setting the instructor’s min_class_level to the level above the course. The function should return without a value.
  • Attempt to assign two classes at different times to one instructor by creating one class assigned to the instructor and another class at a different class period. The function should return the instructor’s ID number.
  • Attempt to assign two classes at the same time to one instructor by creating one class assigned to the instructor and another class in the same class period. The function should return without a value.

We’ll assume that testing the function will be done in a schema other than the schema that owns the final product. Making this assumption allows us to set up each of our test conditions by completely emptying the tables of data and then populating the tables with the data to support each test (which makes predicting the results of the function much easier).

The logic for each test is fairly close to the following:

  • Clean out the tables.
  • Add needed test data to tables.
  • Predict the results.
  • Call the function.
  • Check the results by querying the tables.

Now that these test steps have been established, we can set up a test of the function using a script to handle each condition that needs to be tested. The first of these conditions is tested using a script like the one in Listing 5.24.

Listing 5.24 A test script for the Assign_Instructor() function.

DECLARE
 
BEGIN
   --
   -- Clean out the local tables before creating baseline data.
   -- The CLASS_TIMES table is a lookup table, so no work needs
   -- to be done there.
   --
   TRUNCATE TABLE INSTRUCTORS;
   TRUNCATE TABLE SCHEDULED_CLASSES;
 
   --
   -- Create a faculty member psychology instructor qualified
   -- to teach courses at the 800 level, with no minimum level.
   --
   INSERT
   INTO   INSTRUCTORS
          (instructor_number,
           last_name,
           first_name,
           faculty_member_flag,
           approved_class_level,
           min_class_level,
           approved_field,
           maximum_classes)
   VALUES (1000,
           'Williams',
           'Bill',
           'Y',
           800,
           NULL,
           'PSY',
           'Y');
 
   --
   -- Create four courses with our instructor.
   --
   INSERT
   INTO   SCHEDULED_CLASSES
          (course_number,
           course_field,
           instructor_number,
           semester_id,
           class_time,
           field_level)
   VALUES (2000,
           'PSY',
           1000,
           197,
           'A',
           100);
 
   INSERT
   INTO   SCHEDULED_CLASSES
          (course_number,
           course_field,
           instructor_number,
           semester_id,
           class_time,
           field_level)
   VALUES (2001,
           'PSY',
           1000,
           197,
           'B',
           232);
 
   INSERT
   INTO   SCHEDULED_CLASSES
          (course_number,
           course_field,
           instructor_number,
           semester_id,
           class_time,
           field_level)
   VALUES (2010,
           'PSY',
           1000,
           197,
           'C',
           321);
 
   INSERT
   INTO   SCHEDULED_CLASSES
          (course_number,
           course_field,
           instructor_number,
           semester_id,
           class_time,
           field_level)
   VALUES (2302,
           'PSY',
           1000,
           197,
           'G',
           810);
 
   --
   -- Create a class that this professor could teach.
   --
   INSERT
   INTO   SCHEDULED_CLASSES
          (course_number,
           course_field,
           instructor_number,
           semester_id,
           class_time,
           field_level)
   VALUES (2100,
           'PSY',
           1000,
           197,
           'E',
           201);
 
   --
   -- Now call the Assign_Instructor function. Since there is only
   -- one professor who can teach psychology courses and that
   -- professor is already teaching four courses, the function
   -- will return without a value (an exception will be raised when
   -- calling the function).
 
   --
   nTeacherID := Assign_Instructor (nClassNumber => 2100);
   --
   -- If execution reaches this point, the function did return
   -- a value. This should not have happened.
   --
   DBMS_Output.Put_Line ('ERROR: Function executed normally!');
   DBMS_Output.Put_line ('Instructor ID: ' || nTeacherID);
 
EXCEPTION
   WHEN OTHERS THEN
        DBMS_Output.Put_Line (SQLERRM);
END;

As you can see, creating the test is quite simple. The tests for the other conditions are quite similar in content.


TIP:  Creating Test Scripts

Although creating tests like this takes some time, once the test is set up it can be repeated whenever the function is modified in the future. (Who says the university won’t someday change the way it assigns professors to classes?) Creating test scripts that handle the condition allows the modified function to be tested against the same criteria and baseline data as the original function. In programming (as in science), this is a highly desirable goal.


Summary

Chapter 5 has discussed the fundamentals of creating stored functions within the Oracle database. At this point you should have an understanding of the PL/SQL needed to create a function and should also have some insight on how to design and test your functions. Chapter 6 will discuss some specifics of using functions within packages.


          

            
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