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

 

 

   
 

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.

  
 

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