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.)
SQL Developer User Interface for Unit Testing
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.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 Repository
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.17.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
Using a Dynamic Value Query to Create a Unit Test
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.
■ 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.
Using a Dynamic Value Query to Create a Unit Test
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;