The biggest, most important procedures that I test in PL/SQL are those that generate entire web pages. For that kind of testing, you can't look at whether data has been changed in a table, you have to look at the HTML output of the procedure. In a testing scenario, that output would be in the internal buffer used by the HTP package and the others in the PL/SQL web toolkit.
An important procedure could generate a big web page. For that reason, I'd like to be able to compare at a finer granularity than whether the generated web page matches exactly some version that was stored for testing purposes. I think the ideal technique would be to run a regular expression test over each line of output, and be able to check "does any part of the page match this pattern?". It's that kind of flexibility that's missing in a lot of test environments, e.g. causing anxiety over the prospect of changing some text in an error message it might break some text case that does an exact match on all output, not just looking for the error number.
The contents of a web page could be unpredictable. For example, a page of search results might not be exactly the same after the search index has been refreshed. And Web 2.0-style pages could have random elements like "Tip of the Day" or a list of online friends, Twitter messages, or some set of recently viewed links. Even just personalized text like "Hello John".
In testing, I would like to ignore all those things and just focus on the parts that vary according to the parameters. For example, in a search of Oracle documentation, if the search term is "oracle", I expect that somewhere on the page will be a "Next>>" link. If I pass in the right parameters to retrieve page 1 of results, I expect that nowhere on the page will be a "<<Previous" link. If I pass in a nonsensical search term, I expect that the page will contain a particular message saying there aren't any results. For intentional misspellings, I might want to confirm that the right "Did you mean?" message comes up.
In addition, I might want to test certain invisible properties of the page, like the attributes of meta tags, links to stylesheets, or instead of showing exceptions to the user I'll catch them but embed little coded messages inside HTML comment tags.
Now, of course, someone could write a set of test cases in Perl or Python, even shell scripts, to retrieve URLs using some combination of parameters, then do this scanning of the content. But a PL/SQL-aware tool could be more convenient by hooking into the data dictionary to see the available parameters and the procedure source, which is why I'm intrigued by the Code Tester product.
Of course, any big procedure is composed of little parts. It's those little parts that do things like returning a string in a certain format, computing what number to display on a page, opening a cursor for a query. Those procedures and functions are the easy ones to unit test in an automated way, which is what the demo focused on. If you pass this number in, do you get this number out? If you pass zero, a negative value, a too-high number, do you get back the appropriate result or the expected exception? And so on for strings, collections, and other datatypes.
The wrinkle I throw into that testing scenario is internal procedures and functions. If something is directly callable, it'll be a top-level procedure or function, or inside a package. If it's reusable, chances are high it'll be in a package. But if something is abstracted purely so that my procedure body can be written like pseucode:
prepare_for_action();
run_query();
while results_left_to_process() loop
process_results();
end loop;
then I'll nest those procedures and functions inside the current one. No danger of them being called by accident (or by a malicious attacker, in a web scenario with mod_plsql). No name conflict if I want to use the same step name in a different procedure. They can access all the variables from the current procedure, so I don't need to load down the calls with lots of parameters.
Automated testing for those internal procedures and functions could be tricky. They can't be called directly from unit tests outside the original procedure. Stephen suggested using conditional compilation. The first idea that jumped to my mind is to generate instrumentation code and use some SQL*Plus hackery to embed it in the main procedure:
procedure test_me is
procedure step1 is...
procedure step2 is...
function func1...
-- This source file could be generated by a testing tool.
-- But using @ halfway through a procedure is a SQL*Plus-ism
-- that's doubtless unsupported.
@unit_tests_for_test_me;
begin
-- Conditionally compile this block under non-testing circumstances...
step1();
step2();
-- Conditionally compile this block under testing circumstances...
run_unit_tests();
end;
