Does the program raise a specific exception or any exception?
We generally don't want or expect our programs to raise exceptions. But under certain circumstances, that is exactly what should happen, and we need to test for that. This tutorial shows how to check for an exception by name or error code. You can also check to see if your program raised any exception at all (rather than a specific exception. The default testing behavior is that if an exception is raised, Quest Code Tester for Oracle records this is a runtime error, rather than a logical failure.

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, ask to create a new test definition by selecting New from the Test Definitions for program right-click menu. Select qctod#test_exceptions from the list and press OK. Click OK on the Welcome to Test Builder screen. 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 case used with each of the following names:

  • Check for any errors
  • No errors raised at all
  • Raise -20000
  • Raise NO_DATA_FOUND
  • Divide by zero

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 in the Test Case List, and then shift focus to the Test Case Editor to edit the input and output grids.

Check for any errors

In this test case, we will check to see if the program raises any exception, it doesn't matter which. This constitutes a failed test.

  • In the Input grid, set the RAISE_UNKNOWN_IN argument to TRUE, all others to FALSE.
  • In the Outcome grid add an outcome by clicking the Add New Outcome button, choose Exception as the Changed by Program Type of Data.
  • In the Operator column, choose "raises any error".
  • Next test case!

No errors raised at all

The program fails if it does not raise any exceptions! Sure, why not….

  • In the Input Grid, set the RAISE_NOTHING_IN argument to TRUE, all others to FALSE.
  • Add an outcome by clicking the add new outcome button and set the changed by program type to “exception”.
  • In the Operator column of Outcomes, change choice to "does not raise any error".
  • Next test case!

Raise -20000

My program uses RAISE_APPLICATION_ERROR to raise an application error in the -20000 to -20999 range. Need to be able to check for these as well.

  • In the Input Grid, set the RAISE_20000_IN argument to TRUE, all others to FALSE.
  • Add an outcome by clicking the add new outcome button and set the changed by program type to “exception”.
  • In the Operator column of Outcomes, change choice to "raises the specified error".
  • Set the Expected Results Type of Data to EXCEPTION.
  • Press […] in the Expected Results value field to bring up properties.
  • Put -20000 in the field next to Exception code number.
  • Press OK.
  • Next test case!

Raise NO_DATA_FOUND

Uh oh, I might be doing a SELECT INTO in my program and I know I don't handle NO_DATA_FOUND. Let's test for that.

  • Set the RAISE_NDF_IN argument to TRUE, all others to FALSE.
  • Add an outcome by clicking the add new outcome button and set the changed by program type to “exception”.
  • In the Operator column of Outcomes, change choice to "raises the specified error".
  • Set the Expected Results Type of Data to EXCEPTION.
  • Press […] in the Expected Results value field to bring up properties.
  • Put NO_DATA_FOUND in the field next to Exception name.
  • Press OK.
  • Next test case!

Divide by zero

My program doesn't raise this error. Oracle raises it when I try to divide by zero. Let's test for that.

  • In the Inputs Grid, set the CAUSE_DIV_BY_ZERO_IN argument to TRUE, all others to FALSE.
  • Add an outcome by clicking the add new outcome button and set the changed by program type to “exception”.
  • In the Operator column of Outcomes, change choice to "raises the specified error".
  • Set the Expected Results Type of Data to EXCEPTION.
  • Press […] in the Expected results value field to bring up properties.
  • Put -1476 in the field next to Exception code number.
  • Press OK.
  • All done!

Time to run your test!

When you have gone through each of the test cases, press the Save, Close and Run button to close Test Builder, return to Test Dashboard and immediately run your test.

Quest Code Tester for Oracle will then save your definition to the repository, and run your test. You should see the Results Viewer and a green flag, indicating a successful run.

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 test_exceptions (
   raise_ndf_in IN BOOLEAN DEFAULT FALSE
 , raise_20000_in IN BOOLEAN DEFAULT FALSE
 , raise_unknown_in IN BOOLEAN DEFAULT FALSE
 , raise_nothing_in IN BOOLEAN DEFAULT FALSE
 , cause_div_by_zero_in IN BOOLEAN DEFAULT FALSE
)
IS
BEGIN
   CASE
      WHEN raise_ndf_in
      THEN
         RAISE NO_DATA_FOUND;
      WHEN raise_20000_in
      THEN
         raise_application_error (-20000, 'I raised -20000 deliberately');
      WHEN raise_unknown_in
      THEN
         RAISE UTL_FILE.file_open;
      WHEN raise_nothing_in
      THEN
         DBMS_OUTPUT.put_line ('No problems here!');
      WHEN cause_div_by_zero_in
      THEN
         DBMS_OUTPUT.put_line (1 / 0);
   END CASE;
END test_exceptions;
/

SHO ERR