Toad World® Forums

The equality function for testing collections


#1

PL/SQL can sometimes be a very frustrating language. It is wonderful in so many ways, but it is sorely lacking in “reflection” features (the ability at runtime to get information about the structure of variables). Here is an example, relevant to testing collections: to see if two collections of records are equal, I need to compare those records. There is no generic way in PL/SQL to do that. I need to get the information about each field and construct a function.

Since we cannot do that for you, when you set up a test for a collection, you need to provide the function to perform this check. It can be defined in your package or you can provide the code directly in the outcome definition form.

I expect that very soon we will let you specify the structure of your collection (and same for records) by pointing to a table or query or even supplying a series of name-datatype pairs in a grid.

Do you have other ideas of how to address this?


#2

Hello Steven,

We could also point to stored record types. Also if using the name-datatype touples solution we can store this in xml.

Regards,


#3

By stored record types, I assume you mean a user defined record type in a package spec. Yes, we can use this – when we integrate a much better parser into Qute. Should happen soon…


#4

Steve,

Glad to be able to finally get back to evaluating this fantastic tool you’ve created for us.

I have an urgent need to be able to compare specific field values of user defined record types used as in/out parameters.

Do you have an ETA on when this may be available?

I trust you are well. Regards Scott


#5

Scott,

I do not know when we will offer the ability through the UI itself to select out individual fields of a record for testing, but you CAN test this right now.

Consider this function:

CREATE OR REPLACE PACKAGE qctod#test_records
IS
TYPE name_salary_rt IS RECORD (
lname qctod#employees.last_name%TYPE
, sal qctod#employees.salary%TYPE
);

FUNCTION name_and_salary (
employee_id_in IN qctod#employees.employee_id%TYPE
)
RETURN name_salary_rt;

I can define an outcome as follows:

Changed by Program
Type of data: Expression

Then in CBP […] I set the datatype to NUMBER and type this in the code area:

“Value From Function”.sal

I can then define the rest of my outcome as a test on this particular scalar value.

Having said all this, clearly we need to provide some better guidance. How are you supposed to know that the name of the variable is “Value From Function”, and so on?

But in the meantime, this should work.

Regards, Steven


#6

I have created a procedure which will generate an equality function for a specified user-defined (record) type. This may be of use to some of you until the Quest Code Tester is expanded to provide this functionality.

Steven, is there any reason why you intend to incorporate an interface to allow the user to generate equality functions, rather than just building them in the background as part of the unit test package?

Here is the code - let me know if you find any issues or have any suggestions for improvements…

CREATE OR REPLACE PROCEDURE gen_record_equality_fn
( p_function_name IN VARCHAR2
, p_type_name IN VARCHAR2
, p_owner IN VARCHAR2 := USER
)
IS

 /* Cursor used to identify fields in the specified record type */
 CURSOR c_type_attributes
 (  p_type_name     IN  VARCHAR2
 ,  p_owner         IN  VARCHAR2
 )
 IS
    SELECT   LOWER(attr_name) attr_name
    ,        attr_no
    FROM     all_type_attrs
    WHERE    type_name = p_type_name
    AND      owner     = p_owner
    ORDER BY attr_no;
   
 s_function            CLOB;  

BEGIN

 /* Setup the CREATE FUNCTION script */
 s_function  := 'CREATE OR REPLACE FUNCTION ' || LOWER(p_function_name) || CHR(10)
             || '(  p_record_1 IN ' || LOWER(p_type_name) || CHR(10)
             || ',  p_record_2 IN ' || LOWER(p_type_name) || CHR(10)
             || ',  p_null_eq  IN BOOLEAN DEFAULT TRUE' || CHR(10)
             || ') RETURN BOOLEAN' || CHR(10)
             || 'IS' || CHR(10)
             || 'BEGIN' || CHR(10) || CHR(10);
            
 /* Generate the comparison of records by checking the equality of each field
 || (using the c_type_attributes cursor to loop round each field)
 */              
 <<record_fields>>
 FOR r_attr IN c_type_attributes(p_type_name, p_owner) LOOP

    /* For first field, create the IF condition */
    IF r_attr.attr_no = 1 THEN
       s_function := s_function
                  || '   IF  ';
    /* For subsequent fields, AND into the IF statement */
    ELSE
       s_function := s_function
                  || '   AND ';
    END IF;
                 
    /* Generate condition to either check that the field values are identical for the two records
    || or that the field values are NULL and NULLS should be classed as equal based on parameter
    || passed into the function
    */
    s_function := s_function
               ||        '(   (p_record_1.' || r_attr.attr_name || ' = p_record_2.' || r_attr.attr_name || ')' || CHR(10)
               || '        OR (p_null_eq AND p_record_1.' || r_attr.attr_name || ' IS NULL AND p_record_2.' || r_attr.attr_name || ' IS NULL)' || CHR(10)
               || '       )' || CHR(10);
   
 END LOOP record_fields;
                           
 s_function := s_function
             /* Add the code to handle the result of the IF statement */
             || '   THEN' || CHR(10)
             || '      /* records match */' || CHR(10)
             || '      RETURN TRUE;' || CHR(10)
             || '   ELSE' || CHR(10)
             || '      /* records do not match, return failure */ ' || CHR(10)
             || '      RETURN FALSE; ' || CHR(10)
             || '   END IF; ' || CHR(10) || CHR(10)
             /* Add an exception handler. 
             || THIS CURRENTLY SIMPLY RETURNS A NON-MATCH IF AN ERROR OCCURRS
             || - MAY NEED TO BE MADE MORE SOPHISTICATED IN FUTURE VERSIONS
             */
             || 'EXCEPTION' || CHR(10)
             || '   WHEN OTHERS THEN' || CHR(10)
             || '      RETURN FALSE; ' || CHR(10) || CHR(10)
             || 'END ' || LOWER(p_function_name) || ';';

 /* Run the CREATE FUNCTION script */
 EXECUTE IMMEDIATE(TO_CHAR(s_function));

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
RAISE;

END gen_record_equality_fn;
/

Steve.


#7

Steve,

I am very impressed. This looks very good.

Now, having said that, I have a confession to make: I have already written a function like this and exposed it in the qu_helper package that you will find in the Library (click on Browse the Library to the right).

But in 1.6 (about to release a beta version) we are moving this logic inside the tool, so the user can press a button and get the code generated.

You are asking why we do this rather than rather than just building them in the background as part of the unit test package…

Here’s the problem: we can generate valid functions for many common record structures, but in lots of cases (a record with a collection as a field type) the user MUST change the generated code to get it to work.

So we don’t feel comfortable black-boxing this stuff.

SF


#8

Steven,

I need to test a package that returns a record, but only wish to check one field in the record as well.

I wish to use the method you described:

"I can define an outcome as follows:

Changed by Program
Type of data: Expression

Then in CBP […] I set the datatype to NUMBER and type this in the code area:

“Value From Function”.sal

I can then define the rest of my outcome as a test on this particular scalar value.

Having said all this, clearly we need to provide some better guidance. How are you supposed to know that the name of the variable is “Value From Function”, and so on?

But in the meantime, this should work. "

How, indeed, are you supposed to know the name of “Value From Function”? I tried using the l_result variable, but I could not get it to work right.

Could you elaborate on how you would go about finding the name of the variable? (Perhaps it just involves digging through the generated Test Code or it has a standard varable name?)

Thanks for the help,

Josh


#9

I answered this in the other post Josh entered in this forum, since this was already marked as answered.