Introduction to testing collections
Collections are array-like structures in PL/SQL. We start with a quick introduction to collections, and then show how to use Quest Code Tester for Oracle to test the contents of a simple collection of scalars.

Flash tutorial explaining the topic: A brief video that shows you how to use Quest Code Tester to solve the problem posed by this topic.

Step-by-step instructions: Follow these steps to replicate what we show you in the tutorial.

Steps

From the Test Dashboard, create a new test definition by selecting New on the Test Definition for Program right-click menu. Select qctod#parse from the list and press OK. Click OK on the Welcome to the Test Builder dialog. Now you are inside Test Builder. Make sure you are working with the function version of string_to_list (click on Program Details and choose Function string_to_list_2, and click anywhere in the Test Builder window to hide program details).

Set up the test cases

Before diving into the details of each of the test cases, use "top down design" to brainstorm the various test cases that you will need. You do this in the Test Case list.

Press the "Add test case" button to create a new test case. Now in this case, I need to test a program that parses a delimited list. This means that I need to check for a variety of conditions, such as "the list starts with a delimiter", "the list has two delimiters next to each other", etc. Rather than try to work this all out for myself, I am going to take advantage of Quest Code Tester's Test Data Groups.

So I will create a "starting" test case named "Comma delimited list", because I want to build some tests using "," as the delimiter. Just the one test case.

Specify variations for the STRING_IN argument

In order to pass in a variety of list formats for the string, and then check the results for each, press on the [...] button to the right of STRING_IN in the Input Grid,  the Properties window appears and offers several options for specifying the value of STRING_IN.

To see if an existing test data group can help, press the "Use an existing test data group radio" option. You will then see the section below to choose a test data group. 

Expand the string values folder that appears in the list. Expand the Delimited Lists sub-tree, and click on "Comma delimited".

Press OK. You have now defined a test case with multiple values for a single input.

In the test case list, an icon to the right of the test case name indicates that there are multiple values used in the test case. Now, many times when working with multiple values, you will leave it this way, and Quest Code Tester will then generate all the test cases associated with the multiple values at testing time.

In this case, however, I need to provide specific customization logic for each of the test cases, in order to set up the expected collections.

Press on the "Expand multiple values test" button on the Test Cases toolbar  and the test case will be expanded.

Notice that the value for STRING_IN appears in the name of the test case.

Now it is time to provide the details of each test case. For each of the headers below, click the corresponding test case name, and then shift focus to the Test Case Editor to fill in the blanks.

We will go through the steps required for just a few of the total of nine test cases. Once you see what we are doing here, you can figure out the rest for yourself.

Comma delimited list 1 STRING_IN=ABC,DEF, ...

In this case, the delimited list is "ABC,DEF,". This means that we expect that the collection returned by the function will have three elements in it:

ABC
DEF
NULL

So we need to set up the expected results collection to look like that. Here's how we do it:

  1. First, notice that no changes are needed in the Inputs section.
  2. Looking at Outcomes, Quest Code Tester has already set up an outcome to compare the collection returned by the function to another function of the same type. So far so good.
  3. Press the [...] button in the Outcome Expected Results value field to see the properties for the expected results collection.
  4. We now need to populate the control collection with data that we expect to be returned by the function. The name of the variable that holds this control collection is in the top left corner of the window (should be e_Function_return_value1, but this name can change so please confirm it). Type this information into the Initialization tab of this outcome:

e_Function_return_value1(1) := 'ABC';

e_Function_return_value1(2) := 'DEF';

e_Function_return_value1(3) := '';

We hope you can see why these entries will set up the collection with the data that we expect to have returned by the function.

  1. Highlight all that text and copy it to the clipboard using the Ctrl+C hotkey. You will be using it again soon. Then press OK, and we are done with this test case. Next!

Comma delimited list 2 STRING_IN=ABC,DEF,, DELIM_IN=,

This variation has two trailing delimiters, so we do much the same as in the previous test case, but faster and easier because we have the code that we just entered:

  1. First, notice that no changes are needed in the Inputs section.
  2. Looking at Outcomes, Quest Code Tester has already set up an outcome to compare the collection returned by the function to another function of the same type. So far so good.
  3. Press the [...] button in the Outcome expected results value field to see the properties for the expected results collection.
  4. We now need to populate the control collection with data that we expect to be returned by the function. The name of the variable that holds this control collection is in the top left corner of the window (should be e_Function_return_value1, but this name can change so please confirm it). Type this information into the Initialization tab of this outcome, or paste and edit the code entered before by using the Ctrl+V hotkey:

e_Function_return_value1(1) := 'ABC';

e_Function_return_value1(2) := 'DEF';

e_Function_return_value1(3) := '';

e_Function_return_value1(4) := '';

  1. We hope you can see why these entries will set up the collection with the data that we expect to have returned by the function.
  2. Highlight all that text and copy it to the clipboard. You will be using it again soon. Then press OK, and we are done with this test case. Next!

Finish up...

Well, hopefully you have the idea now. You will need to visit each test case, and ensure that the initialization logic for the outcome defines the expected collection correctly.

Time to save/run your test!

You can now save your test definition and run your test with a single press of the Save , Close, and Run  button in the Test Cases toolbar.

You will then see the results in the Results Viewer. After reviewing results, close the Results Viewer.

Source code of program tested: Compile this code in your schema so that you can build your test definition for it. 

CREATE OR REPLACE PACKAGE qctod#parse
IS
   TYPE item_tt IS TABLE OF VARCHAR2 (2000)
      INDEX BY PLS_INTEGER;

   PROCEDURE string_to_list (
      string_in IN VARCHAR2
    , items_out IN OUT item_tt
    , delim_in IN VARCHAR2 := ','
   );

   FUNCTION string_to_list (string_in IN VARCHAR2, delim_in IN VARCHAR2
            := ',')
      RETURN item_tt;
END qctod#parse;
/

SHO ERR

CREATE OR REPLACE PACKAGE BODY qctod#parse
IS
   FUNCTION string_to_list (string_in IN VARCHAR2, delim_in IN VARCHAR2
            := ',')
      RETURN item_tt
   IS
      l_item VARCHAR2 (32767);
      l_loc PLS_INTEGER;
      l_startloc PLS_INTEGER := 1;
      items_out item_tt;

      PROCEDURE add_item (item_in IN VARCHAR2)
      IS
      BEGIN
         IF (item_in != delim_in OR item_in IS NULL)
         THEN
            items_out (NVL (items_out.LAST, 0) + 1) := item_in;
         END IF;
      END;
   BEGIN
      IF string_in IS NOT NULL
      THEN
         LOOP
            -- Find next delimiter
            l_loc := INSTR (string_in, delim_in, l_startloc);

            IF l_loc = l_startloc                    -- Previous item is NULL
            THEN
               l_item := NULL;
            ELSIF l_loc = 0                     -- Rest of string is last item
            THEN
               l_item := SUBSTR (string_in, l_startloc);
            ELSE
               l_item := SUBSTR (string_in, l_startloc, l_loc - l_startloc);
            END IF;

            add_item (l_item);

            IF l_loc = 0
            THEN
               EXIT;
            ELSE
               l_startloc := l_loc + 1;
            END IF;
         END LOOP;
      END IF;

      RETURN items_out;
   END string_to_list;

   PROCEDURE string_to_list (
      string_in IN VARCHAR2
    , items_out IN OUT item_tt
    , delim_in IN VARCHAR2 := ','
   )
   IS
   BEGIN
      items_out := string_to_list (string_in, delim_in);
   END string_to_list;
END qctod#parse;
/

SHO ERR