Quest Code Tester for Oracle

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

Steps

This program performs a query against user_source for the specified number of iterations. The program includes in its header a block you can run to see how long it takes you to run a given number of iterations. From this, you will be able to determine reasonable values to use in this test.

With 29000 rows in USER_SOURCE, I ran that test script and got the following results:

"1 iterations" elapsed CPU time: 128 hundredths of seconds

"5 iterations" elapsed CPU time: 569 hundredths of seconds

"10 iterations" elapsed CPU time: 1167 hundredths of seconds

I will be using the values 1, 5 and 10 in my examples below; please adjust those numbers to match your own findings. You can determine appropriate numbers by running the commented block of code included in the qctod#elapsed_time procedure.

OK - from the Test Dashboard, create a new test definition by clicking on New from the Test Definition Programs right-click menu. Select qctod#elapsed_time from the list and press OK. Click OK on the Welcome to 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. Press the "Add test case" button to create a new test case and rename the test for each of the following names:

Elapsed time for 1 iteration
Elapsed time for 5 iterations
Elapsed time for 10 iterations

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

Elapsed time for 1 iteration (success)

Click on the case named "Elapsed time for 1 iteration"
In the Input Grid, set the ITERATIONS_IN argument to 1.
In the Outcome grid, click on the Add New Outcome Grid and choose Elapsed Time as the Changed by Program Type of Data.
In the Operator column, choose "<= "(less than or equal to).
Set Type of Data to PLS_INTEGER.
Enter 200 for the expected results value. This is 2 seconds and should be enough (in my case).
Next test case!

Elapsed time for 5 iterations (failure)

Click on the test case named "Elapsed time for 5 iterations"
In the Input Grid set the ITERATIONS_IN argument to 5.
In the Outcome grid, click on the Add New Outcome Grid and choose Elapsed Time as the Changed by Program Type of Data.
In the Operator column, choose "<= "(less than or equal to).
Change the Expected Results value to 200. This should be short a time for 5 iterations (in my case) and will cause a failure.
Next test case!

Elapsed time for 10 iterations

Click on the test case named "Elapsed time for 10 iterations"
In the Input Grid set the ITERATIONS_IN argument to 10.
In the Outcome grid, click on the Add New Outcome Grid and choose Elapsed Time as the Changed by Program Type of Data.
In the Operator column, choose "<= "(less than or equal to).
In the Outcome grid, change the value to 2000. This should be more than enough to pass the test for 10 iterations.
Done!

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 should then see the Results Viewer and a red flag. The test failed. If you drill down, you should see that the "1 iteration" and "10 iterations" tests succeeded, but the "5 iterations" test failed.

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

CREATE OR REPLACE PROCEDURE qctod#elapsed_time (iterations_in IN PLS_INTEGER)
/*
Run the block of code below to determine useful timings for your tests.

DECLARE
   l_start PLS_INTEGER;
   PROCEDURE show_elapsed ( NAME_IN IN VARCHAR2 )
   IS
   BEGIN
      DBMS_OUTPUT.put_line (    '"'
                             || NAME_IN
                             || '" elapsed CPU time: '
                             || TO_CHAR ( DBMS_UTILITY.get_time - l_start  )
                             || ' hundredths of seconds'
                           );
   END show_elapsed;
BEGIN
   l_start := DBMS_UTILITY.get_time;
   qctod#elapsed_time (1);
   show_elapsed ( '1 iterations' );
   l_start := DBMS_UTILITY.get_time;
   qctod#elapsed_time (5);
   show_elapsed ( '5 iterations' );
   l_start := DBMS_UTILITY.get_time;
   qctod#elapsed_time (10);
   show_elapsed ( '10 iterations' );
   l_start := DBMS_UTILITY.get_time;
END;

*/
IS
   dummy PLS_INTEGER;
BEGIN
   FOR indx IN 1 .. iterations_in
   LOOP
      SELECT COUNT (*)
        INTO dummy
        FROM user_source;
   END LOOP;
END qctod#elapsed_time;
/

SHO ERR