What the Oracle Really Meant The Quest for

What the Oracle Really Meant: The Quest for PL/SQL Testing Using Code Tester 21 October 2021 Lawrence Livermore National Laboratory Arnold Weinstein - Computer Scientist Tom Crook - Computer Scientist 10/21/2021 1

Disclaimer 10/21/2021 2

What the Oracle Really Meant: The Quest for PL/SQL Testing Using Code Tester Censored� 10/21/2021 3

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 10/21/2021 4

Why do we test ? It’s something we just do. 10/21/2021 5

Why we really test ! Failure is not an option. 10/21/2021 6

What Code Tester Is • A proprietary IDE specifically designed to test PL/SQL. • A reverse-engineering tool? 10/21/2021 7

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 it’s not suitable for testing GUIs. 10/21/2021 8

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/21/2021 9

Code Tester Getting Started 10/21/2021 10

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. 10/21/2021 11

Code Tester Controls 10/21/2021 12

Code Tester Editor Source 10/21/2021 13

Code Tester Editor Customization 10/21/2021 14

Setup Data -- Testing constraints gv_argon_id chemical_id%TYPE : = 4704; gv_helium_id chemical_id%TYPE : = 4719; gv_argon_cryo_id chemical_id%TYPE : = 21079; gv_nitrogen_id chemical_id%TYPE : = 4930; gv_ethanol_id chemical_id%TYPE : = 4346; gv_tungsten_id chemical_id%TYPE : = 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 VARCHAR 2(2000) : = NULL; gv_type_main_all VARCHAR 2(2000) : = NULL; gv_bldg_s 300_all VARCHAR 2(2000) : = NULL; gv_type_s 300_all VARCHAR 2(2000) : = NULL; gv_cu_ft_s 300 NUMBER : = NULL; gv_cu_ft_main NUMBER : = NULL; gv_gal_s 300 NUMBER : = NULL; gv_gal_main NUMBER : = NULL; gv_lbs_s 300 NUMBER : = NULL; gv_lbs_main NUMBER : = NULL; --- Testing result rowtypes gv_hhmp_s 300_rec hmmp_v%ROWTYPE; gv_hhmp_main_rec hmmp_v%ROWTYPE; --- Testing record types TYPE grec_bldg IS RECORD(bldg primecontainer. bldg %TYPE); TYPE grec_type IS RECORD(container_type primecontainer_type%TYPE); 10/21/2021 15

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); 10/21/2021 16

Setup Custom Code PROCEDURE calc_gal_global (p_chemid IN NUMBER, p_qty_s 300 OUT NUMBER, p_qty_main OUT NUMBER); PROCEDURE calc_lbs_syn (p_synid 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); IN NUMBER, p_qty_s 300 PROCEDURE calc_step_log_global(p_chemid IN NUMBER, p_error_msg OUT VARCHAR 2); PROCEDURE calc_rec_syn_global (p_chemid IN NUMBER, p_rec_s 300 p_rec_main 10/21/2021 OUT hmmp_v%ROWTYPE, OUT hmmp_v%ROWTYPE); 17

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_restore : = test_utilities. pc_restore(v_p_ct_barcode); rollback; 10/21/2021 18

Code Tester Editor Outcomes 10/21/2021 19

Code Tester Editor Builder 10/21/2021 20

Code Tester Editor Builder Properties 10/21/2021 21

Code Tester Results 10/21/2021 22

Code Tester Editor Outcome Customization 10/21/2021 23

Code Tester Editor Outcome Customization 10/21/2021 24

Code Tester Editor Builder Properties 10/21/2021 25

Back to the Developer 10/21/2021 26

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-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) 10/21/2021 27

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. 10/21/2021 28