21Oct
By: Steven Feuerstein On: October 21, 2021 In: APEX Developer Solutions, Feuertips Comments: 0

I had the privilege of discussing obstacles to testing our PL/SQL code with Samuel Nitsche (aka, Der Pesse). About Sam:

Samuel Nitsche is a curiosity-driven software developer who programs, learns and collaborates in the software trade since the early 2000s. He works as a Senior Software Developer and trainer at Smart Enterprise Solutions GmbH, a tiny company in South Germany. His main interest is modern database development, automated testing and code quality, topics he writes regularly about on different platforms (e.g. his blog https://developer-sam.de, Simple-Talk, and several Oracle-related print magazines). Samuel is an Oracle ACE, as well as one of the main contributors and maintainers of utPLSQL (http://utplsql.org). He loves to share his experience in an entertaining way – gladly in Sith robes – at meetups and conferences.
Please watch and/or listen to the recording for all details of our conversation.
Some Sam thoughts you might find interesting:
  • If you leave testing for after you finish coding, then you have left the most boring part for the end – of course you’re not going to do it, or much of it!
  • Don’t think of testing as something separate from coding. It’s all part of the same process of building applications.
  • Treating testing as different from coding is like trying to get kids to eat their vegetables. If you keep them separate from everything else, and left to the end, then the veggies are cold and, of course, kids will hate veggies.
  • Mix the veggies with the rest of the meal, and then everything is eaten with gusto!
  • Sam introduced us to the Lump of Code Fallacy, a fascinating way of looking at and thinking about test-driven development from GeePaw. Have you ever heard of GAK (geek at keyboard)? I had not. Check it out!

And from me:

  • Trying a few things is different from testing. But at a minimum, save every “thing” (block of code, test case, whatever) so that you can try it again in the future.
  • Automated testing, regression testing, TDD: they can all sound and be really intimidating.
  • But once you start doing some of that sort of testing, a very powerful, positive feedback loop gets started.
  • We get such a good feeling out of seeing test results, and running them more than once, that we are encouraged to do more.
  • The easiest programs to test are deterministic functions. It’s actually pretty easy to build your own simple test harness for these. I offer an example below.

Sam and I didn’t nearly get to all the thoughts bouncing around in our heads on this topic, so we will do a follow up in the near future!

Simple test harness

The APEXd2d team needed to clean up the text entered by a user for their twitter handle. By which I mean:

  • @sfonplsql -> sfonplsql
  • https://twitter.com/sfonplsql -> sfonplsql
  • etc.

Here’s the function:

create or replace function clean_twitter_handle (
   p_handle in varchar2
) return varchar2 is
begin
   return
      case
         when instr(p_handle, '/') > 0 then
            substr(p_handle, instr(p_handle, '/', -1) + 1)
         when instr(p_handle, '@') > 0 then
            substr(p_handle, instr(p_handle, '@', -1) + 1)
         else p_handle
      end;
end;

And here’s an anonymous block that tests for various scenarios and shows the results.

declare
   k_program       constant varchar2(100) := 'clean_twitter_handle';
   type results_t is
      table of varchar2(1000) index by pls_integer;
   l_results       results_t;
   l_success       boolean := true;
   l_failure_count integer := 0;

   procedure test_it (
      p_test_case    in varchar2,
      p_handle       in varchar2,
      p_expected     in varchar2,
      p_show_success in boolean default true
   ) is
      l_value varchar2(1000);
   begin
      l_value := clean_twitter_handle(p_handle => p_handle);
      if l_value = p_expected or (
         l_value is null and p_expected is null
      ) then
         if p_show_success then
            l_results(l_results.count + 1) := p_test_case || ' succeeded';
         end if;
      else
         l_results(l_results.count + 1) := p_test_case || ' failed';
         l_success                      := false;
         l_failure_count                := l_failure_count + 1;
      end if;
   end;

   procedure summarize_test is
      l_title varchar2(1000) := lpad('*', 5, '*')
                                || ' Results from testing '
                                || k_program
                                || ' '
                                || lpad('*', 5, '*');
   begin
      DBMS_OUTPUT.put_line(l_title);
      DBMS_OUTPUT.put_line('*');
      if l_success then
         DBMS_OUTPUT.put_line('* SUCCESS - All test cases passed.');
      else
         DBMS_OUTPUT.put_line('* FAILURE - '
                              || l_failure_count
                              || ' test cases failed.');
      end if;
      DBMS_OUTPUT.put_line('*');
      for indx in 1..l_results.count loop
         DBMS_OUTPUT.put_line('* ' || l_results(indx));
      end loop;
      DBMS_OUTPUT.put_line('*');
      DBMS_OUTPUT.put_line(lpad('*', length(l_title), '*'));
   end;
begin
   test_it('Just as we want it', 'sfonplsql', 'sfonplsql');
   test_it('Leading @', '@sfonplsql', 'sfonplsql');
   test_it('Leading /', '/sfonplsql', 'sfonplsql');
   test_it('Full url', 'https://twitter.com/sfonplsql', 'sfonplsql');
   summarize_test;
end;
/

I run it and see this output:

***** Results from testing clean_twitter_handle *****
*
* SUCCESS - All test cases passed.
*
* Just as we want it succeeded
* Leading @ succeeded
* Leading / succeeded
* Full url succeeded
*
*****************************************************

A little harness generator

I spent some time last week abstracting the specifics you see above to a utility that will generate a test harness for your function – you then finish it up with some formatting and elaboration of test cases. It’s not nearly as good or useful as utPLSQL, but it might get you excited to do more testing. You can download and try it out right here.

Here’s an example of the code it generates, using this block:

begin
   test_utils.initialize_test_cases_for ('CLEAN_TWITTER_HANDLE', null);
   
   test_utils.add_test_case (
      p_test_case_name =>'Just as we want it',
      p_expected  =>'sfonplsql'
   );
   
   test_utils.add_test_case (
      p_test_case_name =>'Leading @ with @sfonplsql',
      p_expected  =>'sfonplsql'
   );
   
   test_utils.add_test_case (
      p_test_case_name =>'Leading / with /sfonplsql',
      p_expected  =>'sfonplsql'
   );
   
   test_utils.add_test_case (
      p_test_case_name =>'Full URL with https://twitter.com/sfonplsql',
      p_expected  =>'sfonplsql'
   );
   
   test_utils.generate_tester_for_deterministic_function(
      p_function_name => 'CLEAN_TWITTER_HANDLE',
      p_package_name  => null,
      p_overload      => null,
      p_owner         => user
   );
end;
/

The output:

declare
   k_program       constant varchar2(100) := 'SFEUERSTEIN.CLEAN_TWITTER_HANDLE';
   type results_t is
      table of varchar2(1000) index by pls_integer;
   l_results       results_t;
   l_success       boolean := true;
   l_failure_count integer := 0;

   procedure test_it (
      p_test_case    in varchar2,
      P_HANDLE       in varchar2,
      p_expected     in varchar2,
      p_show_success in boolean default true
   ) is
      l_value varchar2(1000);
   begin
      l_value := SFEUERSTEIN.CLEAN_TWITTER_HANDLE(P_HANDLE => P_HANDLE);
      if l_value = p_expected or (
         l_value is null and p_expected is null
      ) then
         if p_show_success then
            l_results(l_results.count + 1) := p_test_case || ' succeeded';
         end if;
      else
         l_results(l_results.count + 1) := p_test_case || ' failed';
         l_success                      := false;
         l_failure_count                := l_failure_count + 1;
      end if;
   end;

   procedure summarize_test is
      l_title varchar2(1000) := lpad('*', 5, '*')
                                || ' Results from testing '
                                || k_program
                                || ' '
                                || lpad('*', 5, '*');
   begin
      DBMS_OUTPUT.put_line(l_title);
      DBMS_OUTPUT.put_line('*');
      if l_success then
         DBMS_OUTPUT.put_line('* SUCCESS - All test cases passed.');
      else
         DBMS_OUTPUT.put_line('* FAILURE - '
                              || l_failure_count
                              || ' test cases failed.');
      end if;
      DBMS_OUTPUT.put_line('*');
      for indx in 1..l_results.count loop
         DBMS_OUTPUT.put_line('* ' || l_results(indx));
      end loop;
      DBMS_OUTPUT.put_line('*');
      DBMS_OUTPUT.put_line(lpad('*', length(l_title), '*'));
   end;
begin
   test_it(
      p_test_case    => 'Just as we want it',
      p_expected     => 'sfonplsql',
      p_show_success => TRUE,
      P_HANDLE       => 'Value_for_P_HANDLE'
   );
   
   test_it(
      p_test_case    => 'Leading @ with @sfonplsql',
      p_expected     => 'sfonplsql',
      p_show_success => TRUE,
      P_HANDLE       => 'Value_for_P_HANDLE'
   );
   
   test_it(
      p_test_case    => 'Leading / with /sfonplsql',
      p_expected     => 'sfonplsql',
      p_show_success => TRUE,
      P_HANDLE       => 'Value_for_P_HANDLE'
   );
   
   test_it(
      p_test_case    => 'Full URL with https://twitter.com/sfonplsql',
      p_expected     => 'sfonplsql',
      p_show_success => TRUE,
      P_HANDLE       => 'Value_for_P_HANDLE'
   );
   summarize_test;
end;

Perhaps you will find this useful. Let me know!

Watch the full episode here!

Feuertips

Every Wednesday at 11 am ET, I go live on YouTube and Facebook with Michelle Skamene to provide you with a not-to-be-missed PL/SQL tip and fun conversation. Sure, we record it so you can always watch later, but live is so much more rewarding!
Speaking of rewards, Steven finishes each show with a quick quiz to see if you were listening. Participation, of course, is optional!
One lucky quiz answerer will be selected at random to choose from three organizations dedicated to the preservation of our planet and its biodiversity: The Sheldrick Wildlife Trust, The Center for Biological Diversity, and Earthwatch.
Insum will then make a donation of $25 to that group in your name.
What could be better than levelling up your PL/SQL tips and helping one of these worthy organizations? Join us Wednesdays at 11!
Share this:
Share

Leave reply:

Your email address will not be published. Required fields are marked *