As an alternative to specifying exact input data when creating a unit test, you can create a dynamic value query to use seed 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 sues 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 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 seed 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_WITH_SEED_DATA, and select Seed/create implementations using lookup values.
(You will not actually be using lookup values in this case, but this choice enables you to pass quickly through the remaining wizard steps before editing the unit test to use a dynamic value query.)
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.
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_WITH_SEED_DATA 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, the query returns no rows), the result of the validation action is success.
Run the AWARD_BONUS_WITH_SEED_DATA unit test. (For the basic steps to run a unit test, see Run the Unit Test.)
Related Topics