Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Testing a Function Oracle Tips by Burleson

For an application developer, one of the most important uses of scripting is the ability to create and later re-create conditions for testing pieces of code. A good unit test defines a set of test data, documents the conditions established by the test data, predicts the output of the code being tested, and allows the developer to compare (or automatically compares) the predicted results with the actual results. Listing 1.7 displays a simple script that allows testing of a function.

Listing 1.7 A simple script that allows testing of a function.

set serveroutput on
set timing on
set verify off
set pause off
set linesize 80
set pagesize 0
set feedback off

spool test_out.txt
--
-- Set up some state abbreviations.
--
INSERT
INTO   STATE_CODES
       (state_name,
        state_code)
VALUES ('MISSISSIPPI',
        'MS');

--
-- Calling the function Get_State_Name() with a parameter of 'MS'
-- will return the value 'Mississippi'.
--
DECLARE
   vFullStateName   varchar2 (20);

BEGIN
   vFullStateName := Get_State_Name (vStateAbbr => 'MS');
   DBMS_Output.Put_Line ('The function returned ' ||
                         vFullStateName);

EXCEPTION
   WHEN OTHERS THEN
        DBMS_Output.Put_Line (SQLERRM);
END;

--
-- Calling the function Get_State_Name() with a parameter of 'AK'
-- will not return 'Mississippi'.
--
DECLARE
   vFullStateName   varchar2 (20);

BEGIN
   vFullStateName := Get_State_Name (vStateAbbr => 'AK');
   DBMS_Output.Put_Line ('The function returned ' ||
                         vFullStateName);
EXCEPTION
   WHEN OTHERS THEN
        DBMS_Output.Put_Line (SQLERRM);
END;

spool off

This script can be re-executed at any point in the future when the function Get_State_Name() is changed. While this is a very simple example, this type of script can be used to automate testing of almost any piece of code. The ability to easily repeat a test of complex code easily offsets the time required to develop a test for the same code—a 200 line procedure that drives part of a major application is not something that should rely on ad hoc testing.

 

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