Home > SQL Developer: Unit Testing > Using a Dynamic Value Query...
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 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:
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;
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.
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.
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.
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.
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.)
In Specify Validations, click Next to go to the next page.
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).
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.
In the Unit Test navigator, click the node for AWARD_BONUS_DYN_QUERY under Tests, to display the test in an editing window.
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;
For Expected Result, leave the value as Success
.
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.
Run the AWARD_BONUS_DYN_QUERY unit test. (For the basic steps to run a unit test, see Run the Unit Test.)
Related Topics