Listing 8-11. The test( ) Method from Table Package Body LOGICAL_WORKPLACE, rps.logical_workplace.pkb
264 PROCEDURE test is
265
266 /*
267 LOGICAL_WORKPLACES
268 Name Null? Type
269 ---------------------------------------- -------- -------------
270 LOGICAL_WORKPLACE_ID NOT NULL NUMBER
271 PARENT_ID NUMBER
272 ID_CONTEXT NOT NULL VARCHAR2(100)
273 WORKPLACE_TYPE_ID NOT NULL NUMBER
274 CODE NOT NULL VARCHAR2(30)
275 NAME NOT NULL VARCHAR2(80)
276 ACTIVE_DATE NOT NULL DATE
277 INACTIVE_DATE DATE
278 */
279
280 v_id_context
281 LOGICAL_WORKPLACES.id_context%TYPE;
282 r_logical_workplace LOGICAL_WORKPLACES%ROWTYPE;
283
284 begin
285 pl('LOGICAL_WORKPLACE.test()'),
286
287 &_USER..TEST.clear('LOGICAL_WORKPLACE'),
288
289 &_USER..TEST.set_test('LOGICAL_WORKPLACE', 'DELETE', 0,
290 'Delete existing test entries'),
291 begin
292 delete LOGICAL_WORKPLACES
293 where code in (
294 &_USER..TEST.v_TEST_30_1,
295 &_USER..TEST.v_TEST_30_2);
296
297 delete LOGICAL_WORKPLACES
298 where code = &_USER..TEST.v_TEST_30;
299
300 &_USER..TEST.ok();
301 exception
302 when OTHERS then
303 &_USER..TEST.error(SQLERRM);
304 end;
305
306 &_USER..TEST.set_test('LOGICAL_WORKPLACE', 'get_id()', 1,
307 'Allocate the next primary key value'),
308 begin
309 r_logical_workplace.id := get_id();
310
311 if r_logical_workplace.id > 0 then
312 &_USER..TEST.ok();
313 else
314 &_USER..TEST.error();
315 end if;
316 exception
317 when OTHERS then
318 &_USER..TEST.error(SQLERRM);
319 end;
320
321 &_USER..TEST.set_test('LOGICAL_WORKPLACE',
322 'create_id_context()', 2, 'Create an ID context value'),
323 begin
324 r_logical_workplace.parent_id := NULL;
325 r_logical_workplace.id_context :=
326 create_id_context(
327 r_logical_workplace.parent_id,
328 r_logical_workplace.id);
329
330 if r_logical_workplace.id_context =
331 to_char(r_logical_workplace.id) then
332 &_USER..TEST.ok();
333 else
334 &_USER..TEST.error();
335 end if;
336 exception
337 when OTHERS then
338 &_USER..TEST.error(SQLERRM);
339 end;
340
341 &_USER..TEST.set_test('LOGICAL_WORKPLACE', 'set_row()', 3,
342 'Insert parent test entry'),
343 begin
344 r_logical_workplace.workplace_type_id :=
345 WORKPLACE_TYPE.get_id('C'),
346 r_logical_workplace.code :=
347 &_USER..TEST.v_TEST_30;
348 r_logical_workplace.name :=
349 &_USER..TEST.v_TEST_80;
350 r_logical_workplace.active_date :=
351 &_USER..TEST.d_TEST_19000101;
352 r_logical_workplace.inactive_date :=
353 &_USER..TEST.d_TEST_99991231;
354 set_row(r_logical_workplace);
355
356 &_USER..TEST.ok();
357 exception
358 when OTHERS then
359 &_USER..TEST.error(SQLERRM);
360 end;
361
362 &_USER..TEST.set_test('LOGICAL_WORKPLACE', 'set_row()', 4,
363 'Insert child entries'),
364 begin
365 r_logical_workplace.parent_id :=
366 r_logical_workplace.id;
367 r_logical_workplace.id := get_id();
368 r_logical_workplace.id_context :=
369 create_id_context(
370 r_logical_workplace.parent_id,
371 r_logical_workplace.id);
372 -- save this value for testing get_row()
373 v_id_context :=
374 r_logical_workplace.id_context;
375 r_logical_workplace.workplace_type_id :=
376 WORKPLACE_TYPE.get_id('B'),
377 r_logical_workplace.code :=
378 &_USER..TEST.v_TEST_30_1;
379 r_logical_workplace.name :=
380 &_USER..TEST.v_TEST_80;
381 set_row(r_logical_workplace);
382
383 r_logical_workplace.id := get_id();
384 r_logical_workplace.id_context :=
385 create_id_context(
386 r_logical_workplace.parent_id,
387 r_logical_workplace.id);
388 r_logical_workplace.code :=
389 &_USER..TEST.v_TEST_30_2;
390 set_row(r_logical_workplace);
391
392 &_USER..TEST.ok();
393 exception
394 when OTHERS then
395 &_USER..TEST.error(SQLERRM);
396 end;
397
398 &_USER..TEST.set_test('LOGICAL_WORKPLACE', 'get_code_context()', 5,
399 'Get the code context for v_TEST_30_2'),
400 begin
401 pl(get_code_context(
402 r_logical_workplace.id));
403
404 &_USER..TEST.ok();
405 exception
406 when OTHERS then
407 &_USER..TEST.error(SQLERRM);
408 end;
409
410 &_USER..TEST.set_test('LOGICAL_WORKPLACE', 'get_name_context()', 6,
411 'Get the name context for v_TEST_30_2'),
412 begin
413 pl(get_name_context(
414 r_logical_workplace.id));
415
416 &_USER..TEST.ok();
417 exception
418 when OTHERS then
419 &_USER..TEST.error(SQLERRM);
420 end;
421
422 &_USER..TEST.set_test('LOGICAL_WORKPLACE', 'get_row()', 7,
423 'Get the row using the id for v_TEST_30_2'),
424 begin
425 -- r_logical_workplace.id := NULL;
426 r_logical_workplace.parent_id := NULL;
427 r_logical_workplace.id_context := NULL;
428 r_logical_workplace.workplace_type_id := NULL;
429 r_logical_workplace.code := NULL;
430 r_logical_workplace.name := NULL;
431 r_logical_workplace.active_date := NULL;
432 r_logical_workplace.inactive_date := NULL;
433
434 r_logical_workplace := get_row(r_logical_workplace);
435
436 if r_logical_workplace.id_context is not NULL then
437 &_USER..TEST.ok();
438 else
439 &_USER..TEST.error();
440 end if;
441 exception
442 when OTHERS then
443 &_USER..TEST.error(SQLERRM);
444 end;
445
446 &_USER..TEST.set_test('LOGICAL_WORKPLACE', 'get_row()', 8,
447 'Get the row using the id_context for v_TEST_30_1'),
448 begin
449 r_logical_workplace.id := NULL;
450 r_logical_workplace.parent_id := NULL;
451 r_logical_workplace.id_context :=
452 v_id_context;
453 r_logical_workplace.workplace_type_id := NULL;
454 r_logical_workplace.code := NULL;
455 r_logical_workplace.name := NULL;
456 r_logical_workplace.active_date := NULL;
457 r_logical_workplace.inactive_date := NULL;
458
459 r_logical_workplace := get_row(r_logical_workplace);
460
461 if r_logical_workplace.id is not NULL then
462 &_USER..TEST.ok();
463 else
464 &_USER..TEST.error();
465 end if;
466 exception
467 when OTHERS then
468 pl('v_id_context="'||v_id_context||'"'),
469 &_USER..TEST.error(SQLERRM);
470 end;
471
472 &_USER..TEST.set_test('LOGICAL_WORKPLACE', 'get_row()', 9,
473 'Get the row using the code for v_TEST_30'),
474 begin
475 r_logical_workplace.id := NULL;
476 r_logical_workplace.parent_id := NULL;
477 r_logical_workplace.id_context := NULL;
478 r_logical_workplace.workplace_type_id := NULL;
479 r_logical_workplace.code :=
480 &_USER..TEST.v_TEST_30;
481 r_logical_workplace.name :=
482 &_USER..TEST.v_TEST_80;
483 r_logical_workplace.active_date :=
484 &_USER..TEST.d_TEST_19000101;
485 r_logical_workplace.inactive_date := NULL;
486
487 r_logical_workplace := get_row(r_logical_workplace);
488
489 if r_logical_workplace.id is not NULL then
490 &_USER..TEST.ok();
491 else
492 &_USER..TEST.error();
493 end if;
494 exception
495 when OTHERS then
496 &_USER..TEST.error(SQLERRM);
497 end;
498
499 &_USER..TEST.set_test('LOGICAL_WORKPLACE', 'help()', 10,
500 'Display the help text'),
501 begin
502 help();
503
504 &_USER..TEST.ok();
505 exception
506 when OTHERS then
507 &_USER..TEST.error(SQLERRM);
508 end;
509
510 &_USER..TEST.set_test('LOGICAL_WORKPLACE', 'DELETE', 11,
511 'Delete test entries'),
512 begin
513 delete LOGICAL_WORKPLACES
514 where code in (
515 &_USER..TEST.v_TEST_30_1,
516 &_USER..TEST.v_TEST_30_2);
517
518 delete LOGICAL_WORKPLACES
519 where code = &_USER..TEST.v_TEST_30;
520
521 &_USER..TEST.ok();
522 exception
523 when OTHERS then
524 &_USER..TEST.error(SQLERRM);
525 end;
526
527 commit;
528 &_USER..TEST.set_test('LOGICAL_WORKPLACE', NULL, NULL, NULL);
529 &_USER..TEST.success();
530 end test;
The test unit for package LOGICAL_WORKPLACE is quite lengthy, so that’s why I’m using a partial listing in Listing 8-11. You can find the entire listing in the download directory for Chapter 8.
Let’s examine this content table package’s test unit line by line:
- Lines 280 through 282 declare two variables that I use during the test.
- Line 285 outputs some feedback so the tester knows the test has actually executed.
- Line 287 clears any previous test data.
- Lines 287 through 304 start the SQL Circle of Life for my test entries by pre-deleting any that may still exist.
- Lines 306 through 319 test method get_id().
- Lines 321 through 339 test method create_id_context().
- Lines 341 through 360 test method set_row() by inserting a parent company row based on TEST.v_TEST_30. This begins the INSERT portion of the SQL Circle of Life.
- Lines 362 through 396 test method set_row() again, this time inserting two child rows, business units, based on TEST.v_TEST_30_1 and TEST.v_TEST_30_2. This begins the INSERT or UPDATE portion of the SQL Circle of Life.
- Lines 398 through 408 test method get_code_context(). This starts the SELECT portion of the SQL Circle of Life.
- Lines 410 through 420 test method get_name_context().
- Lines 422 through 444 test method get_row() using the primary key column.
- Lines 446 through 470 test method get_row() a second time, using the unique id_context column.
- Lines 472 through 497 test method get_row() a third time, this time using unique columns code, name, and active_date.
- Lines 499 through 508 test method help().
- Lines 510 through 525 delete my test entries from the table. This brings me full circle on my SQL Circle of Life, from DELETE to INSERT to UPDATE to SELECT, and then back to DELETE.
- Finally, on lines 528 through 529, I record that the test unit executed successfully.
Did you notice that in this example, a content table package’s test unit used the full SQL Circle of Life, whereas the code table package used only a portion of it? And did you see that every test was blocked—executed in its own PL/SQL block in order to catch any raised exceptions, rather than abort the test unit? Now it’s your turn.
It’s Your Turn to Test a Content Table Package
Now you will create a test unit for content table package PHYSICAL_WORKPLACE. In case you haven’t noticed yet, table PHYSICAL_WORKPLACES is extremely similar to table LOGICAL_WORKPLACES, so you should be able to do some “code borrowing” to create a table package for PHYSICAL_WORKPLACES, including its test unit method test(). Please follow these steps.
- Write the DDL scripts to create a table package for table PHYSICAL_WORKPLACES by borrowing the code from LOGICAL_WORKPLACE. This means you’ll need to do some thinking about the differences between the two tables, and the resulting differences between the two packages, and then make the appropriate changes.
- Save your specification script as rps.physical_workplace.pks, and your body script as rp.physical_workplace.pkb.
- Compile the WORKER package because your solution may require it as a dependency: rps.worker.pks and rps.worker.pkb.
- Compile your package.
- Execute the test unit: SQL> execute physical_workplace.test();.
- Use script last_test_results.sql to view the outcome of your test unit.
- Add a test to your test unit to change the inactive date for business unit TEST_TS.v_TEST_CODE_30_2 to TEST_TS.d_TEST_19991231.
- Execute the test unit again: SQL> execute physical_workplace.test();.
- Use script last_test_results.sql to view the outcome of your test unit.
Once again, I’m not going to show you my solution. But you can find it in the solutions download directory for Chapter 8.
Next, let’s test an intersection table package.
Testing an Intersection Table Package
It’s time to test the third category of the dynamic trio: an intersection table package. As in the previous example, Listing 8-12 is a partial code listing, showing the test unit from the full-blown, production-ready package for the LOGICAL_ASSIGNMENTS table. In case you’ve forgotten, LOGICAL_ASSIGNMENTS holds a list (a history) of logical assignments for a worker, such as which department the person worked for from one point in time to another. Its package specification lists the following methods that are specific to an intersection table package (or TYPE):
- get_logical_workplace(): Returns the LOGICAL_WORKPLACES assigned at the specified point in time
- is_active(): Returns TRUE if the specified worker has a logical assignment at the specified point in time
The package specification contains several methods in common with a content table package:
- 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
And the package specification also has some methods that are universal to all table packages:
- get_id()Returns a newly allocated primary key value for the table
- help()Display help text for the package
- test()the package
Listing 8-12. The test( ) Method from Table Package Body LOGICAL_ASSIGNMENT,rps. logical_assignment.pkb
195 PROCEDURE test is
196
197 n_logical_workplace_id LOGICAL_WORKPLACES.id%TYPE;
198 n_logical_workplace_id_1 LOGICAL_WORKPLACES.id%TYPE;
199 n_logical_workplace_id_2 LOGICAL_WORKPLACES.id%TYPE;
200 n_worker_id WORKERS.id%TYPE;
201 n_worker_id_1 WORKERS.id%TYPE;
202 n_worker_id_2 WORKERS.id%TYPE;
203 r_worker WORKERS%ROWTYPE;
204 r_logical_workplace LOGICAL_WORKPLACES%ROWTYPE;
205 r_logical_assignment LOGICAL_ASSIGNMENTS%ROWTYPE;
206
207 begin
208 pl('LOGICAL_ASSIGNMENT.test()'),
209
210 &_USER..TEST.clear('LOGICAL_ASSIGNMENT'),
211
212 -- In order to make entries into an Intersection table
213 -- you first have to have entries in the two tables
214 -- for which an entry will create an intersection
215 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'DELETE', 0,
216 'Delete existing test entries from LOGICAL_ASSIGNMENTS'),
217 begin
218 delete LOGICAL_ASSIGNMENTS
219 where logical_workplace_id in (
220 select logical_workplace_id
221 from LOGICAL_WORKPLACES
222 where code in (
223 &_USER..TEST.v_TEST_30,
224 &_USER..TEST.v_TEST_30_1,
225 &_USER..TEST.v_TEST_30_2 ) );
226
227 delete LOGICAL_ASSIGNMENTS
228 where worker_id in (
229 select worker_id
230 from WORKERS
231 where external_id in (
232 &_USER..TEST.v_TEST_30,
233 &_USER..TEST.v_TEST_30_1,
234 &_USER..TEST.v_TEST_30_2 ) );
235
236 &_USER..TEST.ok();
237 exception
238 when OTHERS then
239 &_USER..TEST.error(SQLERRM);
240 end;
241
242 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'DELETE', 1,
243 'Delete existing test entries from LOGICAL_WORKPLACES'),
244 begin
245 delete LOGICAL_WORKPLACES
246 where code in (
247 &_USER..TEST.v_TEST_30_1,
248 &_USER..TEST.v_TEST_30_2 );
249
250 delete LOGICAL_WORKPLACES
251 where code in (
252 &_USER..TEST.v_TEST_30 );
253
254 &_USER..TEST.ok;
255 exception
256 when OTHERS then
257 &_USER..TEST.error(SQLERRM);
258 end;
259
260 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'DELETE', 2,
261 'Delete existing test entries from WORKERS'),
262 begin
263 delete WORKERS
264 where external_id in (
265 &_USER..TEST.v_TEST_30,
266 &_USER..TEST.v_TEST_30_1,
267 &_USER..TEST.v_TEST_30_2 );
268
269 &_USER..TEST.ok();
270 exception
271 when OTHERS then
272 &_USER..TEST.error(SQLERRM);
273 end;
274
275 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'INSERT', 3,
276 'Insert WORKERS test entries using set_row()'),
277 begin
278 r_worker.id := WORKER.get_id();
279 r_worker.worker_type_id := WORKER_TYPE.get_id('E'),
280 r_worker.external_id := &_USER..TEST.v_TEST_30;
281 r_worker.first_name := &_USER..TEST.v_TEST_30;
282 r_worker.middle_name := &_USER..TEST.v_TEST_30;
283 r_worker.last_name := &_USER..TEST.v_TEST_30;
284 r_worker.name := WORKER.get_formatted_name(
285 r_worker.first_name,
286 r_worker.middle_name,
287 r_worker.last_name);
288 r_worker.birth_date := to_date('19800101', 'YYYYMMDD'),
289 r_worker.gender_type_id := GENDER_TYPE.get_id('M'),
290 WORKER.set_row(r_worker);
291 n_worker_id := r_worker.id;
292
293 r_worker.id := WORKER.get_id();
294 r_worker.worker_type_id := WORKER_TYPE.get_id('E'),
295 r_worker.external_id := &_USER..TEST.v_TEST_30_1;
296 r_worker.first_name := &_USER..TEST.v_TEST_30_1;
297 r_worker.middle_name := &_USER..TEST.v_TEST_30_1;
298 r_worker.last_name := &_USER..TEST.v_TEST_30_1;
299 r_worker.name := WORKER.get_formatted_name(
300 r_worker.first_name,
301 r_worker.middle_name,
302 r_worker.last_name);
303 r_worker.birth_date := to_date('19700101', 'YYYYMMDD'),
304 r_worker.gender_type_id := GENDER_TYPE.get_id('F'),
305 WORKER.set_row(r_worker);
306 n_worker_id_1 := r_worker.id;
307
308 r_worker.id := WORKER.get_id();
309 r_worker.worker_type_id := WORKER_TYPE.get_id('C'),
310 r_worker.external_id := &_USER..TEST.v_TEST_30_2;
311 r_worker.first_name := &_USER..TEST.v_TEST_30_2;
312 r_worker.middle_name := &_USER..TEST.v_TEST_30_2;
313 r_worker.last_name := &_USER..TEST.v_TEST_30_2;
314 r_worker.name := WORKER.get_formatted_name(
315 r_worker.first_name,
316 r_worker.middle_name,
317 r_worker.last_name);
318 r_worker.birth_date := to_date('19600101', 'YYYYMMDD'),
319 r_worker.gender_type_id := GENDER_TYPE.get_id('M'),
320 WORKER.set_row(r_worker);
321 n_worker_id_2 := r_worker.id;
322
323 &_USER..TEST.ok();
324 exception
325 when OTHERS then
326 &_USER..TEST.error(SQLERRM);
327 end;
328
329 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'INSERT', 4,
330 'Insert LOGICAL_WORKPLACES test entries using set_row()'),
331 begin
332 r_logical_workplace.id :=
333 LOGICAL_WORKPLACESS.get_id();
334 r_logical_workplace.parent_id := NULL;
335 r_logical_workplace.id_context :=
336 LOGICAL_WORKPLACESS.create_id_context(
337 r_logical_workplace.parent_id,
338 r_logical_workplace.id);
339 r_logical_workplace.workplace_type_id :=
340 WORKPLACE_TYPE_TS.get_id('C'),
341 r_logical_workplace.code := &_USER..TEST.v_TEST_30;
342 r_logical_workplace.name := &_USER..TEST.v_TEST_80;
343 r_logical_workplace.active_date := &_USER..TEST.d_TEST_19000101;
344 r_logical_workplace.inactive_date := &_USER..TEST.d_TEST_99991231;
345 LOGICAL_WORKPLACESS.set_row(r_logical_workplace);
346 n_logical_workplace_id :=
347 r_logical_workplace.id;
348
349 r_logical_workplace.id :=
350 LOGICAL_WORKPLACESS.get_id();
351 r_logical_workplace.parent_id :=
352 n_logical_workplace_id;
353 r_logical_workplace.id_context :=
354 LOGICAL_WORKPLACESS.create_id_context(
355 r_logical_workplace.parent_id,
356 r_logical_workplace.id);
357 r_logical_workplace.workplace_type_id :=
358 WORKPLACE_TYPE_TS.get_id('B'),
359 r_logical_workplace.code := &_USER..TEST.v_TEST_30_1;
360 r_logical_workplace.name := &_USER..TEST.v_TEST_80;
361 r_logical_workplace.active_date := &_USER..TEST.d_TEST_19000101;
362 r_logical_workplace.inactive_date := &_USER..TEST.d_TEST_99991231;
363 LOGICAL_WORKPLACESS.set_row(r_logical_workplace);
364 n_logical_workplace_id_1 :=
365 r_logical_workplace.id;
366
367 r_logical_workplace.id :=
368 LOGICAL_WORKPLACESS.get_id();
369 r_logical_workplace.parent_id :=
370 n_logical_workplace_id;
371 r_logical_workplace.id_context :=
372 LOGICAL_WORKPLACESS.create_id_context(
373 r_logical_workplace.parent_id,
374 r_logical_workplace.id);
375 r_logical_workplace.workplace_type_id :=
376 WORKPLACE_TYPE_TS.get_id('B'),
377 r_logical_workplace.code := &_USER..TEST.v_TEST_30_2;
378 r_logical_workplace.name := &_USER..TEST.v_TEST_80;
379 r_logical_workplace.active_date := &_USER..TEST.d_TEST_19000101;
380 r_logical_workplace.inactive_date := &_USER..TEST.d_TEST_99991231;
381 LOGICAL_WORKPLACESS.set_row(r_logical_workplace);
382 n_logical_workplace_id_2 :=
383 r_logical_workplace.id;
384
385 &_USER..TEST.ok();
386 exception
387 when OTHERS then
388 &_USER..TEST.error(SQLERRM);
389 end;
390
391 -- Now that I have entries in the two tables being intersected
392 -- I can now start testing this package...
393 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'get_id()', 5,
394 'Allocate the next primary key value using get_id()'),
395 begin
396 r_logical_assignment.id :=
397 LOGICAL_ASSIGNMENT.get_id();
398
399 if nvl(r_logical_assignment.id, 0) > 0 then
400 &_USER..TEST.ok();
401 else
402 &_USER..TEST.error();
403 end if;
404 exception
405 when OTHERS then
406 &_USER..TEST.error(SQLERRM);
407 end;
408
409 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'set_row()', 6,
410 'Insert history for v_TEST_30 using set_row()'),
411 begin
412 r_logical_assignment.worker_id := n_worker_id;
413 r_logical_assignment.logical_workplace_id :=
414 n_logical_workplace_id_2;
415 r_logical_assignment.active_date :=
416 to_date('20000101', 'YYYYMMDD'),
417 r_logical_assignment.inactive_date := NULL;
418 LOGICAL_ASSIGNMENT.set_row(r_logical_assignment);
419
420 &_USER..TEST.ok;
421 exception
422 when OTHERS then
423 &_USER..TEST.error(SQLERRM);
424 end;
425
426 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'set_row()', 7,
427 'Insert history for V_TEST_30_1 using set_row()'),
428 begin
429 r_logical_assignment.id :=
430 LOGICAL_ASSIGNMENT.get_id();
431 r_logical_assignment.worker_id := n_worker_id_1;
432 r_logical_assignment.logical_workplace_id :=
433 n_logical_workplace_id_1;
434 r_logical_assignment.active_date :=
435 to_date('19900101', 'YYYYMMDD'),
436 r_logical_assignment.inactive_date :=
437 to_date('19991231', 'YYYYMMDD'),
438 LOGICAL_ASSIGNMENT.set_row(r_logical_assignment);
439
440 r_logical_assignment.id :=
441 LOGICAL_ASSIGNMENT.get_id();
442 r_logical_assignment.worker_id := n_worker_id_1;
443 r_logical_assignment.logical_workplace_id :=
444 n_logical_workplace_id_2;
445 r_logical_assignment.active_date :=
446 to_date('20000101', 'YYYYMMDD'),
447 r_logical_assignment.inactive_date := NULL;
448 LOGICAL_ASSIGNMENT.set_row(r_logical_assignment);
449
450 &_USER..TEST.ok;
451 exception
452 when OTHERS then
453 &_USER..TEST.error(SQLERRM);
454 end;
455
456 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'set_row()', 8,
457 'Insert history for V_TEST_30_2 using set_row()'),
458 begin
459 r_logical_assignment.id :=
460 LOGICAL_ASSIGNMENT.get_id();
461 r_logical_assignment.worker_id := n_worker_id_2;
462 r_logical_assignment.logical_workplace_id :=
463 n_logical_workplace_id_1;
464 r_logical_assignment.active_date :=
465 to_date('19800101', 'YYYYMMDD'),
466 r_logical_assignment.inactive_date :=
467 to_date('19891231', 'YYYYMMDD'),
468 LOGICAL_ASSIGNMENT.set_row(r_logical_assignment);
469
470 r_logical_assignment.id :=
471 LOGICAL_ASSIGNMENT.get_id();
472 r_logical_assignment.worker_id := n_worker_id_2;
473 r_logical_assignment.logical_workplace_id :=
474 n_logical_workplace_id_2;
475 r_logical_assignment.active_date :=
476 to_date('19900101', 'YYYYMMDD'),
477 r_logical_assignment.inactive_date :=
478 to_date('19901231', 'YYYYMMDD'),
479 LOGICAL_ASSIGNMENT.set_row(r_logical_assignment);
480
481 &_USER..TEST.ok;
482 exception
483 when OTHERS then
484 &_USER..TEST.error(SQLERRM);
485 end;
486
487 -- Commit the deletes and inserts
488 commit;
489
490 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT',
491 'get_logical_workplace()', 9,
492 'Get the current logical workplace for v_TEST_30'),
493 begin
494 r_logical_workplace := NULL;
495 r_logical_workplace := get_logical_workplace(n_worker_id);
496
497 if nvl(r_logical_workplace.id, 0) > 0 then
498 &_USER..TEST.ok();
499 else
500 &_USER..TEST.error();
501 end if;
502 exception
503 when OTHERS then
504 &_USER..TEST.error(SQLERRM);
505 end;
506
507 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT',
508 'get_logical_workplace()', 10,
509 'Get the logical workplace on 6/30/1995 for v_TEST_30_1'),
510 begin
511 r_logical_workplace := NULL;
512 r_logical_workplace := get_logical_workplace(
513 n_worker_id_1,
514 to_date('19950630', 'YYYYMMDD'));
515
516 if nvl(r_logical_workplace.id, 0) > 0 then
517 &_USER..TEST.ok();
518 else
519 &_USER..TEST.error();
520 end if;
521 exception
522 when OTHERS then
523 &_USER..TEST.error(SQLERRM);
524 end;
525
526 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT',
527 'get_logical_workplace()', 11,
528 'Get the logical workplace on 6/30/1995 for v_TEST_30_2'),
529 begin
530 -- this should fail
531 r_logical_workplace := NULL;
532 r_logical_workplace := get_logical_workplace(
533 n_worker_id_2,
534 to_date('19950630', 'YYYYMMDD'));
535
536 if nvl(r_logical_workplace.id, 0) > 0 then
537 &_USER..TEST.error();
538 else
539 &_USER..TEST.ok();
540 end if;
541 exception
542 when NO_DATA_FOUND then
543 &_USER..TEST.ok();
544 when OTHERS then
545 &_USER..TEST.error(SQLERRM);
546 end;
547
548 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'help()', 12,
549 'Test help()'),
550 begin
551 help();
552
553 &_USER..TEST.ok;
554 exception
555 when OTHERS then
556 &_USER..TEST.error(SQLERRM);
557 end;
558
559 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'is_active()', 13,
560 'Is there an active assignment on 6/30/1995 for v_TEST_30?'),
561 -- No
562 begin
563 if is_active(n_worker_id, to_date('19950630', 'YYYYMMDD')) then
564 &_USER..TEST.error();
565 else
566 &_USER..TEST.ok();
567 end if;
568 exception
569 when OTHERS then
570 &_USER..TEST.error(SQLERRM);
571 end;
572
573 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'is_active()', 14,
574 'Is there an active assignment on 6/30/1995 for v_TEST_30_1?'),
575 -- Yes
576 begin
577 if is_active(n_worker_id_1, to_date('19950630', 'YYYYMMDD')) then
578 &_USER..TEST.ok();
579 else
580 &_USER..TEST.error();
581 end if;
582 exception
583 when OTHERS then
584 &_USER..TEST.error(SQLERRM);
585 end;
586
587 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'is_active()', 15,
588 'Is there currently an active assignment for v_TEST_30_2?'),
589 -- No
590 begin
591 if is_active(n_worker_id_2) then
592 &_USER..TEST.error();
593 else
594 &_USER..TEST.ok();
595 end if;
596 exception
597 when OTHERS then
598 &_USER..TEST.error(SQLERRM);
599 end;
600
601 -- Now clean up after the tests by deleting the test entries
602 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'DELETE', 16,
603 'Delete existing test entries from LOGICAL_ASSIGNMENTS'),
604 begin
605 delete LOGICAL_ASSIGNMENTS
606 where logical_workplace_id in (
607 select logical_workplace_id
608 from LOGICAL_WORKPLACES
609 where code in (
610 &_USER..TEST.v_TEST_30,
611 &_USER..TEST.v_TEST_30_1,
612 &_USER..TEST.v_TEST_30_2 ) );
613
614 delete LOGICAL_ASSIGNMENTS
615 where worker_id in (
616 select worker_id
617 from WORKER_T
618 where external_id in (
619 &_USER..TEST.v_TEST_30,
620 &_USER..TEST.v_TEST_30_1,
621 &_USER..TEST.v_TEST_30_2 ) );
622
623 &_USER..TEST.ok();
624 exception
625 when OTHERS then
626 &_USER..TEST.error(SQLERRM);
627 end;
628
629 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'DELETE', 17,
630 'Delete existing test entries from LOGICAL_WORKPLACES'),
631 begin
632 delete LOGICAL_WORKPLACES
633 where code in (
634 &_USER..TEST.v_TEST_30_1,
635 &_USER..TEST.v_TEST_30_2 );
636
637 delete LOGICAL_WORKPLACES
638 where code in (
639 &_USER..TEST.v_TEST_30 );
640
641 &_USER..TEST.ok;
642 exception
643 when OTHERS then
644 &_USER..TEST.error(SQLERRM);
645 end;
646
647 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', 'DELETE', 18,
648 'Delete existing test entries from WORKERS'),
649 begin
650 delete WORKERS
651 where external_id in (
652 &_USER..TEST.v_TEST_30,
653 &_USER..TEST.v_TEST_30_1,
654 &_USER..TEST.v_TEST_30_2 );
655
656 &_USER..TEST.ok();
657 exception
658 when OTHERS then
659 &_USER..TEST.error(SQLERRM);
660 end;
661
662 commit;
663 &_USER..TEST.set_test('LOGICAL_ASSIGNMENT', NULL, NULL, NULL);
664 &_USER..TEST.success();
665 end test;
The important point about an intersection table package is that it is dependent on test entries in the two tables it intersects. So you’ll see that on lines 215 through 392, I must start out by deleting any test entries in the “intersected” tables, WORKERS and LOGICAL_WORKPLACES, and then I must add entries to these tables in order to have entries from which to establish an intersection entry. So every intersection table package must also insert and delete entries from the tables on which it is depends. Now it’s your turn to work on an intersection table package.
It’s Your Turn to Test an Intersection Table Package
You will now create a test unit for intersection table package PHYSICAL_ASSIGNMENT. Just as in the last exercise, the corresponding table for this exercise, PHYSICAL_ASSIGNMENTS, is extremely similar to table LOGICAL_ASSIGNMENTS, so you should once again be able to do some “code borrowing” to create a table package for PHYSICAL_ASSIGNMENTS, including its test unit method test(). Follow these steps.
- Write the DDL scripts to create a table package for table PHYSICAL_ASSIGNMENTS by borrowing the code from LOGICAL_ASSIGNMENT.
- Save your specification script as rps.physical_assignment.pks, and your body script as rps.physical_assignment.pkb.
- Compile your package.
- Execute the test unit: SQL> execute physical_assignment.test();.
- Use script last_test_results.sql to view the outcome of your test unit.
Once again, I’m not going to show you my solution. But you can find it in the solutions download directory for Chapter 8.
So just how different can it be to test a TYPE instead of a package? Let’s find out.
Testing a Type
Testing a TYPE is not all that different from testing a package. The only significant difference is that when you declare the test() method in a TYPE, it must be declared as a STATIC method. And when you code the method, you must remember that you’ll need to work with a variable of that TYPE, and instance, as well as the TYPE when performing your testing. If you don’t have an instance of the TYPE, you won’t be able to access and test all its methods.
Listing 8-13 is a partial listing of the TYPE TEST, an object-relational testing tool. This listing shows only the test unit for the TYPE itself.
Listing 8-13. The Test Unit from TYPE TEST, ops.test.tpb
416 STATIC PROCEDURE "test" is
417
418 n_number number;
419 o_test TEST;
420
421 begin
422 pl('TEST.test()'),
423
424 -- A defect requires the schema owner
425 &_USER..TEST.clear('TEST'),
426
427 o_test := new TEST();
428 o_test.set_test('TEST', NULL, 1,
429 'Is get_test_N equal to N?'),
430 if TEST.get_test_N = 'N' then
431 o_test.success();
432 else
433 o_test.error();
434 end if;
435
436 o_test.set_test('TEST', NULL, 2,
437 'Is the length of get_test_N equal to 1?'),
438 if nvl(length(TEST.get_test_N), 0) = 1 then
439 o_test.success();
440 else
441 o_test.error();
442 end if;
443
444 o_test.set_test('TEST', NULL, 3,
445 'Is get_test_Y equal to Y?'),
446 if TEST.get_test_Y = 'Y' then
447 o_test.success();
448 else
449 o_test.error();
450 end if;
451
452 o_test.set_test('TEST', NULL, 4,
453 'Is the length of get_test_Y equal to 1?'),
454 if nvl(length(TEST.get_test_Y), 0) = 1 then
455 o_test.success();
456 else
457 o_test.error();
458 end if;
459
460 o_test.set_test('TEST', NULL, 5,
461 'Is the length of get_test_30 equal to 30?'),
462 if nvl(length(TEST.get_test_30), 0) = 30 then
463 o_test.success();
464 else
465 o_test.error();
466 end if;
467
468 o_test.set_test('TEST', NULL, 6,
469 'Is the length of get_test_30_1 equal to 30?'),
470 if nvl(length(TEST.get_test_30_1), 0) = 30 then
471 o_test.success();
472 else
473 o_test.error();
474 end if;
475
476 o_test.set_test('TEST', NULL, 7,
477 'Is the length of get_test_30_2 equal to 30?'),
478 if nvl(length(TEST.get_test_30_2), 0) = 30 then
479 o_test.success();
480 else
481 o_test.error();
482 end if;
483
484 o_test.set_test('TEST', NULL, 8,
485 'Is the length of get_test_80 equal to 80?'),
486 if nvl(length(TEST.get_test_80), 0) = 80 then
487 o_test.success();
488 else
489 o_test.error();
490 end if;
491
492 o_test.set_test('TEST', NULL, 9,
493 'Is the length of get_test_100 equal to 100?'),
494 if nvl(length(TEST.get_test_100), 0) = 100 then
495 o_test.success();
496 else
497 o_test.error();
498 end if;
499
500 o_test.set_test('TEST', NULL, 10,
501 'Is the length of get_test_2000 equal to 2000?'),
502 if nvl(length(TEST.get_test_2000), 0) = 2000 then
503 o_test.success();
504 else
505 o_test.error();
506 end if;
507
508 o_test.set_test('TEST', 'get_id', 11,
509 'Does get_id() work?'),
510 begin
511 n_number := TEST.get_id();
512 if n_number > 0 then
513 o_test.success();
514 else
515 o_test.error();
516 end if;
517 exception
518 when OTHERS then
519 o_test.error(SQLERRM);
520 end;
521
522 o_test.set_test('TEST', 'help', 12,
523 'Does help() work?'),
524 begin
525 &_USER..TEST.help();
526 raise_application_error(-20999, 'Testing the error routine'),
527 o_test.success();
528 exception
529 when OTHERS then
530 o_test.error(SQLERRM);
531 end;
532
533 o_test.set_test('TEST', NULL, NULL, NULL);
534 o_test.success();
535 end "test";...
First, notice that I’ve declared the method as a STATIC procedure. This means it will be accessible from the TYPE itself, not through an instance of the type.
Second, on line 419, I declare a variable of the TYPE and then on line 427, I set the variable to a new instance of the TYPE. Then I use this instance when calling MEMBER methods in the test unit. Beyond those two major changes, the differences in methods test() in package TEST and TYPE TEST are only semantic (as in the variations in the programmer-created language). One last detail! I had to test the method “test” in order to get around a name space error. I could have named it test_ or TEST_ or anything other than the name of the TYPE itself, but I like to keep things obvious, so I forced Oracle to name it test in lower case.
Now that you have seen an example of testing a TYPE, it’s your turn.
It’s Your Turn to Test a Type
Now you’ll select any TYPE that you’ve created so far, and code its test unit method test(). However, you’ll use your object-relational testing tool. This means you’ll also need to modify the SQL*Plus report last_test_results.sql so it queries your testing tool’s object table TESTS. This time, I’m not even going to give you any steps. You’ve seen enough examples that you should be able to list the steps you need to complete in order to do this exercise. Good skill!
Now, it’s time we move on to the automation of testing.
Automating Testing
Do you remember that in the beginning of this chapter I promised you a framework for automating your testing? Well, by utilizing information from Oracle’s data dictionary and writing another program unit that calls all your test units, you can execute one PL/SQL method that will in turn execute every test() method you’ve written, and then report on the test results.
There are several data dictionary views that have information about packages, TYPEs, and their methods. The three best suited to my needs of identifying every package and TYPE that does and does not have a test() method are SYS.ALL_ARGUMENTS, SYS.USER_PROCDURES, and SYS.USER_TYPE_METHODS. The following are SQL*Plus descriptions of the views:
SYS.USER_ARGUMENTS
Name Null? Type
---------------------------------------- -------- ---------------------------
OBJECT_NAME VARCHAR2(128)
PACKAGE_NAME VARCHAR2(128)
OBJECT_ID NOT NULL NUMBER
OVERLOAD VARCHAR2(40)
SUBPROGRAM_ID NUMBER
ARGUMENT_NAME VARCHAR2(128)
...
SYS.USER_TYPE_METHODS
Name Null? Type
---------------------------------------- -------- ---------------------------
TYPE_NAME NOT NULL VARCHAR2(128)
METHOD_NAME NOT NULL VARCHAR2(128)
METHOD_NO NOT NULL NUMBER
METHOD_TYPE VARCHAR2(6)
PARAMETERS NOT NULL NUMBER
...
SYS.USER_PROCEDURES
Name Null? Type
---------------------------------------- -------- -------------
OBJECT_NAME VARCHAR2(128)
PROCEDURE_NAME VARCHAR2(128)
OBJECT_ID NUMBER
SUBPROGRAM_ID NUMBER
OVERLOAD VARCHAR2(40)
OBJECT_TYPE VARCHAR2(13)
...
Using the information provided by these views, I can write a query that does the following:
- Lists the name of every package and TYPE that has a test() method
- Lists the name of every package and TYPE that does not have a test() method
If I couple that information with the output of the test units that is stored in table TESTS, I can write a query that does the following:
- Lists information about every test that failed
- Lists the name of every package or TYPE where its test method failed
So the information provided by the data dictionary views and the testing tool’s table TESTS arms me with all the information I need to automate the testing of the PL/SQL program units and to report on the test results. Therefore, to automate the testing of the PL/SQL program units, all I need to do is follow these two simple steps.
- Add a test unit method test() to every PL/SQL package or TYPE definition.
- Execute a process to execute each test unit method and report the results.
You already know how to do the first step, so let’s see an example of how to do the second step.
Automate Test Processing
Listing 8-14 is an example of a PL/SQL program unit that executes all coded test() methods for the current schema, and then reports on the results. It queries view SYS.ALL_ARGUMENTS to get a list of packages and TYPEs that do and do not have test() methods. It executes each test() method, recording the results of execution, as the test method itself records the results of each of its tests. Finally, it reports the results of the tests.
Listing 8-14. An Example of a Test Unit Processing, test.sql
001 rem test.sql
002 rem by Donald J. Bales on 2014-10-20
003 rem An anonymous PL/SQL procedure to execute all test units
004 rem and to report on the results of each test.
005
006 declare
007
008 -- Get the names of all packages and types that have a test unit
009 cursor c_test is
010 select p.object_name package_name
011 from SYS.USER_PROCEDURES p
012 where p.object_type = 'PACKAGE'
013 and upper(p.procedure_name) = 'TEST'
014 and not exists (
015 select 1
016 from SYS.USER_ARGUMENTS x
017 where x.package_name = p.object_name
018 and x.object_name = p.procedure_name)
019 UNION
020 select m.type_name package_name
021 from SYS.USER_TYPE_METHODS m
022 where upper(m.method_name) = 'TEST'
023 and m.parameters = 0
024 order by 1;
025
026 -- Get the names of all packages and types that don't have a test unit
027 cursor c_missing is
028 (select p.object_name package_name
029 from SYS.USER_PROCEDURES p
030 where p.object_type = 'PACKAGE'
031 UNION
032 select m.type_name package_name
033 from SYS.USER_TYPE_METHODS m )
034 MINUS
035 (select p.object_name package_name
036 from SYS.USER_PROCEDURES p
037 where upper(p.procedure_name) = 'TEST'
038 and not exists (
039 select 1
040 from SYS.USER_ARGUMENTS x
041 where x.package_name = p.object_name
042 and x.object_name = p.procedure_name)
043 UNION
044 select m.type_name package_name
045 from SYS.USER_TYPE_METHODS m
046 where upper(m.method_name) = 'TEST'
047 and m.parameters = 0)
048 order by 1;
049
050 -- Get the names of all packages and types that have test unit errors
051 cursor c_error is
052 select object_name||
053 decode(substr(method_name, -1, 1), ')', '.', ' ')||
054 method_name object_method,
055 test_number,
056 result
057 from TESTS
058 where result <> 'OK'
059 and result <> 'SUCCESS'
060 order by 1;
061
062 TYPE error_message_table is table of varchar2(32767)
063 index by binary_integer;
064
065 n_error_message number := 0;
066 n_object_method_width number := 39;
067 n_result_width number := 29;
068 n_status number;
069 n_test_number_width number := 5;
070
071 t_error_message error_message_table;
072
073 v_line varchar2(32767);
074
075 begin
076 -- execute the test units
077 for r_test in c_test loop
078 begin
079 execute immediate 'begin '||r_test.package_name||'.test(); end;';
080 exception
081 when OTHERS then
082 n_error_message := n_error_message + 1;
083 t_error_message(n_error_message) :=
084 r_test.package_name||'.test() '||SQLERRM;
085 end;
086 end loop;
087 -- Empty the output buffer
088 loop
089 SYS.DBMS_OUTPUT.get_line(v_line, n_status);
090 if nvl(n_status, 0) < 1 then
091 exit;
092 end if;
093 end loop;
094 -- Show the test units that had errors
095 for r_error in c_error loop
096 if c_error%rowcount = 1 then
097 pl(chr(9));
098 pl('THE FOLLOWING OBJECT''S TEST UNITS HAD ERRORS:'),
099 pl(chr(9));
100 pl(
101 rpad(
102 substr('OBJECT/METHOD',
103 1, n_object_method_width),
104 n_object_method_width, ' ')||
105 ' '||
106 lpad(
107 substr('TEST#',
108 1, n_test_number_width),
109 n_test_number_width, ' ')||
110 ' '||
111 rpad(
112 substr('ERROR',
113 1, n_result_width),
114 n_result_width, ' ')
115 );
116 pl(
117 rpad(
118 substr('-------------',
119 1, n_object_method_width),
120 n_object_method_width, '-')||
121 ' '||
122 lpad(
123 substr('-----',
124 1, n_test_number_width),
125 n_test_number_width, '-')||
126 ' '||
127 rpad(
128 substr('-----',
129 1, n_result_width),
130 n_result_width, '-')
131 );
132 end if;
133 pl(
134 rpad(
135 substr(r_error.object_method,
136 1, n_object_method_width),
137 n_object_method_width, ' ')||
138 ' '||
139 lpad(
140 substr(ltrim(to_char(r_error.test_number)),
141 1, n_test_number_width),
142 n_test_number_width, ' ')||
143 ' '||
144 rpad(
145 substr(r_error.result,
146 1, n_result_width),
147 n_result_width, ' ')
148 );
149 end loop;
150 -- Show the test units that failed to run
151 if t_error_message.count > 0 then
152 for i in t_error_message.first..t_error_message.last loop
153 if i = t_error_message.first then
154 pl(chr(9));
155 pl('THE FOLLOWING OBJECT''S TEST UNITS FAILED:'),
156 end if;
157 pl(chr(9));
158 pl(t_error_message(i));
159 end loop;
160 end if;
161 -- Show the object that missing test units
162 for r_missing in c_missing loop
163 if c_missing%rowcount = 1 then
164 pl(chr(9));
165 pl('THE FOLLOWING OBJECTS ARE MISSING TEST UNITS:'),
166 pl(chr(9));
167 end if;
168 pl(r_missing.package_name);
169 end loop;
170 end;
171 /
Let’s break this listing down line by line:
- Lines 9 through 24 declare a cursor, c_test, against data dictionary views SYS.USER_ARGUMENTS, SYS.USER_PROCEDURES, and SYS.USER_TYPE_METHODS, which gives me a list of packages and TYPEs that have a method test().
- Lines 27 through 48 declare second cursor, c_missing, against SYS.USER_ARGUMENTS, SYS.USER_PROCEDURES, and SYS.USER_TYPE_METHODSwhich gives me a list of packages and TYPEs that do not have a test() method.
- Lines 51 through 60 declare a cursor, c_error, against table TESTS that lists all the individual tests that failed during the current test run.
- Lines 62 and 63 declare a PL/SQL table TYPE, error_message_table, which I will use to declare an array of exceptions raised during the execution of each test() method.
- Lines 77 through 86 execute a cursor for loop, where I call each package and TYPE’s test() method. I’ve blocked the dynamic call to each method, so I can capture and later report on any raised exceptions (test() method failures).
- Lines 88 through 93 use a manual for loop to empty the SYS.DBMS_OUTPUT.put_line() buffer after having executed every test unit method. I do this to throw out any put_line() messages generated by each test unit.
- Lines 95 through 149 report, using put_line(), about any test failures during the test run.
- Lines 151 through 160 report any test() method failures during the test run. I could have created a fourth cursor to show me the test() methods that failed during execution, but I’m already getting this information from the blocked dynamic PL/SQL call to each method on lines 77 through 86.
- Lines 162 through 169 list any packages or TYPEs that do not have a test unit method test().
The only thing this procedure cannot do is determine whether or not a programmer has actually taken the time to code the test unit. The following is sample output from this procedure while I was coding in my environment:
SQL> @test.sql
THE FOLLOWING OBJECT'S TEST UNITS HAD ERRORS:
OBJECT/METHOD TEST# ERROR
--------------------------------------- ----- -----------------------------
LOGICAL_WORKPLACE.GET_ROW() 7 ORA-01403: no data found
LOGICAL_WORKPLACE.GET_ROW() 8 ORA-01403: no data found
LOGICAL_WORKPLACE.GET_ROW() 9 ORA-01403: no data found
LOGICAL_WORKPLACE.SET_ROW() 4 ORA-01403: no data found
LOGICAL_WORKPLACE.SET_ROW() 3 ORA-20003: ORA-01400: cannot
THE FOLLOWING OBJECTS ARE MISSING TEST UNITS:
DEBUG
PARAMETER
SCOPE
PL/SQL procedure successfully completed.
The first part of the report, test unit errors, shows that exception ORA-20003 was raised while testing method LOGICAL_WORKPLACE.SET_ROW(). This information tells me to go directly to package LOGICAL_WORKPLACE to examine what might be wrong with the test or the method set_row().
The second part of the report, missing test units, lists the packages and TYPEs that are missing a test unit method. With this information, I can add the task of coding test units for these to my to-do list. Don’t ignore this list. Anything not tested is something that can and probably will produce errors.
In practice, on an enterprise application, I’ve seen a test unit process like this take two to three hours to execute. In addition, it always finds one to three errors in each newly coded package. As time goes by, and new defects are detected by end users and others, it’s your responsibility to go back and update the test unit method to test for any newly reported defect. Using this kind of automated testing, combined with consistently adding any new test cases, you can make your persistence layer statistically error-free.
I don’t think that the “processing” procedure just described should be external (losable) to the database/objectbase. Instead, it should be added to package TEST as method process(). That way, you will be able to test any package or procedure by calling its test() method, and you can test the entire persistence layer by calling TEST.process(). Now that’s a powerful testing tool!
It’s Your Turn to Automate Test Processing
For your final assignment in this chapter, modify your object-relational testing tool to add STATIC procedure process(), which will execute a program unit similar to test.sql using your testing tool’s result table TESTS. Then execute TEST.process() to see the results of testing your object-relational persistence layer.
Summary
My hope is that you now realize how important, and easy, it is to properly test your PL/SQL program units. A couple of hours of extra coding up front for each program unit can save your hundreds of hours of troubleshooting later on. More important, it will save your end users from hundreds of hours of lost productivity and frustration.
I’ve shown you the coding patterns involved when coding test units for SQL and PL/SQL. I’ve shown you how to create a relational and object-relational testing tool that can automatically test every program unit and access to their associated tables in your database by executing one procedure: process().
My final suggestion is that you decide on the use of a relational or an object-relation testing tool and then add the required test units that access that tool to each and every program unit you write. As a developer of enterprise applications, I have historically had low error rates. I’ve achieved them by incorporating testing into the process of developing. Assume nothing. Test everything.
Just how important is it to test everything? During the coding of the examples for this book, this testing framework found an average of two errors for each package or TYPE. And I’ve been coding in PL/SQL for more than 20 years!
There’s one last tactic you can employ to help prevent errors: provide access to good documentation to your developers. And that’s what I’m going to cover in the next chapter.