3 SQL Developer: Unit Testing

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:

• Go to Section 3.12, "Example of Unit Testing (Tutorial)" and follow the steps, and then return to read the remaining conceptual information under SQL Developer: Unit Testing.

• Read the remaining conceptual information under SQL Developer: Unit Testing, finishing with Section 3.12, "Example of Unit Testing (Tutorial)".

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

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.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.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.

• 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).

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.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.

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:

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.

-- 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;

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;
/