Toad World® Forums

Test just a subset of fields of a record


#1

We received this request: “My function returns a record, but I must test the contents of only some of the fields and ignore others. How can I do this?”

Code Tester currently offers “built-in” support (no customization needed) to test for equality or inequality entire records.

It is relatively easy, however, to achieve the desired result. Suppose I want to build a test for this program:

CREATE OR REPLACE FUNCTION QCTO.get_employee
RETURN testemp%ROWTYPE
IS
l_row testemp%ROWTYPE;
BEGIN
SELECT * INTO l_row FROM testemp;

RETURN l_row;

END;
/

built on this table:

CREATE TABLE TESTEMP
(
EMPLOYEE_ID INTEGER,
LAST_NAME VARCHAR2(100 BYTE)
)

and just a single row of data.

So I create a test definition with a single test case that has “record equality” outcome. I save the test. At this point, Code Tester automatically generates an equality comparison function.

I now open up the test definition again, navigate to Outcomes for the test case and right click under Expected Results and select Edit Equality Function. I see this:

FUNCTION “q$1” (r1 IN qcto.testemp%ROWTYPE,
r2 IN qcto.testemp%ROWTYPE,
nulls_eq IN BOOLEAN DEFAULT TRUE)
RETURN BOOLEAN
IS
retval BOOLEAN;
BEGIN
retval :=
( r1.employee_id = r2.employee_id
OR (nulls_eq AND r1.employee_id IS NULL AND r2.employee_id IS NULL))
AND ( r1.last_name = r2.last_name
OR (nulls_eq AND r1.last_name IS NULL AND r2.last_name IS NULL));
RETURN retval;
END “q$1”;

I then remove the first clause that references employee ID, leaving only:

FUNCTION “q$1” (r1 IN QCTO.TESTEMP%ROWTYPE,
r2 IN QCTO.TESTEMP%ROWTYPE,
nulls_eq IN BOOLEAN DEFAULT TRUE)
RETURN BOOLEAN
IS
retval BOOLEAN;
BEGIN
retval :=
( r1.LAST_NAME = r2.LAST_NAME
OR (nulls_eq AND r1.LAST_NAME IS NULL AND r2.LAST_NAME IS NULL));
RETURN retval;
END “q$1”;

Save the changes, run the test and all works as desired.

Bottom line: we’d certainly prefer to give you a 100% UI-based click-driven way to accomplish this, but at least you don’t have to write all the comparison logic!


#2

How do I test just a subset of fields returned by a cursor variable?

Could you show this using the tutorial FUNCTION qctod#rows_from_table?

Thank you.