What%20the%20Oracle%20Really%20Meant:%20The%20Quest%20for%20PL/SQL%20Testing%20Using%20Code%20Tester

1
What the Oracle Really MeantThe Quest for
PL/SQL Testing Using Code Tester
June 2, 2018Lawrence Livermore National
Laboratory Arnold Weinstein - Computer
Scientist Tom Crook - Computer Scientist
2
Disclaimer
3
What the Oracle Really MeantThe Quest for
PL/SQL Testing Using Code Tester
Censored
4
Definition

• Software testing determines the extent to which a
  software product conforms to its requirements.
• Program testing can be used to show the presence
  of bugs, but never to show their absence!
  Edsger Dijkstra
• There are many approaches to software testing,
  but effective testing of complex products is
  essentially a process of investigation, not
  merely a matter of creating and following rote
  procedure.
• Processes without the proper foundation may fail
  at the very point they are needed most under
  stress. SEI CMM

5
Why do we test ?
Its something we just do.
6
Why we really test !
Failure is not an option.
7
What Code Tester Is

• A proprietary IDE specifically designed to test
  PL/SQL.
• A reverse-engineering tool?

8
What Code Tester Is Not

• A completely automated testing solution.
• Large amounts of PL/SQL unit-test code are
  generated.
• However, the user will have to write varying
  amounts of hand-coded PL/SQL depending on testing
  requirements.
• A GUI testing tool.
• The tool itself is highly graphical, however its
  not suitable for testing GUIs.

9
Strategies for using Code Tester

• Build a test Definition
• Initialize and Cleanup code.
• Declare variables and constants.
• Configure test set data.
• Capture result for comparisons.
• Rollback change.
• Specific test case
• Start with null cases.
• Add boundary cases.
• Add path-coverage (switches, conditionals).
• Create test steps to verify environment.
• Create test step to test code is working properly.

10
Code Tester Getting Started
11
Developing Test Suites

• Create test cases and outcomes.
• Run test cases.
• Make adjustments to test package or program based
  on results of test (Learn-by-Testing).
• After making changes, rerun to ensure everything
  is still working properly.

12
Code Tester Controls
13
Code Tester Editor Source
14
Code Tester Editor Customization
15
Setup Data

• -- Testing constraints
• gv_argon_id chemical
  .chemical_idTYPE 4704
• gv_helium_id chemical
  .chemical_idTYPE 4719
• gv_argon_cryo_id chemical
  .chemical_idTYPE 21079
• gv_nitrogen_id chemical
  .chemical_idTYPE 4930
• gv_ethanol_id chemical
  .chemical_idTYPE 4346
• gv_tungsten_id chemical
  .chemical_idTYPE 4702
• gv_argon_mix_id chem_synonyms.syn_id
  TYPE 1034879
• gv_air_id chem_synonyms.syn_id
  TYPE 1025715
• gv_acrylic_id chem_synonyms.syn_id
  TYPE 1024889
• gv_apcompound_id chem_synonyms.syn_id
  TYPE 1041825
• --
• -- Testing results
• gv_bldg_main_all VARCHAR2(2000)
  NULL
• gv_type_main_all VARCHAR2(2000)
  NULL
• gv_bldg_s300_all VARCHAR2(2000)
  NULL
• gv_type_s300_all VARCHAR2(2000)
  NULL
• gv_cu_ft_s300 NUMBER
  NULL
• gv_cu_ft_main NUMBER
  NULL

16
Using Initialization

• -- Initialization for this test code
• UPDATE facility_log
• SET inv_begin v_inv_begin,
• inv_end v_inv_end
• WHERE bldg v_bldg
• v_return_pc_rec_save test_utilities.pc_rec_save
  (v_p_ct_barcode)
• -- Pre-execution code
• SELECT INTO v_pc_rec_before
• FROM primecontainer
• WHERE ct_barcode v_p_ct_barcode
• calc_rec_global (gv_helium_id)

17
Setup Custom Code

• PROCEDURE calc_gal_global (p_chemid IN
  NUMBER, p_qty_s300 OUT NUMBER, p_qty_main OUT
  NUMBER)
• PROCEDURE calc_lbs_global (p_chemid IN
  NUMBER, p_qty_s300 OUT NUMBER, p_qty_main OUT
  NUMBER)
• PROCEDURE calc_lbs_syn (p_synid IN
  NUMBER, p_qty_s300 OUT NUMBER, p_qty_main OUT
  NUMBER)
• PROCEDURE calc_bldg_global (p_chemid IN
  NUMBER)
• PROCEDURE calc_type_global (p_chemid IN
  NUMBER)
• PROCEDURE calc_rec_global (p_chemid IN
  NUMBER)
• PROCEDURE calc_step_log_global(p_chemid IN
  NUMBER, p_error_msg OUT VARCHAR2)
• PROCEDURE calc_rec_syn_global (p_chemid IN
  NUMBER, p_rec_s300 OUT hmmp_vROWTYPE,
• 
  p_rec_main OUT hmmp_vROWTYPE)

18
Using Cleanup

• -- Post-execution code
• SELECT INTO v_pc_rec_after
• FROM primecontainer
• WHERE ct_barcode v_p_ct_barcode
• -- Cleanup for this test case
• v_return_pc_rec_restore test_utilities.pc_rec_r
  estore(v_p_ct_barcode)
• rollback

19
Code Tester Editor Outcomes
20
Code Tester Editor Builder
21
Code Tester Editor Builder Properties
22
Code Tester Results
23
Code Tester Editor Outcome Customization
24
Code Tester Editor Outcome Customization
25
Code Tester Editor Builder Properties
26
Back to the Developer
27
Approaches

• Declare as many literals as variables constants
  as possible
• Create custom procedures to capture results using
  your declared variables constants.
• If necessary create custom database-bound Test
  Utilities
• Used before and after data setup
• Allows for repetitive runs
• Test Test-Utilities with Code Tester
• Calibration - checks environmental dependencies
• Test testing and development code simultaneously
  write a little, test a lot
• Save incremental sets often (several times/day
  not unusual)

28
Summary

• Testing is essential and tedious, time
  consuming and difficult.
• Code Tester is neither a complete nor
  comprehensive testing solution (no other product
  really is either).
• However, it is a very useful tool for unit
  testing and reverse-engineering PL/SQL packages
• Makes testing PL/SQL easier and faster.
• Makes PL/SQL unit-testing software easier to
  install, configure, develop run.
• Formalizes and stores test procedures so they can
  be rerun as changes are made to the code being
  tested.
• The testing is only as good as you make it
  however, within this framework, your testing will
  improve.