Quest Code Tester for Oracle

Compare the contents of a record to a query or another record

A record is the PL/SQL analogue of a table's row. It consists of one or more fields. These fields can be scalars or more complex structures, such as collections or objects. This tutorial shows how you can easily set up a test to compare the record of scalars changed by your program to a query and also to a control record.

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.

Steps

From the Test Dashboard, create a new test definition by clicking Next on the Test Definition for program right-click menu. Select qctod#test_records from the list and press OK. Click OK on the Welcome to the Test Builder dialog. Now you are inside Test Builder.

From the list of programs in Program Details, click on FETCH_INTO_ROWTYPE. to make sure this is the current focus. Let's test this one first.

Set up the test cases

Before diving into the details of each of the test cases, use "top down design" to brainstorm the various test cases that you will need. You do this in the Test Case list. Press the "Add test case" button and create a new test case named "Record against query." Now it is time to provide the details of that test case.

Record against query

In the Inputs Grid, enter 100 for EMPLOYEE_ID_IN.
In the Outcome grid, you should see an outcome already defined to compare one record to another. If different from this, change the settings to match this. Then we will make some changes to the Expected Results side of things.

For the Expected Results Type of Data, choose Query. We are going to compare the contents of this record to the first row retrieved by a query.
Click on […] in the Expected Results value field and enter this text for query:

select * from qctod#employees where employee_id = 100

Press OK, and time to move on to the next test case.

Record against query – different column names

The last test case was a very smooth operation….I simply provide a query and it matches up ever so nicely with the record (same number of columns, same names, etc.). Let's now try something a little more complicated.

Click on Program Details tab in upper left corner and change program to NAME_AND_SALARY. This function returns a record consisting of just two columns, not the entire row from the table.

Create a new test case using the new test case button, and change the name to: "Name and salary for employee 100" in the input grid.
Enter 100 for EMPLOYEE_ID_IN.
In the Outcome grid, you should see an outcome already defined to compare one record to another. If different from this, change the settings to match this. Then we will make some changes to the Expected Results side of things.

For Expected Results Type of Data, choose Query. We are going to compare the contents of this record to the first row retrieved by another query.
Click on […] in the expected results value field and enter this text for query:

select last_name lname, salary sal from qctod#employees
where employee_id = 100

Notice that I have selected just the two columns in which I am interested and also I have changed the names of the columns returned by the query to match the names of the fields in the record.

Press OK, and time to move on to the next test case.

Note: if you do not provide aliases for column names so that they match the names of the record fields, you will see compile errors in your test code.

If this happens, navigate to the outcome in Test Editor and change the query associated with the Expected Value tab for that outcome.

Record against record with "records equal" function

Now let's take a look at how you can compare one record to another record, and also take advantage of a "records equal" function. This way, you do not have to rely on a table or query to define the structure of your record, and you can test records whose fields contain one or more non-SQL datatypes (such as a Boolean).

Sticking with the same NAME_AND_SALARY function, create a new test case by clicking the new test case icon and changing the name to "Employee 100 against custom record".
In the Input Grid set input value to 100
In the Outcome grid, you should see an outcome already defined to compare one record to another. If different from this, change the settings to match this. Then we will make some changes to the Expected Results side of things.

Press […] to open Properties window for expected results value.
Notice the name of the variable in the top left corner. This is the name of the local variable that we will declare for you in the generated code. You can generally leave it unchanged, but you will be using it in a moment. So we usually copy it into the clipboard.

To the right, we have the "Are-Elements-Equal Function" section. As with collections, Quest Code Tester needs some information from you. There is no way built into PL/SQL to (a) figure out the fields of a user-defined record and (b) compare one record to another, even if you know the actual type of the record in advance (which we do not). So you have to give us the name and/or implementation of that function.
Good news! It turns out, that the qctod#test_records package has such a function, called EQ_NAME_SALARY, so I put the following text into the Function Name field:

qctod#TEST_RECORDS.eq_name_and_salary

I also need to fill up the expected record (variable name above) with data to match what I expect to be found in the record returned by the program. So I put the following code into the Initialization section:

e_Function_return_value1.lname := 'King';

e_Function_return_value1.sal := 24000;

Press OK and then you are done!

Time to save/run your test!

You can now save your test definition and run your test with a single press of the Save, Close, and Run button in the Test Cases toolbar:

You will then see the results in the Results Viewer. After reviewing your results, close the Results Viewer.

Source code of program tested: Compile this code in your schema so that you can build your test definition for it.

CREATE OR REPLACE PACKAGE qctod#test_records
IS
   -- Note that the names of the record must match
   TYPE name_salary_rt IS RECORD (
      lname qctod#employees.last_name%TYPE
    , sal qctod#employees.salary%TYPE
   );

   -- Show how to test a table-based record contents.
   -- Remember that the assertion code only fetches the first
   -- row identified by the query against the table!
   PROCEDURE fetch_into_rowtype (
      employee_id_in IN qctod#employees.employee_id%TYPE
    , row_out OUT qctod#employees%ROWTYPE
   );

   --  Show how to test a user-defined record contents.
   --  Remember that the assertion code only fetches the first
   --  row identified by the query against the table!
   --  A SELECT FROM dual works nicely. You need to include column aliases
   --  in the select list to make sure the generated assertion code
   --  creates a valid equality check for the record.
   FUNCTION name_and_salary (
      employee_id_in IN qctod#employees.employee_id%TYPE
   )
      RETURN name_salary_rt;

   FUNCTION eq_name_and_salary (
      rec1 name_salary_rt
    , rec2 name_salary_rt
    , nulls_ok BOOLEAN
   )
      RETURN BOOLEAN;
END qctod#test_records;
/

SHO ERR

CREATE OR REPLACE PACKAGE BODY qctod#test_records
IS
   PROCEDURE fetch_into_rowtype (
      employee_id_in IN qctod#employees.employee_id%TYPE
    , row_out OUT qctod#employees%ROWTYPE
   )
   IS
      l_row qctod#employees%ROWTYPE;
      empty_row qctod#employees%ROWTYPE;
   BEGIN
      SELECT *
        INTO l_row
        FROM qctod#employees
       WHERE employee_id = employee_id_in;

      row_out := l_row;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         row_out := empty_row;
   END fetch_into_rowtype;

   FUNCTION name_and_salary (
      employee_id_in IN qctod#employees.employee_id%TYPE
   )
      RETURN name_salary_rt
   IS
      l_row name_salary_rt;
      empty_row name_salary_rt;
   BEGIN
      SELECT last_name
           , salary
        INTO l_row
        FROM qctod#employees
       WHERE employee_id = employee_id_in;

      RETURN l_row;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN empty_row;
   END name_and_salary;

   FUNCTION eq_name_and_salary (
      rec1 name_salary_rt
    , rec2 name_salary_rt
    , nulls_ok BOOLEAN
   )
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN     (   rec1.lname = rec1.lname
                  OR (rec1.lname IS NULL AND rec1.lname IS NULL)
                 )
             AND (   rec1.sal = rec1.sal
                  OR (rec1.sal IS NULL AND rec1.sal IS NULL)
                 );
   END eq_name_and_salary;
END qctod#test_records;
/

SHO ERR