Quest Code Tester for Oracle

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 from the Test Definitions for Program right-click menu. Select qctod#delete_mult_rows from the list and press OK. Click OK on the Welcome to the Test Builder Dialog. Now you are inside Test Builder.

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 on the left hand side of the Test Builder.

Press the "Add test case" button to create a new test case, and rename the test cases using the following names:

Delete all rows
Delete no rows
Delete some rows

Now it is time to provide the details of each test case.

Delete all rows

Click on "Delete all rows" and shift attention to the Test Case Editor (right-hand side of window).
Step 2: Leave input values as null – this will cause the program to remove all rows.
Step 3: In the empty Outcomes grid, right-click and choose "Add Outcome." In the first field under "Changed by Program," selected Table – this is the type of data structure you want to check. Then press the […] button just below and set the table name to qctod#dmr_table (you can type it in or select it from the list by pressing on the [...] button inside the Properties window. Click OK to close the Properties of Table window.
In the Outcomes Grid, set operator to "is empty".
That's it. Time to move on to the next test case.

Delete no rows

Drag and drop "Delete all rows" to "Delete no rows" test case, and press OK on the Copy Test Case dialog that displays to copy everything over. Select Delete no rows and shift your focus to Step 2 in the Test Case Editor.
Set LOW_IN to -99 and HIGH_IN to -3. These values are not in the table, so nothing will be removed.
In the Outcomes Grid, change operator to "is unchanged".
That's it. Time to move on to the next test case.

Delete some rows

Drag and drop "Delete no rows" to "Delete some rows", and press OK to copy everything over. Select Delete some rows, and then shift your focus to the Test Case Editor.
Set LOW_IN to 2 and HIGH_IN to 4. These will identify 3 rows to be removed.
Move down to Step 3. Outcomes. In this case we need to see if the table contains the correct rows, after deletion. A good way to do this for relatively small tables is to make a copy of the table just before the program runs, modify it to you’re your expectations, and then use that copy as a "control" against which the table changed by the program can be compared.
So: change the operator to "=". You should then see "Table" under the Expected Results header. If not, bring up the list for that field and set it to Table.
Then press […] just below Table. You will then see the properties for this table. Press the and Quest Code Tester will then populate the Initialization and Cleanup fields with dynamic SQL code to create and clean up the copy table.
You will need to modify the code used to create the copy, because you want only those rows that should be left after the deletion. Therefore, change this:

|| 'SELECT * FROM '
|| 'QCTOD#DMR-TABLE’;

to this:

|| 'SELECT * FROM '
|| 'QCTOD#DMR-TABLE where n not between 2 and 4';

Press OK to close Properties window.
That's it. Time to move on to run the test!

Time to run your test!

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

You will then see the results in the Results Viewer. After reviewing the 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.

BEGIN
   EXECUTE IMMEDIATE 'DROP table qctod#dmr_table';
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/

CREATE TABLE qctod#dmr_table (
   n NUMBER)
/

BEGIN
   INSERT INTO qctod#dmr_table
        VALUES (1);

   INSERT INTO qctod#dmr_table
        VALUES (2);

   INSERT INTO qctod#dmr_table
        VALUES (3);

   INSERT INTO qctod#dmr_table
        VALUES (4);

   INSERT INTO qctod#dmr_table
        VALUES (5);

   COMMIT;
END;
/

SHO ERR

CREATE OR REPLACE PROCEDURE qctod#delete_mult_rows (
   low_in IN PLS_INTEGER DEFAULT NULL
 , high_in IN PLS_INTEGER DEFAULT NULL
)
IS
   l_low PLS_INTEGER := low_in;
   l_high PLS_INTEGER := high_in;
BEGIN
   IF l_low IS NULL
   THEN
      SELECT MIN (n)
        INTO l_low
        FROM qctod#dmr_table;
   END IF;

   IF l_high IS NULL
   THEN
      SELECT MAX (n)
        INTO l_high
        FROM qctod#dmr_table;
   END IF;

   DELETE FROM qctod#dmr_table
         WHERE n BETWEEN l_low AND l_high;
END qctod#delete_mult_rows;
/