| ||||||||||||||||||||||||||||
|
Test inserts into a table with a query, and Quick Build
When inserting data, you can't always check the contents of an entire table, or even an entire row in a table, since the table will have generated primary keys and audit columns. Instead, you will want to extract just the data you need to check with a query. This tutorial shows you how to compare one query to another, and also use Quick Build to accelerate the definition of your tests.
Flash tutorial explaining the topic: A brief video that shows you how to use Quest Code Tester to solve the problem posed by this topic. Step-by-step instructions: Follow these steps to replicate what we show you in the tutorial. From the Test Dashboard, create a new
test definition by
selecting New on the Test
Definition
for Program right-click menu. Select qctod#insert_employee from the
list and
press OK. Click OK
on the Welcome to the Test Builder Dialog. Now you are inside
Test Builder. Set up the test cases
Testing the impact of DML statements
like inserts can be
very complicated and time-consuming – unless you are using
Quest Code Tester!
And what makes this sort of testing especially easy is the Quick Build
feature
of Test Builder. Quick Build offers pre-defined "templates" of test
cases for various scenarios. Use Quick Build
Put your cursor over the Quick Build
tab to the left of Test
Builder (the default position for Quick Build is pinned,
so it may already be visible and fixed within Test
Builder). Since your are testing the insert of a single row, drill
down into the Insert Operations branch to the "Insert one row" group.
What you see are a set of test case templates that one would
normally want to test for when performing an insert: insert a row
successfully,
unsuccessfully (in general) and for specific, common insert-related
errors. Click on "Insert one row" and choose "Add test cases to group". Click OK on the confirmation window, and choose the table whose contents you want to test, and choose " QCTOD#DMR_EMPLOYEES". Press OK, and then your test case list will hold a line for each template. Now we can move through each of the
test cases and finalize
the details. For each of the headers below, assume that I (and you)
have
clicked onto the test case of that name. For this demonstration, I will
complete only three test
cases and remove the rest. You can define more of these if you would
like. Insert one row successfully
SELECT
last_name, salary FROM qctod#employees
WHERE first_name='Jolly'
SELECT
'Tester', 100000 FROM dual Notice
that I am setting up a
"dummy" query here of sorts. This is an easy way to validate the
results of a single row query.
Insert a
row unsuccessfully - the table has not changed at all. In this test case, we will try to
insert an employee with
the last name of 'FEUERSTEIN'. that violates a unique index on the
email (SKING
is the email "address" of Steven King).
Exception
DUP_VAL_ON_INDEX. In this test case, we will try to
insert an employee that
violates a unique index on the email (SKING is the email "address" of
Steven King).
Time to run your test!
You can now save your test definition
and run your test with
a single press of the Save and Run button in the Test Cases toolbar. Source code of program tested: Compile this code in your schema so that you can build your test definition for it. CREATE OR REPLACE PROCEDURE HR.qctod#insert_employee (
fname_in IN VARCHAR2
, lname_in IN VARCHAR2
, email_in IN VARCHAR2
, hdate_in IN DATE
, job_id_in IN VARCHAR2
, salary_in IN NUMBER
)
IS
l_seq PLS_INTEGER;
BEGIN
IF lname_in = 'FEUERSTEIN'
THEN
NULL;
ELSE
SELECT MAX (employee_id)
INTO l_seq
FROM qctod#employees;
INSERT INTO qctod#employees
(employee_id, first_name, last_name, email, hire_date
, job_id, salary
)
VALUES (l_seq + 1, fname_in, lname_in, email_in, hdate_in
, job_id_in, salary_in
);
END IF;
END qctod#insert_employee;
/ |
||||||||||||||||||||||||||||