3 SQL Developer: Unit Testing

The SQL Developer unit testing feature provides a framework for testing PL/SQL objects, such as functions and procedures, and monitoring the results of such objects over time. You create tests, and for each you provide information about what is to be tested and what result is expected. The SQL Developer implementation of unit testing is modeled on the classic and well known xUnit collection of unit test frameworks.

The unit testing feature is part of the support within the SQL Developer family of products for major parts of the life cycle of database system development, from design (provided by Data Modeler) to development to testing.

This chapter contains the following major sections:

Section 3.1, "Overview of Unit Testing"

Section 3.2, "SQL Developer User Interface for Unit Testing"

Section 3.3, "Unit Test Repository"

Section 3.4, "Editing and Running a Unit Test"

Section 3.5, "Using a Dynamic Value Query to Create a Unit Test"

Section 3.6, "Using Lookups to Simplify Unit Test Creation"

Section 3.7, "Using Variable Substitution in Validation Actions"

Section 3.8, "Unit Test Library"

Section 3.9, "Unit Test Reports"

Section 3.10, "Exporting and Importing Unit Test Objects"

Section 3.11, "Command-Line Interface for Unit Testing"

Section 3.12, "Example of Unit Testing (Tutorial)"

3.1 Overview of Unit Testing

The SQL Developer unit testing framework involves a set of sequential steps for each test case. The steps are as follows, including the user input for before the step is run and the framework activities for the step while the test is being run.

  1. Identify the object to be tested.

    User Input: Identify the object, such as a specific PL/SQL procedure or function.

    Framework Activities: Select the object for processing.

  2. Perform any startup processing.

    User Input: Enter the PL/SQL block, or enter NULL for no startup processing.

    Framework Activities: Execute the block.

  3. Run the unit test object.

    User Input: (None.)

    Framework Activities: Execute the unit test.

  4. Check and record the results.

    User Input: Identify the expected return (result), plus any validation rules.

    Framework Activities: Check the results, including for any validation, and store the results.

  5. Perform any end processing (teardown).

    User Input: Enter the PL/SQL block, or enter NULL for no teardown activities.

    Framework Activities: Execute the block.

For each test, you enter the information called for in the preceding steps, to create a test case. A unit test is a group of test cases (one or more) on a specific PL/SQL object.

Each test case is an implementation. Each unit test has at least one implementation (named Default by default); however, you can add one or more other implementations. For example, you can have implementations that test various combinations of parameter values, including those that generate exceptions.

When a unit test is run, each implementation is run one after the other. Each implementation runs the startup action (if any) for the test, then the test implementation itself, and then the teardown action (if any). The difference between implementations is in the values of the calling arguments. Any dynamic value query (explained in Section 3.5, "Using a Dynamic Value Query to Create a Unit Test") is evaluated before the execution of all of the implementations, including before any startup action.

You can group unit tests into a test suite to be run as a grouped item, and the test suite can have its own startup and end processing in addition to any specified for test cases and unit tests.

To learn more about unit testing with SQL Developer, take whichever approach suits your preference:

3.2 SQL Developer User Interface for Unit Testing

The SQL Developer user interface for unit testing includes the Unit Test navigator, the Unit Test submenu, and other features.

Figure 3-1, "Unit Test Navigator" shows the Unit Test navigator, which includes the top-level nodes Library, Lookups, Reports, Suites, and Tests. (If this navigator is not visible, click View, then Unit Test.)

Figure 3-1 Unit Test Navigator

Unit Test navigator, explained in surrounding text

In the preceding figure, the top-level repository node shows the name of the connection being used (unit_test_repos) and whether the user associated with that connection has only User access to the repository or both Administrator and User access (here, both). (Administrator and User access are explained in Section 3.3.)

The preceding figure also shows the types of actions under the Library node (Startups, Teardowns, Validations), one test suite, and several tests.

3.2.1 Unit Test Submenu

To display the Unit Test submenu, click Tools, then Unit Test. (The commands on the Unit Test submenu affect the Unit Test Repository.)

Select Current Repository: Enables you to select the database connection to use for the unit testing repository, and to create a repository using that connection if no repository exists in the associated schema.

Deselect Current Repository: Disconnects from the current unit testing repository. To connect again to a unit testing repository (the same one or a different one), use Select Current Repository.

Create/Update Repository: Enables you to create a unit resting repository, to hold schema objects associated with the SQL Developer unit testing feature.

Drop Repository: Drops (deletes) the current unit testing repository.

Purge Repository: Deletes the contents of the current unit testing repository, but does not delete the repository metadata.

Manage Users: Enables you to select, add, and modify database connections to be used for the unit testing repository.

Select As Shared Repository: Makes the current repository a shared repository.

Deselect As Shared Repository: Makes the current repository an unshared repository.

3.2.2 Other Menus: Unit Test Items

The View menu has the following item related to unit testing:

  • Unit Test: Toggles the display of the Unit Test navigator.

3.2.3 Unit Test Preferences

The SQL Developer user preferences window (displayed by clicking Tools, then Preferences) contains a Unit Test Parameters pane.

For information about specific preferences, click Help in the pane or see Section 1.18.14.

3.3 Unit Test Repository

The unit test repository is a set of tables, views, indexes, and other schema objects that SQL Developer maintains to manage the use of the unit testing feature. (Most of these objects have UT_ in their names.) You can create a separate database user for a repository or use the schema of an existing database user; but for simplicity and convenience in an environment with a single main shared repository, you may want to create a separate database user (as is done in Section 3.12, "Example of Unit Testing (Tutorial)").

A repository can be unshared or shared, depending on how many and which database users are allowed to perform various types of unit testing operations:

  • In an unshared repository, only the database user that owns the unit test repository schema objects can be used for operations than can modify the repository.

    There can be multiple unshared repositories, for example, to allow individual developers to create private repositories.

  • In a shared repository, the owner of the repository objects and any other user that has been granted Administrator access to the repository (specifically, UT_REPO_ADMINISTRATOR role) can perform administrative operations, such as managing users.

    There can be at most one shared repository, and this is the typical case for a team development environment. A repository administrator can add users (as explained in Section 3.3.1) and can switch the repository status between shared and unshared. (When a repository is made shared, SQL Developer creates public synonyms for the appropriate repository objects.)

To change an unshared repository to shared, click Tools, then Unit Test, then Repository, then Select As Shared Repository. To change a shared repository to unshared, click Tools, then Unit Test, then Repository, then Deselect As Shared Repository.

3.3.1 Managing Repository Users and Administrators

To create and run unit tests and suites, you must use a connection for a database user that has been granted User access to the repository (specifically, UT_REPO_USER role). To perform repository administrative operations, such as managing users, you must use a connection for a database user that has been granted Administrator access to the repository (specifically, UT_REPO_ADMINISTRATOR role).

For example, you may want to allow users SCOTT, JONES, and SMITH to use the unit test capabilities and thus have User access to the shared repository, but to allow only SYS and the user that owns the repository objects (such as UNIT_TEST_REPOS in Example of Unit Testing (Tutorial)) to have Administrator access to the shared repository.

To grant either type of access to any database users, click Tools, then Unit Test, then Repository, then Manage Users. Select the database connection for the owner of the repository objects or for any other user that has been granted Administrator access to the repository. The Unit Testing: Manage Users dialog box is displayed.

3.4 Editing and Running a Unit Test

To edit or run a unit test, click the unit test name in the Unit Test navigator and select the desired connection for running the unit test. A pane is displayed with two tabs: Details for the unit test specification, and Results for results if you run or debug the test.

The toolbar under the Details tab for the subprogram name has a toolbar that includes the icons shown in the following figure.

Toolbar icons for editing or running a unit test
  • Freeze View (the pin) keeps that pane in the SQL Developer window when you click another unit test in the Unit Test navigator; a separate tab and detail view pane are created for that other unit test. If you click the pin again, the unit test's detail view pane is available for reuse.

  • Refresh refreshes the display in the pane.

  • Debug starts execution of the first or next implementation of the unit test in debug mode, and displays the results in the Results tab.

  • Run starts normal execution of the unit test, and displays the results in the Results tab. (Before you click Run, you can specify the database user for the run operation by selecting a database connection on the right.)

  • Edit (pencil icon) enables you to edit the unit test specification. (If you cannot modify the unit test, lick the Edit icon.)

  • Commit Changes saves any changes that you have made to the unit test.

  • Rollback Changes discards any unsaved changes that you have made to the unit test.

If you click the Edit icon, you can modify the Startup Process, Teardown Process, and details for each implementation.

You can also specify Gather Code Coverage Statistics to have SQL Developer collect statistics related to code coverage. To view any statistics that have been gathered from unit test runs, use the Test Runs Code Coverage report. In that report, you can click a row with summary information to display detailed information in the Code Coverage Details pane.

3.5 Using a Dynamic Value Query to Create a Unit Test

As an alternative to specifying exact input data when creating a unit test, you can create a dynamic value query to use data from a table as input for the test. The query returns values from specified columns in one or more rows, and all sets of values returned are checked by any process validation that you have specified for the test. One common use of dynamic value queries is to perform "reasonableness" tests, such as checking that each salary or price resulting from a test is within a specified range.

To create a test that uses dynamic value queries, create and populate the table to be used by the query, create the test by specifying the object to be tested and any startup and teardown actions, and specify a validation action (such as a query returning rows or no rows).

Note:

A dynamic value query is executed before the execution of all implementations in a test, including any startup action for the test. If you must populate a table before a dynamic value query is evaluated, you can do this is the startup action for a suite that includes the test.

The following example assumes that you done at least the following in Section 3.12, "Example of Unit Testing (Tutorial)": created the EMPLOYEES table, created the AWARD_BONUS procedure, and created the unit test repository. It creates a unit test that checks to be sure that no salesperson would receive a bonus so large that his or her salary amount would be greater than 20000. Follow these steps:

  1. Create and populate the table for the data by executing the following statements:

    CREATE TABLE award_bonus_dyn_query (emp_id NUMBER PRIMARY KEY, sales_amt NUMBER);
    INSERT INTO award_bonus_dyn_query VALUES (1001, 5000);
    INSERT INTO award_bonus_dyn_query VALUES (1002, 6000);
    INSERT INTO award_bonus_dyn_query VALUES (1003, 2000);
    commit;
    
  2. In the Unit Test navigator, right-click the Tests node and select Create Test.

    The Unit Testing: Create Unit Test wizard is displayed. Click Next to go from each step to the next; and when you are finished specifying the unit test, click Finish.

  3. In Select Operation, select the database connection for the schema that you used to create the AWARD_BONUS procedure; then expand the Procedures node and select AWARD_BONUS.

  4. In Specify Test Name, for Test Name specify AWARD_BONUS_DYN_QUERY (same as the name of the table that you created), and select Create with single dummy representation.

  5. In Specify Startup, select Table or Row Copy because you want to save the current data values in the EMPLOYEES table before any data is modified by the unit test.

    When prompted, for Source Table specify EMPLOYEES, and for Target Table accept the default name provided for a temporary table that will be automatically created when it is needed and deleted when it is no longer needed.

  6. In Specify Parameters, click Next to go to the next page. (For this example, do not specify the Dynamic Value Query here; instead, you will specify it in later steps.)

  7. In Specify Validations, click Next to go to the next page.

  8. In Specify Teardown, select Table or Row Restore because you want to restore the original data values in the EMPLOYEES table before any data was modified by the unit test. When prompted, accept the supplied values for Target Table (EMPLOYEES) and Source Table (the name of the temporary table).

  9. In Summary, review the information. If you need to change anything, click Back as needed and make the changes, then proceed to this Summary page. When you are ready to complete the unit test definition, click Finish.

  10. In the Unit Test navigator, click the node for AWARD_BONUS_DYN_QUERY under Tests, to display the test in an editing window.

  11. In the Details pane, click the pencil icon next to Dynamic Value Query, enter the following, and click OK:

    SELECT emp_id, sales_amt FROM award_bonus_dyn_query;
    
  12. For Expected Result, leave the value as Success.

  13. In Specify Validations, click the plus (+) icon and select Query returning no rows.

    For the query, replace the SELECT statement in the Process Validation box with the following (any semicolon at the end of the statement is ignored):

    SELECT * FROM employees WHERE salary_amt > 20000
      AND commission_pct IS NOT NULL
    

    That is, for all salespersons (employees whose commission percentage is not null), check whether the salary resulting from the unit test run is greater than 20000. If there are no such salespersons (that is, if the query returns no rows), the result of the validation action is success.

  14. Run the AWARD_BONUS_DYN_QUERY unit test. (For the basic steps to run a unit test, see Section 3.12.5.)

3.6 Using Lookups to Simplify Unit Test Creation

A lookup is an object that contains, for one or more data types, data values that can be tested. Lookups are mainly used for the following purposes:

  • Providing lists of values (dropdown lists) for Input fields, as explained in Section 3.6.1.

  • Automatically creating test implementations based on lookup values, as explained in Section 3.6.2.

To create a lookup:

  1. In the Unit Test navigator, right-click the Lookups node and select Add Category.

  2. Specify the category name (for example, EMP_ID_LOOKUP).

  3. For each data type for which you want to specify lookup values (that is, valid and invalid data values for possible testing), right-click the category name and select Add Datatype, select the data type, and use the + (plus sign) icon to add as many data values as you want.

    Note that (null) is automatically included in the list of values for each data type for each lookup that you create.

For example, for the environment described in Section 3.12, "Example of Unit Testing (Tutorial)", you could create lookups named EMP_ID_LOOKUP and SALES_AMT_LOOKUP. Each would have only one data type: NUMBER. For the NUMBER data for each lookup, use the + (plus sign) icon to add each of the following values on a separate line, and click the Commit Changes icon or press F11 when you are finished entering the set of numbers for each lookup:

  • For EMP_ID_LOOKUP: -100, 99, 1001, 1002, 1003, 1004, 2000, 9999

  • For SALES_AMT_LOOKUP: -1000, 0, 1000, 2000, 5000, 6000, 10000, 99999

You can delete and rename lookup categories by using the context (right-click) menu in the Unit Test navigator. You can also delete a data type under a lookup category; however, "deleting" in this case removes any currently specified data values for that type for the lookup category, and it makes the type available for selection in the Unit Testing: Add Data Type dialog box.

3.6.1 Providing Values for Input Fields

When you are specifying Input parameters for a unit test implementation, you can click the Lookup Category control to select a lookup category. When you then click in a cell under Input, you can click the dropdown arrow to select a value from the specified lookup. (You can also enter a value other than one in the list.)

For example, if you created the EMP_ID_LOOKUP lookup category as explained in Section 3.6, and if you select it as the lookup category when specifying parameters, then the values -100, 99, 1001, 1002, 1003, 1004, 2000, 9999, and (null) will be in the dropdown list for the Input cell for the EMP_ID parameter. (For the SALES_AMT parameter, use the SALES_AMT_LOOKUP category.)

3.6.2 Automatically Creating Implementations

If you know that you want implementations to test certain values for a data type, you can use a lookup category to generate these implementations automatically instead of creating them all manually. To do this, use either the DEFAULT lookup category or a user-created category, specify the values for the desired data type, then specify that lookup category for the Configuration set to use for lookups preference in the Unit Test Parameters preferences.

For example, assume that for NUMBER input parameters, you always want to check for a very high positive number (such as 9999), a very low negative number (such as -9999), 1, -1, and 0 (zero). Follow these steps:

  1. In the Unit Test navigator, expand the Lookups node.

  2. Right-click DEFAULT and select Add Datatype.

  3. In the dialog box, specify NUMBER.

  4. In the Lookups Editor for the NUMBER type, use the + (plus sign) icon to add each of the following as a separate item (new line).

    9999
    1.0
    0
    -1.0
    -9999
    
  5. Click the Commit Changes icon or press F11.

  6. Click Tools, then Preferences, then Unit Test Parameters, and ensure that the configuration set to use for lookups is DEFAULT (the lookup category for which you just specified the values for the NUMBER data type).

  7. Create the unit test in the usual way: in the Unit Test navigator, right-click the Tests node and select Create Test.

    However, in the Specify Test Name step, select Seed/Create implementations using lookup values (that is, not "Create with single dummy representation").

    For Specify Startup and Specify Teardown, specify any desired action.

    You cannot specify anything for Specify Parameters or Specify Validations now. An implementation (with a name in the form Test Implementation n) will automatically be created for each possible combination of input parameters of type NUMBER. For any validation actions, you must specify them later by editing each generated implementation.

3.7 Using Variable Substitution in Validation Actions

You can use variable substitution in validation actions to write dynamic validations that provide a result based on the values of input and output parameters of a procedure or function, or on the return value of a function. You can specify strings in the following format in validation actions:

  • For input parameters: {PARAMETER_NAME}

    For example, if an input parameter is named EMP_ID:

    SELECT ... WHERE employee_id = {EMP_ID} AND ...;
    
  • For output parameters: {PARAMETER_NAME$}

    For example, if an output parameter is named SALARY:

    SELECT ... WHERE {SALARY$} < old_salary;
    
  • For the return value: {RETURNS$}

    For example, if a function returns a numeric value:

    SELECT ... WHERE {RETURNS$} > 1;
    

What is actually substituted is the string representation of the parameter value (for text substitution), or the underlying data value of the parameter (for bind substitution, using the syntax :param-name). The following example shows both styles of substitution (text style and bind style):

DECLARE
    l_PARAM1 DATE;
    bad_date EXCEPTION;
BEGIN
    l_PARAM1 := :PARAM1;
    IF '{PARAM1}' <> TO_CHAR(l_PARAM1)
    THEN
        RAISE bad_date;
    END IF;
END;

As a simple example of text-style variable substitution:

  • If P1 is a parameter of type NUMBER and has the value 2.1, the string {P1} will be replaced by the string 2.1.

  • If P1 is a parameter of type VARCHAR2 and has the value ABC, the string '{P1}' will be replaced by the string 'ABC'. (Note the single-quotation marks around {P1} in this example.)

You can use variable substitution for all types of validation actions except Compare Tables. For the applicable validation action types, variable substitution is performed as follows:

  • For Query Returning Row(s) and Query Returning No Row(s), substitution is performed on the SQL query.

  • For Compare Query Results, substitution is performed on both the source and target SQL queries.

  • For Boolean Function and User PL/SQL Code, substitution is performed on the PL/SQL block.

3.8 Unit Test Library

The unit testing library enables you to store actions that you can reuse in the definitions of multiple unit tests. These user-defined actions are displayed under the Library node in the Unit Test navigator (which is explained in Section 3.2). You can store the following kinds of actions in the library, in the following categories:

  • Dynamic value queries

  • Startup actions

  • Teardown actions

  • Validation actions

Most categories have subcategories. For example, the Startup Actions node has subnodes for Table or Row Copy and User PL/SQL Code. You can add an entry to the library in the following ways:

  • Expand the Library hierarchy to display the relevant lowest-level node (such as User PL/SQL Code under Startups); right-click and select Add [action-type]; specify a name for the action; click the name of the newly created action; and complete the specification.

  • Use the Publish to Library option when specifying the action when you are creating a unit test: enter a name for the action and click Publish. (The action will be added under the appropriate category and subcategory in the Library display in the Unit Test navigator.)

To use an action from the library when you are creating a unit test, select it from the list under Library on the appropriate page in the Unit Testing: Create Unit Test wizard or when you are editing a unit test. When you select an action from the library, you have the following options for incorporating it into the process (startup, teardown, or validation):

  • Copy: Uses a copy of the action, which you can then edit (for example, to modify the WHERE clause in a User PL/SQL Code procedure). If the action is later changed in the library, it is not automatically re-copied into the process.

  • Subscribe: Uses the action as stored in the library. (You cannot edit the action in the process if you use the Subscribe option.) If the action is later changed in the library, the changed version is automatically used in the process.

3.9 Unit Test Reports

Several SQL Developer reports provide information about operations related to unit testing. These reports are listed in the Unit Test navigator under the Reports node. The available reports include:

  • All Suite Runs

  • All Test Implementation Runs

  • All Test Runs

  • Suite Runs Code Coverage

  • Suite Test Implementation Runs

  • Suite Test Runs

  • Test Implementation Runs

  • Test Runs Code Coverage

  • User Test Runs (test runs grouped by user)

Each unit testing report contains a top pane with a summary information row for each item. To see detailed information about any item, click in its row to display the information in one or more detail panes below the summary information. For example, if you click in a summary row in the All Test Runs report, details about that test run are displayed under the Test Run Details and Most Recent Code Coverage tabs.

Some reports prompt you for bind variables, where you can accept the default values to display all relevant items or enter bind variables to restrict the display. (For more information, see Bind Variables for Reports.)

3.10 Exporting and Importing Unit Test Objects

You can export and import unit tests, suites, and objects that are stored in the library (such as startup, validation, and teardown actions).

Exporting an object causes all dependent objects to be included in the resulting XML file. For example, if you export a suite, the resulting XML file includes all tests in that suite, as well as all startup, validation, and teardown actions within each test in that suite.

To export an object, right-click its name in the Unit Test navigator and select Export to File; then specify the location and name for the XML file that will include the definitions of the objects.

Importing unit test objects from an XML file causes all objects in the file to be created in the appropriate places in the Unit Test navigator hierarchy. If an object already exists in the repository with the same name as an object of the same type in the XML file, it is replaced (overwritten) by the object definition in the XML file.

To import unit test objects, click Tools, then Unit Test, then Import from File; then specify the XML file to be used for the import operation.

3.11 Command-Line Interface for Unit Testing

In addition to running unit tests and suites, and exporting and importing unit test objects, within the SQL Developer graphical interface, you can use the ututil batch file (Windows) or shell script (Linux) on the operating system command line. ututil is located in the sqldeveloper\sqldeveloper\bin folder or sqldeveloper/sqldeveloper/bin directory under the location where you installed SQL Developer.

ututil.bat or ututil.sh accepts these commands: run to run a test or suite, exp to perform an export operation, and imp to perform an import operation. For detailed information about the syntax and options, start by running ututil without any parameters at the system command prompt. For example:

C:\Program Files\sqldeveloper\sqldeveloper\bin>ututil
 
ututil -run ?
ututil -exp ?
ututil -imp ?

Then enter the command for information about the command that you want to use. For example: ututil -run ?

The ututil -run command includes the following parameters:

  • -db <connection name> specifies the database connection associated with the database user to be used for running the unit test.

  • -repo <connection name> specifies the database connection associated with the unit testing repository to be used for running the unit test.

  • {-log <0,1,2,3>} specifies the logging level, where:

    0 = No logging (the default).

    1 = Report the status.

    2 = Report the status and error message.

    3 = Report the status, error message, and return ID value.

  • {-return <return id>} specifies the return ID value, which is used as the primary key in the results table, and which will allow automation tools to query the results from the database.

The following example runs a unit test named AWARD_BONUS in a Windows environment where SQL Developer is installed under Program Files. (Note that test and suite names are case sensitive for the command-line interface.) This example uses the repository connection for user unit_test_repos and runs the test as user fred.

> cd c:\Program Files\sqldeveloper\sqldeveloper\bin
> ututil -run -test -name AWARD_BONUS -repo unit_test_repos -db fred

The following example exports a unit test named AWARD_BONUS. It uses the repository connection for user unit_test_repos and stores the exported definitions in the file C:\ut_xml\award_bonus_test.xml.

> ututil -exp -test -name AWARD_BONUS -repo unit_test_repos -file c:\ut_xml\award_bonus_test.xml

The following example imports object definitions from the file C:\ut_xml\award_bonus_suite.xml. It uses the repository connection for user unit_test_repos.

> ututil -imp -repo unit_test_repos -file c:\ut_xml\award_bonus_suite.xml

To check the results of any tests or suites that you run from the command line, you can start SQL Developer and view the All Test Runs and All Suite Runs reports (see Section 3.9, "Unit Test Reports").

3.12 Example of Unit Testing (Tutorial)

This section presents a simplified example in which you create a table and a PL/SQL procedure, create unit tests with test cases for valid and invalid input data, run the unit tests, and create and run a unit test suite. It assumes that you have a table of employee data that includes salary information, and that you need to create a procedure to award bonuses to sales representatives, whose pay consists of a base salary plus a commission-based bonus.

Note:

An Oracle By Example (OBE) tutorial, Performing a Unit Test of Your PL/SQL in Oracle SQL Developer 2.1, is similar to this one, but it uses a copy of the EMPLOYEES table from the Oracle sample HR schema, which includes more columns and rows as well as different data. For information about SQL Developer OBEs, see the Start Page (click Help, then Start Page).

The EMPLOYEES table includes the following columns, all of type NUMBER:

  • EMPLOYEE_ID: Employee identification (badge) number.

  • COMMISSION_PCT: Commission percentage for the employee: a decimal fraction representing the percentage of the amount of sales by the employee, to be used to compute a bonus that will be added to the employee's base salary to determine the total salary. For example, 0.2 or .2 indicates a 20 percent commission, or 0.2 times the amount of sales.

    Only employees in the Sales department have numeric COMMISSION_PCT values. Other employees (not "on commission") have null COMMISSION_PCT values.

  • SALARY: Salary amount for the employee; includes base salary plus any bonus (which will be calculated by an award_bonus procedure, to be created during this example).

Assume that the following data exists in these columns in the EMPLOYEES table:

EMPLOYEE_ID COMMISSION_PCT SALARY
1001 0.2 8400
1002 0.25 6000
1003 0.3 5000
1004 (null) 10000

You create a procedure named AWARD_BONUS, which has two input parameters:

  • emp_id: The employee ID of an employee.

  • sales_amt: The amount of sales with which the employee is credited for the period in question.

    This amount is calculated using the COMMISSION_PCT value for the specified employee, and the result is added to the SALARY value for that employee.

    If the COMMISSION_PCT is null for the employee, no commission or bonus can be calculated, and an exception is raised. This scenario occurs if an attempt is made to add a commission-based bonus to the salary of an employee who is not in the Sales department.

The rest of this example involves the following major steps:

  1. Create the EMPLOYEES Table.

  2. Create the AWARD_BONUS Procedure.

  3. Create the Unit Testing Repository.

  4. Create a Unit Test.

  5. Run the Unit Test.

  6. Create and Run an Exception Unit Test.

  7. Create a Unit Test Suite.

  8. Run the Unit Test Suite.

3.12.1 Create the EMPLOYEES Table

This tutorial uses a table named EMPLOYEES, which must exist before you run any unit tests of the AWARD_BONUS procedure. This table contains some of the columns used in the HR.EMPLOYEES table that is included in the Oracle-supplied sample schemas, but it does not contain all of the columns, and it contains fewer rows and different data.

You can create this EMPLOYEES table in an existing schema and using an existing database connection, or you can create a new schema and connection for the table. To create and populate this table, enter the following statements in a SQL Worksheet or a SQL*Plus command window:

-- Connect as the database user that will be used to run the unit tests.
-- Then, enter the following statements:
 
CREATE TABLE employees (employee_id NUMBER PRIMARY KEY, commission_pct NUMBER, salary NUMBER);
INSERT INTO employees VALUES (1001, 0.2, 8400);
INSERT INTO employees VALUES (1002, 0.25, 6000);
INSERT INTO employees VALUES (1003, 0.3, 5000);
-- Next employee is not in the Sales department, thus is not on commission.
INSERT INTO employees VALUES (1004, null, 10000);
commit;

3.12.2 Create the AWARD_BONUS Procedure

Create the AWARD_BONUS procedure in the same schema as the EMPLOYEES table. In a SQL Worksheet using the appropriate database connection, enter the following text:

create or replace
PROCEDURE award_bonus (
  emp_id NUMBER, sales_amt NUMBER) AS
  commission    REAL;
  comm_missing  EXCEPTION;
BEGIN
  SELECT commission_pct INTO commission
    FROM employees
      WHERE employee_id = emp_id;
 
  IF commission IS NULL THEN
    RAISE comm_missing;
  ELSE
    UPDATE employees
      SET salary = salary + sales_amt*commission
        WHERE employee_id = emp_id;
  END IF;
END award_bonus;
/

Click the Run Script icon (or press F5) to create the AWARD_BONUS procedure.

3.12.3 Create the Unit Testing Repository

You will need a unit testing repository in the database to hold schema objects that you create and that SQL Developer will maintain. You can create a separate database user for this repository or use the schema of an existing database user; however, to simplify your learning and any possible debugging you may need to do later, it is recommended that you use a separate schema for the unit testing repository, and the instructions in this section reflect this approach.

  1. Create a database user (for example, UNIT_TEST_REPOS) for the unit testing repository. Using a database connection with DBA privileges, right-click Other Users in the Connections navigator and select Create User. Specify UNIT_TEST_REPOS as the user name, and complete any other required information.

    For Default Tablespace, specify USERS; for Temporary Tablespace, specify TEMP.

    For System Privileges, enable CREATE SESSION; then click Apply, then Close.

  2. Create a database connection for the unit testing repository user that you created, as follows. Click Tools, then Unit Test, then Manage Users. In the Select Connection dialog box, click the plus (+) icon to create a new database connection (for example, unit_test_repos) for the unit testing repository user.

    Click Save to save the connection, then Cancel to close the dialog box.

  3. Create the repository in the schema of the user that you created, as follows. Click Tools, then Unit Test, then Select Current Repository. Specify the database connection (for example, unit_test_repos) for the unit testing repository user. When you see a message that no repository exists for that connection, follow the prompts to create a new repository.

    SQL Developer will display several prompts so it can execute commands that grant the necessary privileges to the unit test repository user. In each case, click Yes, and enter the SYS account password when prompted.

3.12.4 Create a Unit Test

To create the first unit test, use the Unit Test navigator. If this navigator is not visible on the left side, click View, then Unit Test. The Unit Test navigator is described in Section 3.2.

  1. In the Unit Test navigator, right-click the Tests node and select Create Test.

    The Unit Testing: Create Unit Test wizard is displayed. In the remaining steps, click Next to go from each step to the next; and when you are finished specifying the unit test, click Finish.

  2. In Select Operation, select the database connection for the schema that you used to create the AWARD_BONUS procedure; then expand the Procedures node and select AWARD_BONUS.

  3. In Specify Test Name, for Test Name specify AWARD_BONUS (same as the procedure name), and select Create with single dummy representation.

  4. In Specify Startup, click the plus (+) icon to add a startup action; and for the action select Table or Row Copy because you want to save the current data values in the EMPLOYEES table before any data is modified by the unit test.

    When prompted, for Source Table specify EMPLOYEES, and for Target Table accept the default name provided for a temporary table that will be automatically created when it is needed and deleted when it is no longer needed. (The target table will be created; and if a table already exists with the name that you specify as the target table, it will be overwritten.)

  5. In Specify Parameters, change the values in the Input column to the following:

    For Parameter EMP_ID: 1001

    For Parameter SALES_AMT: 5000

    For Expected Result, leave the value as Success.

  6. In Specify Validations, click the plus (+) icon and select Query returning row(s).

    For the query, replace the SELECT statement in the Process Validation box with the following (any semicolon at the end of the statement is ignored):

    SELECT * FROM employees
      WHERE employee_id = 1001 AND salary = 9400
    

    That is, because employee 1001 has a 20 percent (0.2) commission and because the sales amount was specified as 5000, the bonus is 1000 (5000 * 0.2), and the new salary for this employee is 9400 (8400 base salary plus 1000 bonus). In this case, the query returns one row, and therefore the result of the validation action is success.

    Note that you could have instead specified the SELECT statement in this step using variable replacement (explained in Section 3.7), as follows:

    SELECT * FROM employees
      WHERE employee_id = {EMP_ID} AND salary = 9400
    

    However, in this specific example scenario, using variable substitution would provide no significant advantage.

  7. In Specify Teardown, select Table or Row Restore because you want to restore the original data values in the EMPLOYEES table before any data was modified by the unit test. When prompted, accept the supplied values for Target Table (EMPLOYEES) and Source Table (the name of the temporary table).

  8. In Summary, review the information. If you need to change anything, click Back as needed and make the changes, then proceed to this Summary page. When you are ready to complete the unit test definition, click Finish.

3.12.5 Run the Unit Test

To run the unit test, use the Unit Test navigator. If this navigator is not visible on the left side, click View, then Unit Test. The Unit Test navigator is described in Section 3.2.

  1. In the Unit Test navigator, expand the Tests node and click the AWARD_BONUS test.

    A pane for the AWARD_BONUS test is displayed, with Details and Results tabs.

  2. On the Details tab, near the top-right corner, select the database connection for the schema that you used to create the AWARD_BONUS procedure.

    Do not change any other values. (However, if you later want to run the unit test with different specifications or data values, you can click the Edit (pencil) icon in the Code Editor toolbar at the top of the pane.)

  3. Click the Run Test (green arrowhead) icon in the Code Editor toolbar (or press F9).

At this point, focus is shifted to the Results tab, where you can soon see that the AWARD_BONUS ran successfully.

If you want to check the EMPLOYEES table data, you will see that the salary for employee 1001 is the same as it was before (8400), because the startup action for the unit test copied the original data to the temporary table and the teardown action restored the original data to the EMPLOYEES table.

3.12.6 Create and Run an Exception Unit Test

Create another unit test for the exception condition where the COMMISSSION_PCT value is null for the employee, and therefore no commission or bonus can be calculated. For this tutorial, the test data includes employee 1004 with a null commission percentage. (This condition could result from several possible scenarios, the most likely being an attempt to run the procedure on a salaried employee who is not eligible for commissions.)

The steps for creating this exception unit test are similar to those in Section 3.12.4, except there are no startup or teardown steps because this test should not modify any table data, and there is no need for any validation action.

  1. In the Unit Test navigator, right-click the Tests node and select Create Test.

    The Unit Testing: Create Unit Test wizard is displayed. Click Next to go from each step to the next; and when you are finished specifying the unit test, click Finish.

  2. In Select Operation, select the database connection for the schema that you used to create the AWARD_BONUS procedure; then expand the Procedures node and select AWARD_BONUS.

  3. In Specify Test Name, for Test Name specify AWARD_BONUS_NO_COMM_EXC, and select Create with single dummy representation.

  4. In Specify Startup, click Next to go to the next page.

  5. In Specify Parameters, change the values in the Input column to the following:

    EMP_ID: 1004

    SALES_AMT: 5000

    For Expected Result, change the value to Exception and leave the expected error number as ANY.

  6. In Specify Validations, click Next to go to the next page.

  7. In Specify Teardown, click Next to go to the next page.

  8. In Summary, review the information. If you need to change anything, click Back as needed and make the changes, then proceed to this Summary page. When you are ready to complete the unit test definition, click Finish.

To run this unit test, follow the steps in Section 3.12.5, except specify AWARD_BONUS_NO_COMM_EXC instead of AWARD_BONUS.

On the Results tab, you will see that the AWARD_BONUS_NO_COMM_EXC test ran successfully; and if you check the EMPLOYEES table data, you will see that the information for employee 1004 (and all the other employees) was not changed.

Design Consideration:

As an alternative to creating a separate unit test for the exception condition, you could add it as an implementation to the AWARD_BONUS test (right-click AWARD_BONUS and select Add Implementation). Thus, the AWARD_BONUS unit test would have two implementations: the "Default" implementation using employee 1001, and the AWARD_BONUS_NO_COMM_EXC implementation using employee 1004.

The approach in this tutorial enables you to create a simple unit test suite using the two unit tests (see Section 3.12.7). However, in more realistic unit testing scenarios, it is probably better to use a unit test for each procedure, add implementations for each test case for a procedure, and group multiple unit tests (for individual procedures) into one or more test suites.

3.12.7 Create a Unit Test Suite

Create a unit test suite that groups together the two unit tests of the AWARD_BONUS procedure. If the Unit Test navigator is not visible on the left side, click View, then Unit Test. The Unit Test navigator is described in Section 3.2.

  1. In the Unit Test navigator, right-click the Suites node and select Add Suite.

  2. In the Unit Testing: Add Test Suite dialog box, specify AWARD_BONUS_SUITE as the suite name.

  3. In the Unit Test navigator, under Suites, click the AWARD_BONUS_SUITE node.

    An pane for the AWARD_BONUS_SUITE test suite is displayed.

  4. Do not specify a Startup Process or Teardown Process, because neither is needed for this test suite.

  5. Click the Add (+) icon to add the first test to the suite.

  6. In the Unit Testing: Add Tests or Suites to a Suite dialog box, click (select) AWARD_BONUS, check (select) Run Test Startups and Run Test Teardowns so that the startup and teardown actions for that unit test will be run, and click OK.

  7. Click the Add (+) icon to add the next test to the suite.

  8. In the Unit Testing: Add Tests or Suites to a Suite dialog box, click (select) AWARD_BONUS_NO_COMM_EXC, and click OK. (The check Run Test Startups and Run Test Teardowns options are irrelevant here because the AWARD_BONUS_NO_COMM_EXC test does not perform any startup and teardown actions.)

  9. Click the Commit Changes icon in the Code Editor toolbar at the top of the pane (or press F11).

3.12.8 Run the Unit Test Suite

To run the unit test suite, use the Unit Test navigator. If you are in the editing pane for the AWARD_BONUS_SUITE test suite, run the suite by clicking the Run Suite (green arrowhead) icon in the Code Editor toolbar. Otherwise, perform the following steps:

  1. In the Unit Test navigator, expand the Suites node and click the AWARD_BONUS_SUITE test suite.

    A pane for the AWARD_BONUS_SUITE test is displayed, with Details and Results tabs.

  2. In the Details tab, near the top-right corner, select the database connection for the schema that you used to create the AWARD_BONUS procedure.

    Do not change any other values. (However, if you later want to run the unit test suite with different specifications, you can click the Edit (pencil) icon in the Code Editor toolbar at the top of the pane.)

  3. Click the Run Suite (green arrowhead) icon in the Code Editor toolbar (or press F9).

After the suite is run, focus is shifted to the Results tab, where you can soon see that the AWARD_BONUS_SUITE test suite ran successfully.