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 example:
■ 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 Using the Command-Line Interface
In addition to running unit tests and suites, and exporting and importing unit test objects, within the SQL Developer graphical interface, you 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 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.
> 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: 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).