What the Oracle Really Meant: PL/SQL Testing with Code Tester

1. What the Oracle Really Meant:The Quest for PL/SQL Testing Using Code Tester January 6, 2020Lawrence Livermore National Laboratory Arnold Weinstein - Computer Scientist Tom Crook - Computer Scientist

2. Disclaimer

3. What the Oracle Really Meant:The Quest for PL/SQL Testing Using Code Tester Censored

4. 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 Definition

5. Why do we test ? It’s 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 it’s 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_id%TYPE := 4704; gv_helium_id chemical .chemical_id%TYPE := 4719; gv_argon_cryo_id chemical .chemical_id%TYPE := 21079; gv_nitrogen_id chemical .chemical_id%TYPE := 4930; gv_ethanol_id chemical .chemical_id%TYPE := 4346; gv_tungsten_id chemical .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 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; gv_gal_s300 NUMBER := NULL; gv_gal_main NUMBER := NULL; gv_lbs_s300 NUMBER := NULL; gv_lbs_main NUMBER := NULL; -- -- Testing result rowtypes gv_hhmp_s300_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.container_type%TYPE);

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_v%ROWTYPE, p_rec_main OUT hmmp_v%ROWTYPE);

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_restore(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.