Testing
After 30 plus years of enterprise application development, I’ve found that the actual application development process for programmers consists of three high-level tasks:
- Coding
- Testing
- Documenting
The graph in Figure 8-1 is what I consider the ideal distribution of time for those three tasks. Yes, that’s 60% of the development time spent on testing. The first response I get from most developers and project managers is, “Wow, that’s an awful lot of time for testing, isn’t it?” Not really. All the time you spend testing is time you won’t have to spend supporting the application. And it costs a lot more to fix a defect in production than it does to take care of the problem earlier. It’s as simple as that.
Figure 8-1. An ideal distribution of development work
Professional PL/SQL programmers write test units for their PL/SQL program units as they write their program units. I’ve been having you write them all along, haven’t I? These are the only proper ways to reduce the amount of time spent testing:
- Develop with modularity (object orientation) because once a well-encapsulated software component is tested, it’s unlikely it will break or need testing if a different unrelated module is modified.
- Automate testing by writing automated test units for every program unit (within reason) and/or by using a testing application suite.
- Write rerunnable test units. You should be able to run test units over and over again until there are no errors, without having to do any extra work to run them again.
- Update test units with additional tests if a defect not caught by the current test unit rears its ugly head.
- Don’t lose your test units.
- Document your test units.
In contrast, nowadays it seems that everyone’s favorite way to reduce the amount of time spent testing is to not test at all, which means passing testing on to the end user (not a good idea), which in turn means passing it on to support personnel (even worse). Why is passing testing on to support personnel even worse? At least the end users have a good idea how things should work. Support personnel usually know even less. It becomes a case of the proverbial “blind leading the blind.”
So just whose responsibility is testing?
- You are primarily responsible for testing your own code. You need to write test plans and test units to execute those test plans.
- Your peers on your programming team are also responsible if they run into an error when using one of your components. Their responsibility is to inform you that there is a defect in your code, to ask you to update your test plans, update your test units, and then to test your code again.
- Your supervisor, whether a technical lead, project manager, or in another position, is responsible for verifying that you have executed your test plans and that there are no errors. Your supervisor is also responsible for verifying that your test plans are updated if someone encounters an error.
- An application’s support personnel are responsible for reporting errors to the development team so defects can be fixed, and then test plans and test units can be updated and rerun.
- An application’s end users are responsible for reporting errors to the support team so defects can be fixed, and then test plans and test units can be updated and rerun.
I used a numbered list for these responsibilities to emphasize that you are the one most responsible for testing.
So just what do I mean by “test plan” and “test unit”? A test plan is a document that outlines how an application is to be tested. It should at least answer the following questions:
- How will the persistence layer (database) be tested?
- How will the application layer (middle tier) be tested?
- How will the presentation layer (the various presentation layers: data entry, reporting, interfaces, and so on) be tested?
- What technique will be used to keep track of what needs to be tested?
- What technique will be used to record that something has been tested?
- Who will sign off that the testing has been completed successfully and that there are no errors?
- How many rounds of testing will there be, and who will perform each round of testing?
A test unit is a list of tests to be executed by a tester (a person who tests) or executed by a program, written or scripted, which executes the test steps in an automated fashion. This chapter is about writing test units:
- The commonly executed steps in a PL/SQL test unit for SQL
- The commonly executed steps in a PL/SQL test unit for PL/SQL
- A table to store the tests performed and their results
- A package to aid in writing PL/SQL test units
- A framework for keeping track of what needs to be tested in a persistence layer (database/objectbase)
Let’s start by looking at the common tasks involved in testing SQL.
If you have a PL/SQL method that uses SQL, then you must devise some method to test that the SQL in that program unit works correctly. From an abstract point of view, that boils down to executing some or all of these steps in the following order:
- DELETE
- INSERT
- SELECT
- UPDATE
- DELETE
If you’re going to test a program unit’s ability to INSERT a new entry into a table, you first need to decide on an acceptable set of values to use during the INSERT, and then you must clean up after yourself by removing what you’ve just added so the test unit can be run again. Since it’s entirely possible that an error may have occurred before your INSERT operation, you need to DELETE first. Otherwise, when you rerun your test on INSERT, you may get an error due to a row that was not properly cleaned up or removed during a prior test. That’s why I have DELETE as the number one high-level operation for testing any PL/SQL program unit that automates SQL testing.
Next, if you’re going to test a PL/SQL program unit’s ability to retrieve values from a table, you first need to INSERT those values so they are available. That means you must INSERT before you SELECT, which, in turn, means you need to DELETE, INSERT, SELECT, and then DELETE in your test unit.
Now if you’re going to test a program unit’s ability to UPDATE, it follows that you must first SELECT to verify that there is something to UPDATE, which requires you to INSERT before you SELECT and DELETE before you INSERT. Then you need to DELETE in order to clean up after yourself.
Last, if you’re going to test a program unit’s ability to DELETE, you need to perform a DELETE, then an INSERT, followed by a SELECT, (yes, you can skip UPDATE) and then a DELETE. I like to call this abstract pattern the SQL Circle of Life, as illustrated in Figure 8-2.
Figure 8-2. The SQL Circle of Life
At this moment, this discussion may seem quite silly. But you’ll see soon enough, when you start writing test units, you will need to keep the SQL Circle of Life in mind when writing rerunnable SQL test units in PL/SQL.
Let’s look at patterns found when testing PL/SQL methods next.
Just as there is an abstract pattern to testing SQL in PL/SQL, there is also a pattern you’ll need to follow to test PL/SQL program units themselves. The PL/SQL testing pattern is “blocking” your test code.
You need to wrap each of your PL/SQL tests in a PL/SQL block so you can programmatically maintain control in your test unit. If you can’t maintain control in your test unit, it may end abruptly without performing all your tests. Here’s an example:
...
begin
-- Perform your test by calling a method with test data here
-- Then check to ensure test success here
exception
when OTHERS then
-- Catch any PL/SQL exception and report the error here
end;
-- Go on to the next test
...
You’ll end up testing your PL/SQL methods by calling them with test values in your test unit, and then inspecting the results of the operation to verify it worked correctly. In order to maintain control, you’ll need to catch any raised exceptions, hence the when OTHERS then clause in the exception-handling section of the PL/SQL block surrounding your code. From the exception-handling section, you’ll report any errors that take place, therefore handling the exception, not raising the exception to the outer (enclosing) PL/SQL block. Then you’ll go on to perform your next method test.
How will you report and keep track of success or failure in your test units? You’ll do that with a testing tool. Let’s look at that next.
A Testing Tool
Humans are very good at creating their own tools. To that end, I’ve created a “testing tool” that helps me code my PL/SQL test units.
I have two high-level requirements for this testing tool. It needs to
- Provide a set of constants that I can use for column values when testing.
- Provide a set of methods that I can use to save tests and results for later analysis.
Do you recall that in the “SQL Test Patterns” section, I stated that you first need to decide on an acceptable set of values to use during the INSERT? How do you go about that?
Well here’s how I do it: I ask the database! Listing 8-1 is a SQL*Plus query I run to determine the varchar2 lengths that are in common use. For the most part, there should only be a few. While in the past, the lengths of a character field may have had some meaning, they no longer do. With the advent of globalization, the lengths of character fields only amount to specifying an upper limit on the amount of storage you’re willing to use for a given string value.
Listing 8-1. A Query to Determine Commonly Used Lengths for Varchar2 Columns, data_length_histogram.sql
01 rem data_length_histogram.sql
02 rem by Donald J. Bales on 2014-10-20
03 rem Create a histogram of VARCHAR2 data lengths in use
04
05 column column_name format a30;
06
07 set linesize 1000;
08 set newpage 1;
09 set pagesize 32767;
10 set trimspool on;
11
12 spool data_length_histogram.txt;
13
14 select column_name,
15 min(data_length) min,
16 avg(data_length) avg,
17 max(data_length) max,
18 count(1) occurs
19 from SYS.ALL_TAB_COLUMNS
20 where owner = USER
21 and data_type like 'VARCHAR%'
22 and table_name not in (
23 'AUTHORS',
24 'AUTHOR_PUBLICATIONS',
25 'A_THRU_Z',
26 'DEBUGS',
27 'PLAN_TABLE',
28 'PLSQL_PROFILER_RUNS',
29 'PLSQL_PROFILER_UNITS',
30 'PLSQL_PROFILER_DATA',
31 'TOP_100_FIRST_NAMES',
32 'TOP_100_LAST_NAMES' )
33 group by column_name
34 order by max(data_length),
35 column_name
36 /
37
38 spool off;
And here’s the output from the query in Listing 8-1:
COLUMN_NAME MIN AVG MAX OCCURS
------------------------------ ---------- ---------- ---------- ----------
LOGICAL_INDICATOR 1 1 1 1
PHYSICAL_INDICATOR 1 1 1 1
CAS_NUMBER 30 30 30 1
CODE 30 30 30 7
EXTERNAL_ID 30 30 30 1
FIRST_NAME 30 30 30 1
LAST_NAME 30 30 30 1
MIDDLE_NAME 30 30 30 1
DESCRIPTION 80 80 80 4
ID_CONTEXT 100 100 100 2
NAME 80 85.7142857 100 7
TITLE 100 100 100 1
NARRATIVE 2000 2000 2000 1
13 rows selected.
Analyzing this output, I have concluded that I will need the following constants for testing:
- A TRUE value for the indicator variables
- A FALSE value for the indicator variables
- A 30-character code value that I can specify any time I test inserting a new code or partial name value into a code or content table
- An 80-character description value that I can specify any time I test inserting a new description into a code or content table
- A 100-character value that I can specify any time I test inserting a new ID context or title into a content or intersection table
- A 2,000-character value that I can specify any time I test inserting a new comment or narrative into a content table
I’ll keep this list of requirements in mind when I code my testing tool’s package specification, so the constants will be accessible to all other program units, and therefore available when writing their test units.
WHY ARE WE SPECIFYING CHARACTER COLUMN LENGTHS ANYWAY?
After having internationalized a number of enterprise applications, I began to wonder why we have to specify a maximum column length for character columns at all. In the future, I don’t think we’ll need to do this; the database will handle the length automatically, and why not?
One programmer I know argued that was important and gave me what he thought was a valid example: “It’s important to constrain a number column for money. It should be NUMBER(11,2), because dollars have hundredths values (dimes and pennies).” But that’s only valid for dollars; other currencies have thousandths.
Consider that characters used in the English language use one byte of storage. Other languages use characters that require two to four bytes of storage. In addition, sentences, even words, in English are quite compact when compared with other languages. For example, an idea expressed in 30 characters in English may take four to five times as many words and/or characters in German.
So just what are we specifying when we constrain a varchar2’s length? I think it’s just our expectation that nothing entered into the column will ever need to be larger than what we’ve specified. So until Oracle gets with my line of thinking, we might as well decide on a standard set of character column lengths and stick to it. Here are the lengths I commonly use for character fields in applications:
- Code: 30 English only; 60 internationalized
- Code context: 500 English only; 1,000 internationalized
- Comments: 2,000 English only; 4,000 internationalized
- Description: 80 English only; 160
- ID context: 100 English only; 200 internationalized
- Name: 80 English only; 160 internationalized
- Name context: 1,000 English only; 2,000 internationalized
- Title: 80 English only; 160 internationalized
I would like to hear your thoughts: [email protected]. Now back to testing.
How about some behavior? What will I call the methods that aid me with writing test units? Here’s a preliminary set of requirements:
- clear(): To erase the results of a previous test run from table TESTS
- get_id(): To allocate a new primary key value for the table TESTS
- test(): To test the testing tool
- set_test(): To start a new test
- set_result(): To store the results of a test
However, before I write a testing tool package, I need to create a table to permanently store the results of my tests.
A TEST Table
Why store my test results in a table? Because then I can use SQL to query the table to analyze the results of my testing. No tool is more powerful at manipulating data than SQL, so a table is the ideal place to save the results of testing.
What do I need to store? Here’s my short list of data to store:
- The name of the package or user-defined TYPE (object name) that contains the method being tested
- The name of the method being tested
- An external identifier for the test being performed on a given method so I can identify a given test if it fails
- A short but meaningful description of the test
- The result of the test
In order to store this data, I’ve written the DDL script in Listing 8-2. It creates a test table named TESTS. I’ve also added an ID column for a primary key. This will be a unique session ID, so testing in one session does not collide with testing going on in another session. I’ve also included insert user and insert date columns, which are standard columns I add to all tables (to add those bread crumbs I talked about in Chapter 7).
Listing 8-2. A DDL Script to Create a Testing Results Table, rps.tests.tab
01 rem tests.tab
02 rem by Donald Bales on 2014-10-20
03 rem Create test results
04
05 create table TESTS (
06 id number not null,
07 object_name varchar2(30),
08 method_name varchar2(30),
09 test_number number,
10 description varchar2(80),
11 result varchar2(256),
12 unique_session_id varchar2(24) not null,
13 insert_user varchar2(30) default USER not null,
14 insert_date date default SYSDATE not null );
15
16 alter table TESTS add
17 constraint TESTS_PK
18 primary key (
19 id )
20 using index
21 tablespace USERS pctfree 0
22 storage (initial 1M next 1M pctincrease 0);
23
24 create sequence TESTS_ID
25 start with 1 order;
26
27 execute SYS.DBMS_STATS.gather_table_stats(USER, 'TESTS'),
28
29 grant all on TESTS to PUBLIC;
31 grant all on TESTS to PUBLIC;
Now that I have a table to store results, let’s get to writing a testing tool package.
A TEST Package Specification
In my testing tool’s package, I define a set of variables or constants that are suitable as insert values for SQL statements. I can then use these predefined values when writing test units. This approach has several advantages. First, global use of these testing values will create an environment where testing is done with consistency. In turn, because of that consistency, everyone involved in testing will know what these predefined values are, and then know that they are looking at test data if they encounter these values while testing.
Listing 8-3 is the package specification for my testing tool. On the top half of the specification, I’ve added globally accessible constants to be utilized by all programmers when writing their test units. These are followed by methods designed to aid programmers when writing their test units. By using these methods, the test data is consistently formatted and stored for easy analysis.
Listing 8-3. A Testing Tool Package Spec, rps.test.pks
001 create or replace package TEST as
002 /*
003 test.pks
004 by Donald J. Bales on 2014-10-20
005 A Testing package
006 */
007
008 -- Result constants
009 v_TEST_ERROR constant varchar2(5) := 'ERROR';
010 v_TEST_OK constant varchar2(2) := 'OK';
011 v_TEST_SUCCESS constant varchar2(7) := 'SUCCESS';
012
013 -- Testing constants
014 d_TEST_19000101 constant date :=
015 to_date('19000101', 'YYYYMMDD'),
016 d_TEST_99991231 constant date :=
017 to_date('99991231', 'YYYYMMDD'),
018
019 v_TEST_N constant varchar2(1) := 'N';
020
021 v_TEST_Y constant varchar2(1) := 'Y';
022
023 v_TEST_30 constant varchar2(30) :=
024 'TEST TEST TEST TEST TEST TESTx';
025
026 v_TEST_30_1 constant varchar2(30) :=
027 'TEST1 TEST1 TEST1 TEST1 TEST1x';
028
029 v_TEST_30_2 constant varchar2(30) :=
030 'TEST2 TEST2 TEST2 TEST2 TEST2x';
031
032 v_TEST_80 constant varchar2(80) :=
033 'Test Test Test Test Test Test Test Test '||
034 'Test Test Test Test Test Test Test Testx';
035
036 v_TEST_100 constant varchar2(100) :=
037 'Test Test Test Test Test Test Test Test Test Test '||
038 'Test Test Test Test Test Test Test Test Test Testx';
039
040 v_TEST_2000 constant varchar2(2000) :=
041 'Test Test Test Test Test Test Test Test Test Test '||
042 'Test Test Test Test Test Test Test Test Test Test '||
043 'Test Test Test Test Test Test Test Test Test Test '||
044 'Test Test Test Test Test Test Test Test Test Test '||
045 'Test Test Test Test Test Test Test Test Test Test '||
046 'Test Test Test Test Test Test Test Test Test Test '||
047 'Test Test Test Test Test Test Test Test Test Test '||
048 'Test Test Test Test Test Test Test Test Test Test '||
049 'Test Test Test Test Test Test Test Test Test Test '||
050 'Test Test Test Test Test Test Test Test Test Test '||
051 'Test Test Test Test Test Test Test Test Test Test '||
052 'Test Test Test Test Test Test Test Test Test Test '||
053 'Test Test Test Test Test Test Test Test Test Test '||
054 'Test Test Test Test Test Test Test Test Test Test '||
055 'Test Test Test Test Test Test Test Test Test Test '||
056 'Test Test Test Test Test Test Test Test Test Test '||
057 'Test Test Test Test Test Test Test Test Test Test '||
058 'Test Test Test Test Test Test Test Test Test Test '||
059 'Test Test Test Test Test Test Test Test Test Test '||
060 'Test Test Test Test Test Test Test Test Test Test '||
061 'Test Test Test Test Test Test Test Test Test Test '||
062 'Test Test Test Test Test Test Test Test Test Test '||
063 'Test Test Test Test Test Test Test Test Test Test '||
064 'Test Test Test Test Test Test Test Test Test Test '||
065 'Test Test Test Test Test Test Test Test Test Test '||
066 'Test Test Test Test Test Test Test Test Test Test '||
067 'Test Test Test Test Test Test Test Test Test Test '||
068 'Test Test Test Test Test Test Test Test Test Test '||
069 'Test Test Test Test Test Test Test Test Test Test '||
070 'Test Test Test Test Test Test Test Test Test Test '||
071 'Test Test Test Test Test Test Test Test Test Test '||
072 'Test Test Test Test Test Test Test Test Test Test '||
073 'Test Test Test Test Test Test Test Test Test Test '||
074 'Test Test Test Test Test Test Test Test Test Test '||
075 'Test Test Test Test Test Test Test Test Test Test '||
076 'Test Test Test Test Test Test Test Test Test Test '||
077 'Test Test Test Test Test Test Test Test Test Test '||
078 'Test Test Test Test Test Test Test Test Test Test '||
079 'Test Test Test Test Test Test Test Test Test Test '||
080 'Test Test Test Test Test Test Test Test Test Testx';
081
082 -- 1 2 3 4 5
083 -- 12345678901234567890123456789012345678901234567890
084
085 -- Clear the results of the last test
086 PROCEDURE clear(
087 aiv_object_name TESTS.object_name%TYPE);
088
089
090 -- Set the result of the last test to v_TEST_ERROR
091 PROCEDURE error;
092
093
094 -- Set the result of the last test to the passed Oracle error
095 PROCEDURE error(
096 aiv_result in TESTS.result%TYPE);
097
098
099 -- Display help text
100 PROCEDURE help;
101
102
103 -- Instantiate the package
104 PROCEDURE initialize;
105
106
107 -- Set the result of the last test to v_TEST_OK
108 PROCEDURE ok;
109
110
111 -- Update the test with its results
112 PROCEDURE set_result(
113 aiv_result in TESTS.result%TYPE);
114
115
116 -- Add a test
117 PROCEDURE set_test(
118 aiv_object_name in TESTS.object_name%TYPE,
119 aiv_method_name in TESTS.method_name%TYPE,
120 ain_test_number in TESTS.test_number%TYPE,
121 aiv_description in TESTS.description%TYPE);
122
123
124 -- Set the result of the last test to v_TEST_SUCCESS
125 PROCEDURE success;
126
127
128 -- Test unit
129 PROCEDURE test;
130
131
132 end TEST;
133 /
134 @se.sql TEST
Let’s examine Listing 8-3, line by line:
- Lines 9 through 11 add three character constants, ERROR, OK, and SUCCESS, to be used as values for parameter aiv_result, when calling method result(). OK means the test completed successfully, and ERROR means the test did not complete successfully. Constant SUCCESS is called only at the end of a program unit’s test unit after recording a test for the package or user-defined TYPE in question, in order to flag that the package or TYPE’s test unit completely successfully, without raising any uncaught exceptions. If an uncaught exception does occur, the programmer should pass the value of SQLERRM as the result.
- Lines 14 through 80 add constants to be used globally for testing inserts into SQL tables. These “testing” values not only self-document their values as test values, but also exercise the maximum column size constraints on the columns for tables for which they are used to test.
- Lines 86 and 87 declare method clear(), which a program should call at the beginning of a test unit in order to clear any previous test results before starting a new round of tests.
- Line 91 declares method error(), which a program should call to record that the test resulted in an error.
- Lines 95 and 96 declare an overridden version of method error(), which takes one argument. A program should call this method if it needs to record an Oracle exception error message.
- Lines 107 and 108 declare method ok(), which a program should call to record that an individual test completed without errors.
- Lines 112 and 113 declare the method set_result(). Methods error(), ok(), and success() call this method to record testing results. This method uses pragma autonomous_transaction so entries are not lost if the test fails in the program unit in question because of an uncaught exception.
- Lines 117 through 121 declare the method set_test(), which a programmer should use to record a test that is about to be executed inside a test unit. This method also uses pragma autonomous_transaction so entries are not lost if the test fails in the program unit in question because of an uncaught exception.
- Line 125 declares method success(), which a program should call at the end of a test unit to flag that the entire test unit completed successfully. That doesn’t mean that there weren’t errors during the individual tests; it means that the method test() itself was completely executed.
- Line 129 declares the standard test unit method to be used by every package and user-defined TYPE: test(). This is where a programmer codes the test units for every associated PL/SQL program unit. You’ll see an example in the testing tool’s package body shortly.
The test result constants OK, ERROR, and SUCCESS make it easy to identify errors after tests are run. If you want to know which test unit methods failed, just write a query where you list every object in the table TESTS that doesn’t have a row where the object_name can also be found with a result value of SUCCESS. If you want to know which individual tests failed, write a query to list the rows where column result starts with ERR or ORA. I’ll show you a couple of sample queries like these in the “Automating Testing” section later in this chapter.
Now let’s look at the implementation.
A TEST Package Body
Now that you’ve seen the specification for my testing tool, let’s look at its implementation, including an example of its use. Listing 8-4 is the DDL to create the package body for package TEST.
Listing 8-4. A Testing Tool Package Body, rps.test.pkb
001 create or replace package body TEST as
002 /*
003 test.pkb
004 by Donald J. Bales on 2014-10-20
005 A Testing package
006 */
007
008 -- Hold this value across calls to test() and result()
009 n_id TESTS.id%TYPE;
010
011
012 PROCEDURE clear(
013 aiv_object_name TESTS.object_name%TYPE) is
014
015 pragma autonomous_transaction;
016
017 begin
018 delete TESTS
019 where object_name = aiv_object_name
020 and unique_session_id = SYS.DBMS_SESSION.unique_session_id;
021
022 commit;
023 end clear;
024
025
026 PROCEDURE error is
027
028 begin
029 set_result(v_TEST_ERROR);
030 end error;
031
032
033 PROCEDURE error(
034 aiv_result in TESTS.result%TYPE) is
035
036 begin
037 set_result(aiv_result);
038 end error;
039
040
041 FUNCTION get_id
042 return TESTS.id%TYPE is
043
044 n_id TESTS.id%TYPE;
045
046 begin
047 select TESTS_ID.nextval
048 into n_id
049 from SYS.DUAL;
050
051 return n_id;
052 end get_id;
053
054
055 PROCEDURE help is
056 begin
057 pl('You're on your own buddy.'),
058 end help;
059
060
061 PROCEDURE initialize is
062 begin
063 null;
064 end;
065
066
067 PROCEDURE ok is
068
069 begin
070 set_result(v_TEST_OK);
071 end ok;
072
073
074 PROCEDURE set_result(
075 aiv_result in TESTS.result%TYPE) is
076
077 pragma autonomous_transaction;
078
079 begin
080 update TESTS
081 set result = aiv_result
082 where id = n_id;
083
084 if nvl(sql%rowcount, 0) = 0 then
085 raise_application_error(-20000, 'Can''t find test'||
086 to_char(n_id)||
087 ' on update TEST'||
088 ' in TEST.test'),
089 end if;
090
091 n_id := NULL;
092
093 commit;
094 end set_result;
095
096
097 PROCEDURE set_test(
098 aiv_object_name in TESTS.object_name%TYPE,
099 aiv_method_name in TESTS.method_name%TYPE,
100 ain_test_number in TESTS.test_number%TYPE,
101 aiv_description in TESTS.description%TYPE) is
102
103 pragma autonomous_transaction;
104
105 begin
106 n_id := get_id();
107
108 begin
109 insert into TESTS (
110 id,
111 object_name,
112 method_name,
113 test_number,
114 description,
115 result,
116 unique_session_id,
117 insert_user,
118 insert_date )
119 values (
120 n_id,
121 upper(aiv_object_name),
122 upper(aiv_method_name),
123 ain_test_number,
124 aiv_description,
125 NULL,
126 SYS.DBMS_SESSION.unique_session_id,
127 USER,
128 SYSDATE );
129 exception
130 when OTHERS then
131 raise_application_error(-20000, SQLERRM||
132 ' on insert TEST'||
133 ' in TEST.test'),
134 end;
135 commit;
136 end set_test;
137
138
139 PROCEDURE success is
140
141 begin
142 set_result(v_TEST_SUCCESS);
143 end success;
144
145
146 PROCEDURE test is
147
148 n_number number;
149
150 begin
151 pl('TESTS.test()'),
152 clear('TEST'),
153
154 set_test('TEST', NULL, 1,
155 'Is v_TEST_N equal to N?'),
156 if v_TEST_N = 'N' then
157 ok();
158 else
159 error();
160 end if;
161
162 set_test('TEST', NULL, 2,
163 'Is the length of v_TEST_N equal to 1?'),
164 if nvl(length(v_TEST_N), 0) = 1 then
165 ok();
166 else
167 error();
168 end if;
169
170 set_test('TEST', NULL, 3,
171 'Is v_TEST_Y equal to Y?'),
172 if v_TEST_Y = 'Y' then
173 ok();
174 else
175 error();
176 end if;
177
178 set_test('TEST', NULL, 4,
179 'Is the length of v_TEST_Y equal to 1?'),
180 if nvl(length(v_TEST_Y), 0) = 1 then
181 ok();
182 else
183 error();
184 end if;
185
186 set_test('TEST', NULL, 5,
187 'Is the length of v_TEST_30 equal to 30?'),
188 if nvl(length(v_TEST_30), 0) = 30 then
189 ok();
190 else
191 error();
192 end if;
193
194 set_test('TEST', NULL, 6,
195 'Is the length of v_TEST_30_1 equal to 30?'),
196 if nvl(length(v_TEST_30_1), 0) = 30 then
197 ok();
198 else
199 error();
200 end if;
201
202 set_test('TEST', NULL, 7,
203 'Is the length of v_TEST_30_2 equal to 30?'),
204 if nvl(length(v_TEST_30_2), 0) = 30 then
205 ok();
206 else
207 error();
208 end if;
209
210 set_test('TEST', NULL, 8,
211 'Is the length of v_TEST_80 equal to 80?'),
212 if nvl(length(v_TEST_80), 0) = 80 then
213 ok();
214 else
215 error();
216 end if;
217
218 set_test('TEST', NULL, 9,
219 'Is the length of v_TEST_100 equal to 100?'),
220 if nvl(length(v_TEST_100), 0) = 100 then
221 ok();
222 else
223 error();
224 end if;
225
226 set_test('TEST', NULL, 10,
227 'Is the length of v_TEST_2000 equal to 2000?'),
228 if nvl(length(v_TEST_2000), 0) = 2000 then
229 ok();
230 else
231 error();
232 end if;
233
234 set_test('TEST', 'get_id', 11,
235 'Does get_id() work?'),
236 begin
237 n_number := get_id();
238 if n_number > 0 then
239 ok();
240 else
241 error();
242 end if;
243 exception
244 when OTHERS then
245 error(SQLERRM);
246 end;
247
248 set_test('TEST', 'help', 12,
249 'Does help() work?'),
250 begin
251 help();
252 -- raise_application_error(-20999, 'Testing test unit report'),
253 ok();
254 exception
255 when OTHERS then
256 error(SQLERRM);
257 end;
258
259 set_test('TEST', NULL, NULL, NULL);
260 success();
261 end test;
262
263
264 end TEST;
265 /
266 @be.sql TESTS
Let’s look at Listing 8-4 in detail:
- Lines 12 through 23 implement procedure clear(), which is called by method test() to remove any previous test for the same unique session ID.
- Lines 26 through 30 implement procedure error(), which is called by method test() to record a generic error.
- Lines 33 through 38 implement procedure error(), which is called by method test() inside an exception handler in order to record an Oracle exception’s error message.
Note Do you find the use of the same method names for more than one purpose disturbing? This is called overloading the method names. One error() method is used to record a generic error, while the second is used to record an Oracle error message.
- Lines 41 through 52 implement function get_id(), which returns the next primary key value to be used when inserting a row into table TESTS.
- Lines 67 through 71 implement procedure ok(), which is called by method test() to record the success of an individual test.
- Lines 74 through 94 implement procedure set_result(), which updates the last recorded test with the passed result value. This method uses an instance-level variable, n_id, in coordination with procedure test(). test() saves the last id it allocates to variable n_id in the package’s memory, and then set_result() uses that value when saving the result. I do this to reduce the amount of coding required by the test unit writer.
- Lines 97 through 136 implement procedure set_test(), which is used by test unit writers to record that a test is about to take place.
- Lines 139 through 143 implement procedure success(), which is called by method test() to indicate the successful execution of the test unit itself.
- Lines 146 through 260 implement procedure test(), which is the actual test unit for package TEST. Yes, I’ve used the testing tool to test the testing tool. Neat, huh?
- Line 148, inside the test unit method test(), declares a number variable to temporarily hold the result of one of the tests.
- Line 151 calls pl() to give feedback to the tester that the test has been started.
- Line 152 calls procedure TEST.clear(), passing the name of the object in order to remove any previous test results.
- Lines 154 and 155 call procedure TEST.test(), passing it the information about the test I’m about to perform.
- Line 156 performs my first test, to see if the constant TEST.v_TEST_N has the correct value. Since I’m certain that this test cannot cause an exception, I have not blocked the code for the test.
- Line 157 sets the result for the test to constant TEST.v_TEST_OK by calling procedure ok() if v_TEST_N is indeed the character N; otherwise, on line 159, I set the result to TEST.v_TEST_ERROR by calling procedure error().
- Lines 162 through 232 perform tests on the remaining constants in a similar fashion. With all these tests, I’m confident that an exception cannot possibly be raised, so I don’t block the test code. This is not so for the next test.
- Lines 234 and 235 call procedure TEST.test() in order to record that I’m about to perform a test of function get_id().
- Lines 236 through 246 block the call to method get_id() in order to catch any unexpected exception. Next, I execute my actual test, a call to get_id(). If there is no unexpected exception, the next line, 238, tests to see if the ID value was greater than zero. If so, I call method ok(); otherwise, I call error(). If an exception does occur, I call error(), passing the value of SQLERRM.
- Lines 250 through 256 block a call to help() and then test it in a fashion similar to the previous test.
- Line 258 calls test() for the package, therefore passing only the object’s name.
- Line 259 calls procedure success(). These last two calls will indicate that the test procedure test() itself completed successfully, instead of possibly aborting somewhere in the midst of the test from an unhandled exception.
Now that I’ve shown you my relational testing tool, it’s your turn to create an object-relational version.
It’s Your Turn to Create a Testing Tool
Your assignment is to recreate the testing tool I’ve just shown you as an object-relational TYPE instead of a package. There’s one twist, though. You can’t declare constants, pseudo-constants, or variables in a TYPE, as I did in the TEST package specification, so you’ll need to declare additional parameterless functions that return the constant values.
To create an object-relational testing tool, follow these steps.
- Write a DDL script to create a TYPE specification named TEST. This type should have an attribute for every column name in table TESTS. It should also have corresponding methods for each method found in package TEST. In addition, you’ll add parameterless functions in the place of the constants in package TEST. Include the method signature for, but skip coding the methods help() and test(). We’ll work on coding test() in the next section, and help() in the next chapter.
- Save your script as ops.test.tps.
- Log in as username OPS.
- Compile your script: SQL> @ops.test.tps.
- Write a DDL script to create the object table TESTS of TEST.
- Save your script as ops.tests.tab.
- Compile your script: SQL> @ops.tests.tab.
- Write a DDL script to create the TYPE BODY for TEST.
- Save your script as ops.test.tpb.
- Compile your script: SQL> @ops.test.tpb.
But what about testing your new test tool? I’ll devote the entire next section to that task. Do you want to see my solution for this exercise? Listings 8-5, 8-6, and 8-7 are the DDL for the TYPE specification, table, and body, respectively, for my version of TYPE TEST.
Listing 8-5. A DDL Script for TYPE TEST’s Spec, ops.test.tps
001 drop type TEST;
002 create type TEST as object (
003 /*
004 test.tps
005 by Donald J. Bales on 2014-10-20
006 A Type for logging test results
007 */
008 -- Type TEST's attributes
009 id number,
010 object_name varchar2(30),
011 method_name varchar2(30),
012 test_number number,
013 description varchar2(80),
014 result varchar2(256),
015 unique_session_id varchar2(24),
016 insert_user varchar2(30),
017 insert_date date,
018
019 -- Allocate the next primary key value fir id
020 STATIC FUNCTION get_id
021 return number,
022
023 -- Get the test value for January 1, 1900
024 STATIC FUNCTION get_test_19000101
025 return date,
026
027 -- Get the test value for December 31, 1999
028 STATIC FUNCTION get_test_19991231
029 return date,
030
031 -- Get the test value N for any indicators
032 STATIC FUNCTION get_test_n
033 return varchar2,
034
035 -- Get the test value Y for any indicators
036 STATIC FUNCTION get_test_y
037 return varchar2,
038
039 -- Get the 30 character test value
040 STATIC FUNCTION get_test_30
041 return varchar2,
042
043 -- Get the first 30 character test value duplicate for LIKE
044 STATIC FUNCTION get_test_30_1
045 return varchar2,
046
047 -- Get the second 30 character test value duplicate for LIKE
048 STATIC FUNCTION get_test_30_2
049 return varchar2,
050
051 -- Get the 80 character test value
052 STATIC FUNCTION get_test_80
053 return varchar2,
054
055 -- Get the 100 character test value
056 STATIC FUNCTION get_test_100
057 return varchar2,
058
059 -- Get the 2000 character test value
060 STATIC FUNCTION get_test_2000
061 return varchar2,
062
063 -- Clear any previous test run for the specified object name
064 STATIC PROCEDURE clear(
065 aiv_object_name varchar2),
066
067 -- Set the result to ERROR
068 MEMBER PROCEDURE error,
069
070 -- Set the result to Oracle ERROR
071 MEMBER PROCEDURE error(
072 aiv_result in varchar2),
073
074 -- Set the result to the specified result value
075 MEMBER PROCEDURE set_result(
076 aiv_result in varchar2),
077
078 -- Show the help text for this object
079 STATIC PROCEDURE help,
080
081 -- Set the result to OK
082 MEMBER PROCEDURE ok,
083
084 -- Set the result of the execution of test() to SUCCESS
085 MEMBER PROCEDURE success,
086
087 -- Test this object
088 STATIC PROCEDURE "test",
089
090 -- Set the test about to be performed
091 MEMBER PROCEDURE set_test(
092 aiv_object_name in varchar2,
093 aiv_method_name in varchar2,
094 ain_test_number in number,
095 aiv_description in varchar2),
096
097 -- Get the map value
098 MAP MEMBER FUNCTION to_map
099 return number,
100
101 -- Parameter-less constructor
102 CONSTRUCTOR FUNCTION test(
103 self in out nocopy test)
104 return self as result,
105
106 -- Convenience constructor
107 CONSTRUCTOR FUNCTION test(
108 self in out nocopy test,
109 ain_id in number,
110 aiv_object_name in varchar2,
111 aiv_method_name in varchar2,
112 ain_test_number in number,
113 aiv_description in varchar2)
114 return self as result
115 );
116 /
117 @se.sql
118
119 grant execute on TEST to public;
In Listing 8-5, the TYPE specification for TEST, I added the static functions get_test_19000101() through get_test_2000() to act as and replace the constants d_TEST_19000101 through v_TEST_2000 from the package TEST specification on lines 14 through 80.
Listing 8-6. The DDL Script for Table TESTS, ops.tests.tab
01 rem tests.tab
02 rem by Donald Bales on 2014-10-20
03 rem Create test results
04
05 --drop table TESTS;
06 create table TESTS of TEST
07 tablespace USERS pctfree 0
08 storage (initial 1M next 1M pctincrease 0);
09
10 alter table TESTS add
11 constraint TESTS_PK
12 primary key (
13 id )
14 using index
15 tablespace USERS pctfree 0
16 storage (initial 1M next 1M pctincrease 0);
17
18 --drop sequence TESTS_ID;
19 create sequence TESTS_ID
20 start with 1 order;
21
22 execute SYS.DBMS_STATS.gather_table_stats(USER, 'TESTS'),
23
24 grant all on TESTS to PUBLIC;
There’s nothing really new in Listing 8-6, the script to create a table based on TYPE TEST.
Listing 8-7. A DDL Script for TYPE TEST’s BODY, ops.test.tpb
001 create or replace type body TEST as
002 /*
003 test.tpb
004 by Donald J. Bales on 2014-10-20
005 A Type for logging test results
006 */
007
008 STATIC PROCEDURE clear(
009 aiv_object_name varchar2) is
010
011 pragma autonomous_transaction;
012
013 begin
014 delete TESTS
015 where object_name = aiv_object_name
016 and unique_session_id = SYS.DBMS_SESSION.unique_session_id;
017
018 commit;
019 end clear;
020
021
022 STATIC FUNCTION get_id
023 return number is
024
025 n_id number;
026
027 begin
028 select TESTS_ID.nextval
029 into n_id
030 from SYS.DUAL;
031
032 return n_id;
033 end get_id;
034
035
036 STATIC FUNCTION get_test_19000101
037 return date is
038
039 begin
040 return to_date('19000101', 'YYYYMMDD'),
041 end get_test_19000101;
042
043
044 STATIC FUNCTION get_test_19991231
045 return date is
046
047 begin
048 return to_date('19991231', 'YYYYMMDD'),
049 end get_test_19991231;
050
051
052 STATIC FUNCTION get_test_n
053 return varchar2 is
054
055 begin
056 return 'N';
057 end get_test_n;
058
059
060 STATIC FUNCTION get_test_y
061 return varchar2 is
062
063 begin
064 return 'Y';
065 end get_test_y;
066
067
068 STATIC FUNCTION get_test_30
069 return varchar2 is
070
071 begin
072 return 'TEST TEST TEST TEST TEST TESTx';
073 end get_test_30;
074
075
076 STATIC FUNCTION get_test_30_1
077 return varchar2 is
078
079 begin
080 return 'TEST1 TEST1 TEST1 TEST1 TEST1x';
081 end get_test_30_1;
082
083
084 STATIC FUNCTION get_test_30_2
085 return varchar2 is
086
087 begin
088 return 'TEST2 TEST2 TEST2 TEST2 TEST2x';
089 end get_test_30_2;
090
091
092 STATIC FUNCTION get_test_80
093 return varchar2 is
094
095 begin
096 return 'Test Test Test Test Test Test Test Test '||
097 'Test Test Test Test Test Test Test Testx';
098 end get_test_80;
099
100
101 STATIC FUNCTION get_test_100
102 return varchar2 is
103
104 begin
105 return 'Test Test Test Test Test Test Test Test Test Test '||
106 'Test Test Test Test Test Test Test Test Test Testx';
107 end get_test_100;
108
109
110 STATIC FUNCTION get_test_2000
111 return varchar2 is
112 -- 1 2 3 4 5
113 -- 12345678901234567890123456789012345678901234567890
114 begin
115 return 'Test Test Test Test Test Test Test Test Test Test '||
116 'Test Test Test Test Test Test Test Test Test Test '||
117 'Test Test Test Test Test Test Test Test Test Test '||
118 'Test Test Test Test Test Test Test Test Test Test '||
119 'Test Test Test Test Test Test Test Test Test Test '||
120 'Test Test Test Test Test Test Test Test Test Test '||
121 'Test Test Test Test Test Test Test Test Test Test '||
122 'Test Test Test Test Test Test Test Test Test Test '||
123 'Test Test Test Test Test Test Test Test Test Test '||
124 'Test Test Test Test Test Test Test Test Test Test '||
125 'Test Test Test Test Test Test Test Test Test Test '||
126 'Test Test Test Test Test Test Test Test Test Test '||
127 'Test Test Test Test Test Test Test Test Test Test '||
128 'Test Test Test Test Test Test Test Test Test Test '||
129 'Test Test Test Test Test Test Test Test Test Test '||
130 'Test Test Test Test Test Test Test Test Test Test '||
131 'Test Test Test Test Test Test Test Test Test Test '||
132 'Test Test Test Test Test Test Test Test Test Test '||
133 'Test Test Test Test Test Test Test Test Test Test '||
134 'Test Test Test Test Test Test Test Test Test Test '||
135 'Test Test Test Test Test Test Test Test Test Test '||
136 'Test Test Test Test Test Test Test Test Test Test '||
137 'Test Test Test Test Test Test Test Test Test Test '||
138 'Test Test Test Test Test Test Test Test Test Test '||
139 'Test Test Test Test Test Test Test Test Test Test '||
140 'Test Test Test Test Test Test Test Test Test Test '||
141 'Test Test Test Test Test Test Test Test Test Test '||
142 'Test Test Test Test Test Test Test Test Test Test '||
143 'Test Test Test Test Test Test Test Test Test Test '||
144 'Test Test Test Test Test Test Test Test Test Test '||
145 'Test Test Test Test Test Test Test Test Test Test '||
146 'Test Test Test Test Test Test Test Test Test Test '||
147 'Test Test Test Test Test Test Test Test Test Test '||
148 'Test Test Test Test Test Test Test Test Test Test '||
149 'Test Test Test Test Test Test Test Test Test Test '||
150 'Test Test Test Test Test Test Test Test Test Test '||
151 'Test Test Test Test Test Test Test Test Test Test '||
152 'Test Test Test Test Test Test Test Test Test Test '||
153 'Test Test Test Test Test Test Test Test Test Test '||
154 'Test Test Test Test Test Test Test Test Test Testx';
155 end get_test_2000;
156
157
158 STATIC PROCEDURE help is
159 begin
160 pl('You''re on your own buddy.'),
161 end help;
162
163
164 MEMBER PROCEDURE error is
165
166 begin
167 set_result('ERROR'),
168 end error;
169
170
171 MEMBER PROCEDURE error(
172 aiv_result in varchar2) is
173
174 begin
175 set_result(aiv_result);
176 end error;
177
178
179 MEMBER PROCEDURE ok is
180
181 begin
182 set_result('OK'),
183 end ok;
184
185
186 MEMBER PROCEDURE set_result(
187 aiv_result in varchar2) is
188
189 pragma autonomous_transaction;
190
191 begin
192 result := aiv_result;
193
194 update TESTS
195 set result = self.result
196 where id = self.id;
197
198 if nvl(sql%rowcount, 0) = 0 then
199 raise_application_error(-20000, 'Can''t find test'||
200 to_char(self.id)||
201 ' on update TEST'||
202 ' in TEST_TS.test'),
203 end if;
204
205 self := new test();
206
207 commit;
208 end set_result;
209
210
211 MEMBER PROCEDURE set_test(
212 aiv_object_name in varchar2,
213 aiv_method_name in varchar2,
214 ain_test_number in number,
215 aiv_description in varchar2) is
216
217 pragma autonomous_transaction;
218
219 begin
220 self.id := TEST.get_id();
221 self.object_name := upper(aiv_object_name);
222 self.method_name := upper(aiv_method_name);
223 self.test_number := ain_test_number;
224 self.description := aiv_description;
225 self.result := NULL;
226 self.unique_session_id := SYS.DBMS_SESSION.unique_session_id;
227 self.insert_user := USER;
228 self.insert_date := SYSDATE;
229
230 begin
231 insert into TESTS values (self);
232 exception
233 when OTHERS then
234 raise_application_error(-20000, SQLERRM||
235 ' on insert TESTS'||
236 ' in TEST.set_test'),
237 end;
238 commit;
239 end set_test;
240
241
242 MEMBER PROCEDURE success is
243
244 begin
245 set_result('SUCCESS'),
246 end success;
247
248
249 STATIC PROCEDURE "test" is
250
251 n_number number;
252 o_test TEST;
253
254 begin
255 pl('TEST.test()'),
256
257 -- A defect requires the schema owner
258 &_USER..TEST.clear('TEST'),
259
260 o_test := new TEST();
261 o_test.set_test('TEST', NULL, 1,
262 'Is get_test_N equal to N?'),
263 if TEST.get_test_N = 'N' then
264 o_test.success();
265 else
266 o_test.error();
267 end if;
268
269 o_test.set_test('TEST', NULL, 2,
270 'Is the length of get_test_N equal to 1?'),
271 if nvl(length(TEST.get_test_N), 0) = 1 then
272 o_test.success();
273 else
274 o_test.error();
275 end if;
276
277 o_test.set_test('TEST', NULL, 3,
278 'Is get_test_Y equal to Y?'),
279 if TEST.get_test_Y = 'Y' then
280 o_test.success();
281 else
282 o_test.error();
283 end if;
284
285 o_test.set_test('TEST', NULL, 4,
286 'Is the length of get_test_Y equal to 1?'),
287 if nvl(length(TEST.get_test_Y), 0) = 1 then
288 o_test.success();
289 else
290 o_test.error();
291 end if;
292
293 o_test.set_test('TEST', NULL, 5,
294 'Is the length of get_test_30 equal to 30?'),
295 if nvl(length(TEST.get_test_30), 0) = 30 then
296 o_test.success();
297 else
298 o_test.error();
299 end if;
300
301 o_test.set_test('TEST', NULL, 6,
302 'Is the length of get_test_30_1 equal to 30?'),
303 if nvl(length(TEST.get_test_30_1), 0) = 30 then
304 o_test.success();
305 else
306 o_test.error();
307 end if;
308
309 o_test.set_test('TEST', NULL, 7,
310 'Is the length of get_test_30_2 equal to 30?'),
311 if nvl(length(TEST.get_test_30_2), 0) = 30 then
312 o_test.success();
313 else
314 o_test.error();
315 end if;
316
317 o_test.set_test('TEST', NULL, 8,
318 'Is the length of get_test_80 equal to 80?'),
319 if nvl(length(TEST.get_test_80), 0) = 80 then
320 o_test.success();
321 else
322 o_test.error();
323 end if;
324
325 o_test.set_test('TEST', NULL, 9,
326 'Is the length of get_test_100 equal to 100?'),
327 if nvl(length(TEST.get_test_100), 0) = 100 then
328 o_test.success();
329 else
330 o_test.error();
331 end if;
332
333 o_test.set_test('TEST', NULL, 10,
334 'Is the length of get_test_2000 equal to 2000?'),
335 if nvl(length(TEST.get_test_2000), 0) = 2000 then
336 o_test.success();
337 else
338 o_test.error();
339 end if;
340
341 o_test.set_test('TEST', 'get_id', 11,
342 'Does get_id() work?'),
343 begin
344 n_number := TEST.get_id();
345 if n_number > 0 then
346 o_test.success();
347 else
348 o_test.error();
349 end if;
350 exception
351 when OTHERS then
352 o_test.error(SQLERRM);
353 end;
354
355 o_test.set_test('TEST', 'help', 12,
356 'Does help() work?'),
357 begin
358 &_USER..TEST.help();
359 raise_application_error(-20999, 'Testing the error routine'),
360 o_test.success();
361 exception
362 when OTHERS then
363 o_test.error(SQLERRM);
364 end;
365
366 o_test.set_test('TEST', NULL, NULL, NULL);
367 o_test.success();
368 end "test";
369
370
371 MAP MEMBER FUNCTION to_map
372 return number is
373
374 begin
375 return self.id;
376 end to_map;
377
378
379 CONSTRUCTOR FUNCTION test(
380 self in out nocopy test)
381 return self as result is
382
383 begin
384 self.id := NULL;
385 self.object_name := NULL;
386 self.method_name := NULL;
387 self.test_number := NULL;
388 self.description := NULL;
389 self.result := NULL;
390 self.unique_session_id := NULL;
391 self.insert_user := NULL;
392 self.insert_date := NULL;
393
394 return;
395 end test;
396
397
398 CONSTRUCTOR FUNCTION test(
399 self in out nocopy test,
400 ain_id in number,
401 aiv_object_name in varchar2,
402 aiv_method_name in varchar2,
403 ain_test_number in number,
404 aiv_description in varchar2)
405 return self as result is
406
407 begin
408 self.id := ain_id;
409 self.object_name := aiv_object_name;
410 self.method_name := aiv_method_name;
411 self.test_number := ain_test_number;
412 self.description := aiv_description;
413 self.result := NULL;
414 self.unique_session_id := SYS.DBMS_SESSION.unique_session_id;
415 self.insert_user := USER;
416 self.insert_date := SYSDATE;
417
418 return;
419 end test;
420
421
422 end;
423 /
424 @be.sql TEST
Did you notice in Listing 8-6 that I used the SQL*Plus defined variable &USER in the source listing? This is a work-around to a defect in the Oracle compiler when trying to resolve the name of a method that is part of the same TYPE BODY.
In the sections that follow, I’ll use the relational testing tool to test packages and the object-relational tool I just created to test TYPEs. So let’s move on to a discussion of testing with these new testing tools.
Testing
Do you recall the discussion about table types early in Chapter 1, where I said that tables fall into one of three categories: content, codes, or intersections? The types of methods and method names used by each of these three categories will follow a pattern. Content table packages (or TYPEs) will all have a similar set of methods and method names. Code table packages (or TYPEs) will also have a similar set of methods and method names. And so will intersection table packages (or TYPEs).
Accordingly, these three categories will have a pattern of tests that follows the set of methods and method names used by each category. Content table packages will generally have methods to insert, update, delete, and select a specified row from a table. In contrast, a code table will have only a method to get the ID value for a specified code, or code and description for a specified ID. Intersection table packages will have methods like content table packages, and additional methods suitable only for intersections.
So the SQL Circle of Life I talked about earlier in this chapter will be fully employed for content and intersection table packages but not for code table packages. Since content tables are usually dependent on one or more code tables, and intersection tables are dependent on two content tables, let’s start with a code table package. I’ll cover the testing of the WORKER_TYPE table package now, and you’ll code and test the GENDER_TYPE table package later in this section.
Testing a Code Table Package
As usual, let’s dive right into a code listing. Listings 8-8 and 8-9 are the full-blown, production-ready packages for the WORKER_TYPE table.
The package specification lists methods that are specific to a code table.
- get_code_descr(): Used to retrieve the code and description values for a specified ID.
- get_code_id_descr(): Used to retrieve the code, ID, and description values for a fully or partially specified code. For a partially specified code, the method may return a single matching set of values or raise exception TOO_MANY_ROWS if more than one matching entry exists in the table.
- get_id(): Returns the ID that corresponds to the specified code.
The package specification also lists some methods that are universal to all table packages.
- get_id(): Returns a newly allocated primary key value for the table.
- help(): Displays help text for the package.
- test(): Tests the package.
Listing 8-8. The Specification for Table Package WORKER_TYPE,rps.worker_type.pks
01 create or replace PACKAGE WORKER_TYPE as
02 /*
03 worker_type.pks
04 by Don Bales on 2014-10-20
05 Code Table WORKER_TYPES's methods.
06 */
07
08
09 -- Gets the code and decription values for the specified ain_id.
10 PROCEDURE get_code_descr(
11 ain_id in WORKER_TYPES.id%TYPE,
12 aov_code out WORKER_TYPES.code%TYPE,
13 aov_description out WORKER_TYPES.description%TYPE );
14
15
16 -- Verifies the passed aiov_code value is an exact or like match on
17 -- the date specified.
18 PROCEDURE get_code_id_descr(
19 aiov_code in out WORKER_TYPES.code%TYPE,
20 aon_id out WORKER_TYPES.id%TYPE,
21 aov_description out WORKER_TYPES.description%TYPE,
22 aid_on in WORKER_TYPES.active_date%TYPE );
23
24
25 -- Verifies the passed aiov_code value is currently an exact or like
26 -- match.
27 PROCEDURE get_code_id_descr(
28 aiov_code in out WORKER_TYPES.code%TYPE,
29 aon_id out WORKER_TYPES.id%TYPE,
30 aov_description out WORKER_TYPES.description%TYPE );
31
32
33 -- Returns a newly allocated id value.
34 FUNCTION get_id
35 return WORKER_TYPES.id%TYPE;
36
37
38 -- Returns the id for the specified code value.
39 FUNCTION get_id(
40 aiv_code in WORKER_TYPES.code%TYPE )
41 return WORKER_TYPES.id%TYPE;
42
43
44 -- Test-based help for this package. "set serveroutput on" in
45 -- SQL*Plus.
46 PROCEDURE help;
47
48
49 -- Test units for this package.
50 PROCEDURE test;
51
52
53 end WORKER_TYPE;
54 /
55 @se.sql WORKER_TYPE
Listing 8-9. The Body for Table Package WORKER_TYPE ,rps. worker_type.pkb
001 create or replace PACKAGE BODY WORKER_TYPE as
002 /*
003 worker_type.pkb
004 by Don Bales on 2014-10-20
005 Table WORKER_TYPES's methods
006 */
007
008
009 PROCEDURE get_code_descr(
010 ain_id in WORKER_TYPES.id%TYPE,
011 aov_code out WORKER_TYPES.code%TYPE,
012 aov_description out WORKER_TYPES.description%TYPE ) is
013
014 begin
015 select code,
016 description
017 into aov_code,
018 aov_description
019 from WORKER_TYPES
020 where id = ain_id;
021 end get_code_descr;
022
023
024 PROCEDURE get_code_id_descr(
025 aiov_code in out WORKER_TYPES.code%TYPE,
026 aon_id out WORKER_TYPES.id%TYPE,
027 aov_description out WORKER_TYPES.description%TYPE,
028 aid_on in WORKER_TYPES.active_date%TYPE ) is
029
030 v_code WORKER_TYPES.code%TYPE;
031
032 begin
033 select id,
034 description
035 into aon_id,
036 aov_description
037 from WORKER_TYPES
038 where code = aiov_code
039 and aid_on between active_date and nvl(inactive_date, DATE_.d_MAX);
040 exception
041 when NO_DATA_FOUND then
042 select id,
043 code,
044 description
045 into aon_id,
046 v_code,
047 aov_description
048 from WORKER_TYPES
049 where code like aiov_code||'%'
050 and aid_on between active_date and nvl(inactive_date, DATE_.d_MAX);
051
052 aiov_code := v_code;
053 end get_code_id_descr;
054
055
056 PROCEDURE get_code_id_descr(
057 aiov_code in out WORKER_TYPES.code%TYPE,
058 aon_id out WORKER_TYPES.id%TYPE,
059 aov_description out WORKER_TYPES.description%TYPE ) is
060
061 begin
062 get_code_id_descr(
063 aiov_code,
064 aon_id,
065 aov_description,
066 SYSDATE );
067 end get_code_id_descr;
068
069
070 FUNCTION get_id
071 return WORKER_TYPES.id%TYPE is
072
073 n_id WORKER_TYPES.id%TYPE;
074
075 begin
076 select WORKER_TYPES_ID.nextval
077 into n_id
078 from SYS.DUAL;
079
080 return n_id;
081 end get_id;
082
083
084 FUNCTION get_id(
085 aiv_code in WORKER_TYPES.code%TYPE )
086 return WORKER_TYPES.id%TYPE is
087
088 n_id WORKER_TYPES.id%TYPE;
089
090 begin
091 select id
092 into n_id
093 from WORKER_TYPES
094 where code = aiv_code;
095
096 return n_id;
097 end get_id;
098
099
100 PROCEDURE help is
101
102 begin
103 -- 12345678901234567890123456789012345678901234567890123456789012345678901234567890
104 pl('=================================== PACKAGE ===================================='),
105 pl(chr(9));
106 pl('WORKER_TYPE'),
107 pl(chr(9));
108 pl('----------------------------------- FUNCTIONS ----------------------------------'),
109 pl(chr(9));
110 pl('WORKER_TYPE.get_id'),
111 pl('return WORKER_TYPES.id%TYPE;'),
112 pl(chr(9)||'Returns a newly allocated sequence value for id.'),
113 pl(chr(9));
114 pl('WORKER_TYPE.get_id('),
115 pl('aiv_code in WORKER_TYPES.code%TYPE )'),
116 pl('return WORKER_TYPES.id%TYPE;'),
117 pl(chr(9)||'Returns the corresponding id for the specified code.'),
118 pl(chr(9));
119 pl('----------------------------------- PROCEDURES ---------------------------------'),
120 pl(chr(9));
121 pl('WORKER_TYPE.get_code_descr('),
122 pl('ain_id in WORKER_TYPES.id%TYPE,'),
123 pl('aov_code out WORKER_TYPES.code%TYPE,'),
124 pl('aov_description out WORKER_TYPES.description%TYPE );'),
125 pl(chr(9)||'Gets the corresponding code and description for the specified'),
126 pl(chr(9)||'id.'),
127 pl(chr(9));
128 pl('WORKER_TYPE.get_code_id_descr('),
129 pl('aiov_code in out WORKER_TYPES.code%TYPE,'),
130 pl('aon_id out WORKER_TYPES.id%TYPE,'),
131 pl('aov_description out WORKER_TYPES.description%TYPE,'),
132 pl('aid_on in WORKER_TYPES.active_date%TYPE );'),
133 pl(chr(9)||'Gets the corresponding code, id, and description for'),
134 pl(chr(9)||'the specified code. First it trys to find an exact match. If one'),
135 pl(chr(9)||'cannot be found, it trys to find a like match. It may throw a'),
136 pl(chr(9)||'NO_DATA_FOUND or a TOO_MANY_ROWS exception if a match cannot be'),
137 pl(chr(9)||'found for the specified code and point in time.'),
138 pl(chr(9));
139 pl('WORKER_TYPE.get_code_id_descr('),
140 pl('aiov_code in out WORKER_TYPES.code%TYPE,'),
141 pl('aon_id out WORKER_TYPES.id%TYPE,'),
142 pl('aov_description out WORKER_TYPES.description%TYPE );'),
143 pl(chr(9)||'Gets the corresponding code, id, and description for'),
144 pl(chr(9)||'the specified code. First it trys to find an exact match. If one'),
145 pl(chr(9)||'cannot be found, it trys to find a like match. It may throw a'),
146 pl(chr(9)||'NO_DATA_FOUND or a TOO_MANY_ROWS exception if a match cannot be'),
147 pl(chr(9)||'found for the specified code at the current point in time.'),
148 pl(chr(9));
149 pl('WORKER_TYPE.help( );'),
150 pl(chr(9)||'Displays this help text if set serveroutput is on.'),
151 pl(chr(9));
152 pl('WORKER_TYPE.test( );'),
153 pl(chr(9)||'Built-in test unit. It will report success or error for each test if set'),
154 pl(chr(9)||'serveroutput is on.'),
155 pl(chr(9));
156 end help;
157
158
159 PROCEDURE test is
160
161 n_id WORKER_TYPES.id%TYPE;
162 v_code WORKER_TYPES.code%TYPE;
163 v_description WORKER_TYPES.description%TYPE;
164
165 begin
166 -- Send feedback that the test ran
167 pl('WORKER_TYPE.test()'),
168
169 -- Clear the last set of test results
170 &_USER..TEST.clear('WORKER_TYPE'),
171
172 -- First, we need some test values
173
174 -- Let's make sure they don't already exist: DELETE
175 &_USER..TEST.set_test('WORKER_TYPE', 'DELETE', 0,
176 'Delete test entries'),
177 begin
178 delete WORKER_TYPES
179 where code in (
180 &_USER..TEST.v_TEST_30,
181 &_USER..TEST.v_TEST_30_1,
182 &_USER..TEST.v_TEST_30_2);
183 &_USER..TEST.ok();
184 exception
185 when OTHERS then
186 &_USER..TEST.error(SQLERRM);
187 end;
188
189 -- Now let's add three test codes: INSERT
190 &_USER..TEST.set_test('WORKER_TYPE', 'INSERT', 1,
191 'Insert 3 test entries'),
192 begin
193 insert into WORKER_TYPES (
194 id,
195 code,
196 description,
197 active_date,
198 inactive_date )
199 values (
200 get_id(),
201 &_USER..TEST.v_TEST_30,
202 &_USER..TEST.v_TEST_80,
203 &_USER..TEST.d_TEST_19000101,
204 &_USER..TEST.d_TEST_99991231 );
205
206 insert into WORKER_TYPES (
207 id,
208 code,
209 description,
210 active_date,
211 inactive_date )
212 values (
213 get_id(),
214 &_USER..TEST.v_TEST_30_1,
215 &_USER..TEST.v_TEST_80,
216 &_USER..TEST.d_TEST_19000101,
217 &_USER..TEST.d_TEST_99991231 );
218
219 insert into WORKER_TYPES (
220 id,
221 code,
222 description,
223 active_date,
224 inactive_date )
225 values (
226 get_id(),
227 &_USER..TEST.v_TEST_30_2,
228 &_USER..TEST.v_TEST_80,
229 &_USER..TEST.d_TEST_19000101,
230 &_USER..TEST.d_TEST_99991231 );
231
232 &_USER..TEST.ok();
233 exception
234 when OTHERS then
235 &_USER..TEST.error(SQLERRM);
236 end;
237
238 -- Now that we have test entries,
239 -- let's test the package methods
240 &_USER..TEST.set_test('WORKER_TYPE', 'get_id()', 2,
241 'Get the ID for the specified code'),
242 begin
243 n_id := get_id(&_USER..TEST.v_TEST_30);
244
245 if n_id > 0 then
246 &_USER..TEST.ok();
247 else
248 &_USER..TEST.error();
249 end if;
250 exception
251 when OTHERS then
252 &_USER..TEST.error(SQLERRM);
253 end;
254
255 &_USER..TEST.set_test('WORKER_TYPE', 'get_code_descr()', 3,
256 'Get the code and description for the specified ID'),
257 begin
258 get_code_descr(
259 n_id,
260 v_code,
261 v_description);
262 if v_code = &_USER..TEST.v_TEST_30 and
263 v_description = &_USER..TEST.v_TEST_80 then
264 &_USER..TEST.ok();
265 else
266 &_USER..TEST.error();
267 end if;
268 exception
269 when OTHERS then
270 &_USER..TEST.error(SQLERRM);
271 end;
272
273 &_USER..TEST.set_test('WORKER_TYPE', 'get_code_id_descr()', 4,
274 'Get the code, ID, and description for the specified code'),
275 begin
276 v_code := &_USER..TEST.v_TEST_30;
277 get_code_id_descr(
278 v_code,
279 n_id,
280 v_description);
281 if v_code = &_USER..TEST.v_TEST_30 and
282 n_id > 0 and
283 v_description = &_USER..TEST.v_TEST_80 then
284 &_USER..TEST.ok();
285 else
286 &_USER..TEST.error();
287 end if;
288 exception
289 when OTHERS then
290 &_USER..TEST.error(SQLERRM);
291 end;
292
293 &_USER..TEST.set_test('WORKER_TYPE', 'get_code_id_descr()', 5,
294 'Get the code, ID, and description for the specified date'),
295 begin
296 v_code := 'TEST';
297 -- This test should raise a TOO_MANY_ROWS exception
298 -- because at least three duplicate values will
299 -- on the date specified
300 get_code_id_descr(
301 v_code,
302 n_id,
303 v_description,
304 &_USER..TEST.d_TEST_99991231);
305 if v_code = &_USER..TEST.v_TEST_30 and
306 n_id > 0 and
307 v_description = &_USER..TEST.v_TEST_80 then
308 &_USER..TEST.ok();
309 else
310 &_USER..TEST.error();
311 end if;
312 exception
313 when TOO_MANY_ROWS then
314 &_USER..TEST.ok();
315 when OTHERS then
316 &_USER..TEST.error(SQLERRM);
317 end;
318
319 &_USER..TEST.set_test('WORKER_TYPE', 'help()', 6,
320 'Display help'),
321 begin
322 help();
323 &_USER..TEST.ok();
324 exception
325 when OTHERS then
326 &_USER..TEST.error(SQLERRM);
327 end;
328
329 -- Let's make sure they don't already exist: DELETE
330 &_USER..TEST.set_test('WORKER_TYPE', 'DELETE', 7,
331 'Delete test entries'),
332 begin
333 delete WORKER_TYPES
334 where code in (
335 &_USER..TEST.v_TEST_30,
336 &_USER..TEST.v_TEST_30_1,
337 &_USER..TEST.v_TEST_30_2);
338 &_USER..TEST.ok();
339 exception
340 when OTHERS then
341 &_USER..TEST.error(SQLERRM);
342 end;
343
344 &_USER..TEST.set_test('WORKER_TYPE', NULL, NULL, NULL);
345 &_USER..TEST.success();
346 end test;
347
348
349 end WORKER_TYPE;
350 /
351 @be.sql WORKER_TYPE
I’ll explain only the test unit method test() in Listing 8-9 this time around.
- Lines 161 through 163 declare a handful of variables that I will need while testing the package’s methods.
- Line 167 uses pl() to output some feedback to the tester that the test method did indeed execute.
- Line 170 clears any previous test results for this package.
- Lines 175 through 187 delete any existing “test” entries so they don’t cause a problem with the current test. I do this with a SQL DELETE statement because the code table package pattern of methods has no ongoing use for a routine that deletes code values.
- Lines 190 through 236 insert three “test” entries into the code table. The first entry uses the standard 30-character value for the code. I’ll try to find an exact match for this value later in testing. The second and third entries exist to cause a TOO_MANY_ROWS exception later in testing. Without explicitly testing the method, get_id(), the method that returns a new primary key value is also tested during the insertion of the three “test” entries.
- Lines 240 through 253 test the second get_id() method, the code table package pattern method, which returns a code’s ID value.
- Lines 255 through 271 test method get_code_descr().
- Lines 273 through 291 test the method get_code_id_descr() for an exact match on today’s date.
- Lines 293 through 317 test the method get_code_id_descr() to verify it will raise exception TOO_MANY_ROWS when there is no exact match.
- Lines 319 through 327 test method help().
- Lines 330 through 342 delete the “test” entries I added at the beginning of the test. This means I’ve used DELETE, INSERT, and then DELETE as the SQL Circle of Life for testing this code table package.
- Lines 344 and 345 add the entry to the testing table that indicates the test method itself ran successfully.
I can now query the table TESTS to find the results of the test. Listing 8-10 is a SQL*Plus script to list the results of the last performed test.
Listing 8-10. A Report to Show the Results of the Last Test, rps.last_test_results.sql
01 rem last_test_results.sql
02 rem by Donald J. Bales on 2014-10-20
03 rem Display the last test results
04
05 set linesize 1000;
06 set newpage 1;
07 set pagesize 32767;
08 set trimspool on;
09
10 column test format a34;
11 column t# format 99;
12 column description format a27;
13 column result format a7;
14
15 spool last_test_results.txt;
16
17 select t.object_name||
18 decode(substr(t.method_name, -1, 1), ')', '.', ' ')||
19 t.method_name test,
20 t.test_number t#,
21 t.description,
22 t.result
23 from TESTS t
24 where t.unique_session_id = SYS.DBMS_SESSION.unique_session_id
25 and t.object_name = (
26 select e.object_name
27 from TESTS e
28 where e.unique_session_id = SYS.DBMS_SESSION.unique_session_id
29 and e.id = (
30 select max(x.id)
31 from TESTS x
32 where x.unique_session_id = SYS.DBMS_SESSION.unique_session_id))
33 order by t.id;
34
35 spool off;
When I run script last_test_results.sql after testing WORKER_TYPE I get the following output from SQL*Plus:
TEST T# DESCRIPTION RESULT
---------------------------------- --- --------------------------------- -------
WORKER_TYPE DELETE 0 Delete test entries OK
WORKER_TYPE INSERT 1 Insert 3 test entries OK
WORKER_TYPE.GET_ID() 2 Get the ID for the specified code OK
WORKER_TYPE.GET_CODE_DESCR() 3 Get the code and description OK
for the specified ID
WORKER_TYPE.GET_CODE_ID_DESCR() 4 Get the code, ID, and OK
description for the specified code
WORKER_TYPE.GET_CODE_ID_DESCR() 5 Get the code, ID, and descr OK
iption for the specified date
WORKER_TYPE.HELP() 6 Display help OK
WORKER_TYPE DELETE 7 Delete test entries OK
WORKER_TYPE SUCCESS
9 rows selected.
If any of the tests failed, the enclosing PL/SQL block caught any raised exception, then the column result would list the word ERROR or the reported Oracle error message from any raised exception. If the enclosing PL/SQL block around a test failed to catch a raised exception, then the test unit itself would not complete, and the last entry in the report, SUCCESS, would not exist. Now that you’ve seen me do it, it’s your turn.
It’s Your Turn to Test a Code Table Package
I tested the WORKER_TYPE code table package, and you’ll test the GENDER_TYPE code table package. The two tests should be very similar. You can find a full-blown, production-ready version of package GENDER_TYPE in the downloaded code directory for Chapter 8. I suggest you proceed as follows.
- Copy the method WORKER_TYPE.test() and paste it into package body GENDER_TYPE.
- Modify the method for any column name and other changes.
- Save and compile the package body.
- Execute the test against GENDER_TYPE by executing method test().
- Use report last_test_results.sql to see the results of your test.
No, I’m not going to show you my solution because it follows the same pattern as any other code table package’s test() method. Instead, let’s move on to testing a content table’s package.
Testing a Content Table Package
This time, for an example of a content table test unit, I’m going to show you only a partial code listing. Listing 8-11 is the test unit from the full-blown, production-ready package for the LOGICAL_WORKPLACES table. Its package specification (not shown) lists the following methods that are specific to a content table package (or TYPE):
- get_name() or get_name_context(): Actually, in this case, since table LOGICAL_WORKPLACES has a hierarchal relationship with itself, the method is get_name_context(), which shows all the names in the hierarchy separated by periods (.).
- get_external_id() or get_code_context(): Once again, since table LOGICAL_WORKPLACES has a hierarchal relationship with itself, the method is named get_code_context(), which shows all the external IDs in the hierarchy separated by periods (.).
- get_row(): Returns a matching row depending on primary key column or unique key column value(s) set in the record passed to the method.
- set_row(): Updates an existing matching row, using the same rules implemented by get_row() to find a matching row, or inserts a new row if a match is not found.
The package specification has one method that is unique to a hierarchal table:
- create_id_context(): Creates a hierarchal unique key that represents the hierarchal relationship between rows in the same table as a character value separated by periods (.)
And it also has some methods that are universal to all table packages:
- get_id()Returns a newly allocated primary key value for the table
- help()help text for the package
- test()package