| |
|
Defining the "elements equal" function for record comparisons
You will, under a variety of circumstances, need to provide Quest Code Tester for Oracle with the name of a function that it can use to determine if one record is equal to another. This function is needed to compare records and also arises commonly to compare the contents of collections, when you are working with collections of records. This tutorial explores your options in specifying and defining this function. In addition to the helper package offered in the code section, I will be referring to the qctod#test_records function.
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. Please see the video for details.
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 qu_helper AUTHID CURRENT_USER
/*
Quest Code Tester for Oracle Helper Package
Quest Software, Inc.
www.quest.com
Author: Steven Feuerstein
Created: February 2007
Purpose: provide helper programs to Quest Support and users
to help them make the most of Quest Code Tester, and resolve
issues as quickly and easily as possible.
*/
IS
/*
"Starter program" that contains all the code needed to
walk down the fi;; test definition hierarchy. You will generally
want to copy this code to you own program and then add
the action you want to take at each level.
*/
PROCEDURE walk_the_hierarchy (harness_guid_in IN VARCHAR2);
/*
Walk through all the elements of the specified test
definition and identify/show all non-null customization sections
Pass TRUE for second parameter if you want to see the actual code
and not just an indicator that this section contains code.
Two versions: supply GUID or owner/program combo.
*/
PROCEDURE show_cust_code (
harness_guid_in IN VARCHAR2
, show_code_in IN BOOLEAN DEFAULT FALSE
);
PROCEDURE show_cust_code (
owner_in IN VARCHAR2
, program_name_in IN VARCHAR2
, show_code_in IN BOOLEAN DEFAULT FALSE
);
/*
Show the contents of the trace log, qu_log.
*/
PROCEDURE show_log (
since_in IN DATE DEFAULT NULL
, filter_context_in IN VARCHAR2 DEFAULT '%'
, filter_text_in IN VARCHAR2 DEFAULT '%'
);
/*
Show schemas with some or all of Quest Code Tester installed in it.
You should be connected as a SysDBA account if you really want to
find all installations.
*/
PROCEDURE show_installs;
/*
Generate a records-equal function for the specified table or query.
If the dataset argument starts with the word SELECT, we treat it as a
query. Obviously, if your argument is a non-SQL datatype like a Boolean,
you will need to make an adjustment here and there in the generated code.
If you do not pass a value for rowtype_in, then dataset_in%ROWTYPE will
be used, that is we assume that the name of the table passed in the first
argument will be available for reference when you test your code.
Pass TRUE for the fourth argument if you want prefix CREATE OR REPLACE
to the function definition. Pass FALSE if you intend to place this
code inside the test definition.
Example using qctod#test_records:
CREATE TABLE name_salary_table (
lname VARCHAR2(100)
, sal NUMBER
)
/
BEGIN
qu_helper.gen_eq_function
(dataset_in => 'name_salary_table'
, function_name_in => 'eq_lname_sal'
, rowtype_in => 'qctod#test_records.name_salary_rt'
, prefix_cor_in => FALSE
);
END;
/
And this is what is generated:
FUNCTION eq_lname_sal (
record1_in IN qctod#test_records.name_salary_rt
, record2_in IN qctod#test_records.name_salary_rt
, nulls_eq_in IN BOOLEAN DEFAULT TRUE
)
RETURN BOOLEAN
IS
BEGIN
RETURN ( record1_in.lname = record2_in.lname
OR ( record1_in.lname IS NULL
AND record2_in.lname IS NULL
AND nulls_eq_in
)
)
AND ( record1_in.sal = record2_in.sal
OR ( record1_in.sal IS NULL
AND record2_in.sal IS NULL
AND nulls_eq_in
)
);
END eq_lname_sal;
*/
PROCEDURE gen_eq_function (
dataset_in IN VARCHAR2
, function_name_in IN VARCHAR2 DEFAULT 'records_equal'
, rowtype_in IN VARCHAR2 DEFAULT NULL
, prefix_cor_in IN BOOLEAN DEFAULT TRUE
);
/*
This program clears out the attributes rows for inputs, to avoid errors like:
Could not convert variant of type (String) into type (Double).
when saving changes in Test Builder. Besides seeing this error, the callstack
in the error report should show that you are saving inputs, with lines like:
00a9bda0 QuestCodeTesterOracle.exe uTInput 374 TInput.SaveInput
00a9cfff QuestCodeTesterOracle.exe uTInput 594 TColInput.SaveToDB
*/
PROCEDURE clear_input_attributes;
/*
Want to run a test from the command line? This program shows how to make that
call and also to iterate through and display the results.
*/
PROCEDURE run_test_show_results (
owner_in IN VARCHAR2
, program_name_in IN VARCHAR2
);
/*
Show all the privileges for the currently connected schema.
SERVEROUTPUT must be turned on!
*/
PROCEDURE show_privileges;
END qu_helper;
/
CREATE OR REPLACE PACKAGE BODY qu_helper
/*
Quest Code Tester for Oracle Helper Package
Quest Software, Inc.
www.quest.com
Author: Steven Feuerstein
Created: February 2007
*/
IS
PROCEDURE walk_the_hierarchy (harness_guid_in IN VARCHAR2)
IS
l_harness qu_harness_tp.qu_harness_rt
:= qu_harness_qp.onerow (harness_guid_in);
PROCEDURE show_header (level_in IN VARCHAR2, NAME_IN IN VARCHAR2)
IS
BEGIN
qu_runtime.pl (RPAD ('=', 80, '='));
qu_runtime.pl
( CASE level_in
WHEN qu_result_xp.c_harness
THEN 'TEST DEFINITION'
WHEN qu_result_xp.c_unit_test
THEN 'UNIT TEST'
WHEN qu_result_xp.c_test_case
THEN 'TEST CASE'
WHEN qu_result_xp.c_test_element
THEN 'TEST ELEMENT'
ELSE level_in
END
|| ' "'
|| NAME_IN
|| '"'
);
END show_header;
PROCEDURE show_attribute (
usage_type_in IN VARCHAR2
, parent_guid_in IN VARCHAR2
)
IS
l_attribute qu_attributes_tp.qu_attributes_rt;
BEGIN
l_attribute :=
qu_attributes_qp.onerow (parent_guid_in, usage_type_in);
show_header (qu_result_xp.c_attribute
, l_attribute.argument_alias
|| '-'
|| l_attribute.datatype_declare
);
END show_attribute;
PROCEDURE walk_inputs (tc_in IN qu_test_case_tp.qu_test_case_rt)
IS
l_inputs qu_input_tp.qu_input_tc;
BEGIN
l_inputs := qu_input_qp.ar_fk_input_case (tc_in.universal_id);
FOR inp_index IN 1 .. l_inputs.COUNT
LOOP
show_header (qu_result_xp.c_input
, l_inputs (inp_index).argument_alias
|| '-'
|| l_inputs (inp_index).universal_id
);
show_attribute (qu_outcome_xp.c_input
, l_inputs (inp_index).universal_id
);
END LOOP;
END walk_inputs;
PROCEDURE walk_outcomes (tc_in IN qu_test_case_tp.qu_test_case_rt)
IS
l_outcomes qu_outcome_tp.qu_outcome_tc;
BEGIN
l_outcomes := qu_outcome_qp.ar_fk_outcome_case (tc_in.universal_id);
FOR oc_index IN 1 .. l_outcomes.COUNT
LOOP
show_header (qu_result_xp.c_outcome
, l_outcomes (oc_index).NAME
|| '-'
|| l_outcomes (oc_index).universal_id
);
show_attribute (qu_outcome_xp.c_result
, l_outcomes (oc_index).universal_id
);
show_attribute (qu_outcome_xp.c_control
, l_outcomes (oc_index).universal_id
);
END LOOP;
END walk_outcomes;
PROCEDURE walk_test_cases (ut_in IN qu_unit_test_tp.qu_unit_test_rt)
IS
l_test_cases qu_test_case_tp.qu_test_case_tc;
BEGIN
l_test_cases :=
qu_test_case_qp.ar_fk_qu_test_guid (ut_in.universal_id);
FOR tc_index IN 1 .. l_test_cases.COUNT
LOOP
show_header (qu_result_xp.c_test_case
, l_test_cases (tc_index).NAME
);
walk_inputs (l_test_cases (tc_index));
walk_outcomes (l_test_cases (tc_index));
END LOOP;
END walk_test_cases;
PROCEDURE walk_test_elements (ut_in IN qu_unit_test_tp.qu_unit_test_rt)
IS
l_test_elements qu_test_element_tp.qu_test_element_tc;
BEGIN
l_test_elements :=
qu_test_element_qp.ar_fk_test_id (ut_in.universal_id);
FOR te_index IN 1 .. l_test_elements.COUNT
LOOP
show_header (qu_result_xp.c_test_element
, l_test_elements (te_index).NAME
);
show_attribute (qu_outcome_xp.c_test_element
, l_test_elements (te_index).universal_id
);
END LOOP;
END walk_test_elements;
PROCEDURE walk_unit_tests (harness_in IN qu_harness_tp.qu_harness_rt)
IS
l_unit_tests qu_unit_test_tp.qu_unit_test_tc;
BEGIN
l_unit_tests :=
qu_unit_test_qp.ar_fk_qu_utest_hrn (harness_in.universal_id);
FOR ut_index IN 1 .. l_unit_tests.COUNT
LOOP
show_header
(qu_result_xp.c_unit_test
, l_unit_tests (ut_index).program_type
|| '-'
|| l_unit_tests (ut_index).program_name
|| CASE
WHEN l_unit_tests (ut_index).overload IS NULL
THEN NULL
ELSE '-'
END
);
walk_test_elements (l_unit_tests (ut_index));
walk_test_cases (l_unit_tests (ut_index));
END LOOP;
END walk_unit_tests;
BEGIN
show_header (qu_result_xp.c_harness, l_harness.NAME);
walk_unit_tests (l_harness);
END walk_the_hierarchy;
PROCEDURE show_cust_code (
harness_in IN qu_harness_tp.qu_harness_rt
, show_code_in IN BOOLEAN DEFAULT FALSE
)
IS
l_code_found BOOLEAN;
PROCEDURE show_header (level_in IN VARCHAR2, NAME_IN IN VARCHAR2)
IS
BEGIN
qu_runtime.pl (RPAD ('=', 80, '='));
qu_runtime.pl ( CASE level_in
WHEN qu_result_xp.c_harness
THEN 'TEST DEFINITION'
WHEN qu_result_xp.c_unit_test
THEN 'UNIT TEST'
WHEN qu_result_xp.c_test_case
THEN 'TEST CASE'
WHEN qu_result_xp.c_outcome
THEN 'OUTCOME'
END
|| ' "'
|| NAME_IN
|| '"'
);
END show_header;
PROCEDURE summarize (code_found_in IN BOOLEAN)
IS
BEGIN
IF NOT code_found_in
THEN
qu_runtime.pl ('X No customization code found.');
END IF;
END summarize;
PROCEDURE show_cust_for (
level_in IN VARCHAR2
, type_in IN VARCHAR2
, code_in IN CLOB
, code_found_inout IN OUT BOOLEAN
)
IS
-- Note: no longer using level_in here.
BEGIN
IF qu_config.is_really_null (code_in)
THEN
-- Just return the current IN OUT value....
NULL;
ELSE
code_found_inout := TRUE;
qu_runtime.pl ('> ' || type_in || ' contains code.');
IF show_code_in
THEN
qu_runtime.pl (code_in);
END IF;
END IF;
END show_cust_for;
PROCEDURE show_outcomes (tc_in IN qu_test_case_tp.qu_test_case_rt)
IS
l_code_found BOOLEAN;
l_outcomes qu_outcome_tp.qu_outcome_tc;
BEGIN
l_outcomes := qu_outcome_qp.ar_fk_outcome_case (tc_in.universal_id);
FOR oc_index IN 1 .. l_outcomes.COUNT
LOOP
l_code_found := FALSE;
show_header (qu_result_xp.c_outcome
, l_outcomes (oc_index).NAME
|| '-'
|| l_outcomes (oc_index).universal_id
);
show_cust_for (qu_result_xp.c_outcome
, 'Setup'
, l_outcomes (oc_index).setup_code
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_outcome
, 'Teardown'
, l_outcomes (oc_index).teardown_code
, code_found_inout => l_code_found
);
summarize (l_code_found);
END LOOP;
END show_outcomes;
PROCEDURE show_test_cases (ut_in IN qu_unit_test_tp.qu_unit_test_rt)
IS
l_code_found BOOLEAN;
l_test_cases qu_test_case_tp.qu_test_case_tc;
BEGIN
l_test_cases :=
qu_test_case_qp.ar_fk_qu_test_guid (ut_in.universal_id);
FOR tc_index IN 1 .. l_test_cases.COUNT
LOOP
l_code_found := FALSE;
show_header (qu_result_xp.c_test_case
, l_test_cases (tc_index).NAME
);
show_cust_for (qu_result_xp.c_test_case
, 'Declarations'
, l_test_cases (tc_index).declarations
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_test_case
, 'Subprograms'
, l_test_cases (tc_index).declare_programs
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_test_case
, 'Setup'
, l_test_cases (tc_index).setup_code
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_test_case
, 'Pre-execution'
, l_test_cases (tc_index).pre_execution_code
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_test_case
, 'Alternate execution'
, l_test_cases (tc_index).alt_execution_code
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_test_case
, 'Alternate assertion code'
, l_test_cases (tc_index).assertion_code
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_test_case
, 'Post-execution'
, l_test_cases (tc_index).post_execution_code
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_test_case
, 'Exceptions'
, l_test_cases (tc_index).EXCEPTIONS
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_test_case
, 'Teardown'
, l_test_cases (tc_index).teardown_code
, code_found_inout => l_code_found
);
summarize (l_code_found);
show_outcomes (l_test_cases (tc_index));
END LOOP;
END show_test_cases;
PROCEDURE show_unit_tests (harness_in IN qu_harness_tp.qu_harness_rt)
IS
l_code_found BOOLEAN;
l_unit_tests qu_unit_test_tp.qu_unit_test_tc;
BEGIN
l_unit_tests :=
qu_unit_test_qp.ar_fk_qu_utest_hrn (harness_in.universal_id);
FOR ut_index IN 1 .. l_unit_tests.COUNT
LOOP
l_code_found := FALSE;
show_header
(qu_result_xp.c_unit_test
, l_unit_tests (ut_index).program_type
|| '-'
|| l_unit_tests (ut_index).program_name
|| CASE
WHEN l_unit_tests (ut_index).overload IS NULL
THEN NULL
ELSE '-'
END
);
show_cust_for (qu_result_xp.c_unit_test
, 'Declarations'
, l_unit_tests (ut_index).declarations
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_unit_test
, 'Subprograms'
, l_unit_tests (ut_index).declare_programs
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_unit_test
, 'Setup'
, l_unit_tests (ut_index).setup_code
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_unit_test
, 'Exceptions'
, l_unit_tests (ut_index).EXCEPTIONS
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_unit_test
, 'Teardown'
, l_unit_tests (ut_index).teardown_code
, code_found_inout => l_code_found
);
summarize (l_code_found);
show_test_cases (l_unit_tests (ut_index));
END LOOP;
END show_unit_tests;
BEGIN
l_code_found := FALSE;
show_header (qu_result_xp.c_harness, harness_in.NAME);
show_cust_for (qu_result_xp.c_harness
, 'External Setup'
, harness_in.ext_setup_code
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_harness
, 'Public Declarations'
, harness_in.declarations
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_harness
, 'Public Subprograms'
, harness_in.declare_programs
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_harness
, 'Priviate Declarations'
, harness_in.private_declarations
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_harness
, 'Private Subprograms'
, harness_in.private_declare_programs
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_harness
, 'Setup'
, harness_in.setup_code
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_harness
, 'Teardown'
, harness_in.teardown_code
, code_found_inout => l_code_found
);
show_cust_for (qu_result_xp.c_harness
, 'External Teardown'
, harness_in.ext_teardown_code
, code_found_inout => l_code_found
);
summarize (l_code_found);
show_unit_tests (harness_in);
END show_cust_code;
PROCEDURE show_cust_code (
harness_guid_in IN VARCHAR2
, show_code_in IN BOOLEAN DEFAULT FALSE
)
IS
l_harness qu_harness_tp.qu_harness_rt
:= qu_harness_qp.onerow (harness_guid_in);
BEGIN
show_cust_code (l_harness);
END show_cust_code;
PROCEDURE show_cust_code (
owner_in IN VARCHAR2
, program_name_in IN VARCHAR2
, show_code_in IN BOOLEAN DEFAULT FALSE
)
IS
l_harness qu_harness_tp.qu_harness_rt
:= qu_harness_qp.or_un_qu_hrn_name (owner_in, program_name_in);
BEGIN
show_cust_code (l_harness);
END show_cust_code;
PROCEDURE show_log (
since_in IN DATE DEFAULT NULL
, filter_context_in IN VARCHAR2 DEFAULT '%'
, filter_text_in IN VARCHAR2 DEFAULT '%'
)
IS
TYPE log_rt IS RECORD (
ID INTEGER
, CONTEXT VARCHAR2 (32767)
, text VARCHAR2 (32767)
);
l_logrec log_rt;
CV sys_refcursor;
BEGIN
qu_runtime.pl (RPAD ('=', 80, '='));
qu_runtime.pl ('Contents of Quest Code Tester Log');
qu_runtime.pl ( ' Logged since '
|| CASE
WHEN since_in IS NULL
THEN '*all entries*'
ELSE TO_CHAR (since_in, 'YYYY-MM-DD HH24:MI:SS')
END
);
qu_runtime.pl (' Context like "' || NVL (filter_context_in, '%')
|| '"');
qu_runtime.pl (' Text like "' || NVL (filter_text_in, '%') || '"');
qu_runtime.pl (RPAD ('=', 80, '='));
OPEN CV FOR 'SELECT *
FROM qu_log
WHERE (created_on >= since_in OR since_in IS NULL)
AND CONTEXT LIKE NVL (filter_context_in, ''%'')
AND text LIKE NVL (filter_text_in, ''%'')
ORDER BY ID';
LOOP
FETCH CV
INTO l_logrec;
EXIT WHEN CV%NOTFOUND;
qu_runtime.pl ( l_logrec.ID
|| ' | '
|| l_logrec.CONTEXT
|| ' | '
|| l_logrec.text
);
END LOOP;
CLOSE CV;
END show_log;
PROCEDURE show_installs
IS
l_version VARCHAR2 (1000);
BEGIN
qu_runtime.pl (RPAD ('=', 40, '='));
qu_runtime.pl (' Quest Code Tester Installations');
qu_runtime.pl (RPAD ('=', 40, '='));
FOR rec IN (SELECT username
FROM all_users)
LOOP
BEGIN
EXECUTE IMMEDIATE 'BEGIN :val := '
|| rec.username
|| '.qu_config.version; end;'
USING OUT l_version;
qu_runtime.pl ( '> Version '
|| l_version
|| ' installed in '
|| rec.username
);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
END show_installs;
PROCEDURE gen_eq_function (
dataset_in IN VARCHAR2
, function_name_in IN VARCHAR2 DEFAULT 'records_equal'
, rowtype_in IN VARCHAR2 DEFAULT NULL
, prefix_cor_in IN BOOLEAN DEFAULT TRUE
)
IS
c_name CONSTANT qu_config.maxvarchar2
:= NVL (function_name_in, 'records_equal');
c_query CONSTANT qu_config.maxvarchar2
:= CASE
WHEN UPPER (dataset_in) LIKE 'SELECT %'
THEN dataset_in
ELSE 'SELECT * FROM ' || dataset_in
END;
l_cur PLS_INTEGER := DBMS_SQL.open_cursor;
l_columns DBMS_SQL.desc_tab;
l_count PLS_INTEGER;
BEGIN
DBMS_SQL.parse (l_cur, c_query, DBMS_SQL.native);
DBMS_SQL.describe_columns (l_cur, l_count, l_columns);
qu_runtime.pl ( CASE
WHEN prefix_cor_in
THEN 'CREATE OR REPLACE '
ELSE NULL
END
|| 'FUNCTION '
|| c_name
|| '(record1_in in '
|| NVL (rowtype_in, dataset_in || '%ROWTYPE')
|| ',record2_in in '
|| NVL (rowtype_in, dataset_in || '%ROWTYPE')
);
qu_runtime.pl
(',nulls_eq_in IN BOOLEAN DEFAULT TRUE) RETURN BOOLEAN IS BEGIN RETURN '
);
FOR indx IN 1 .. l_columns.COUNT
LOOP
qu_runtime.pl ( CASE
WHEN indx = 1
THEN NULL
ELSE ' AND '
END
|| '(record1_in.'
|| l_columns (indx).col_name
|| ' = record2_in.'
|| l_columns (indx).col_name
);
qu_runtime.pl ( ' OR ( record1_in.'
|| l_columns (indx).col_name
|| ' IS NULL AND record2_in.'
|| l_columns (indx).col_name
|| ' IS NULL AND nulls_eq_in))'
);
END LOOP;
qu_runtime.pl ('; END ' || c_name || ';');
END gen_eq_function;
PROCEDURE clear_input_attributes
IS
BEGIN
UPDATE qu_attributes
SET internal_value = NULL
, external_value = NULL
WHERE usage_type = 'INPUT';
COMMIT;
END clear_input_attributes;
PROCEDURE run_test_show_results (
owner_in IN VARCHAR2
, program_name_in IN VARCHAR2
)
IS
l_result qu_config.maxvarchar2;
l_result_row qu_result_xp.last_run_results_api_cur%ROWTYPE;
my_results qu_result_xp.last_run_results_api_rc;
BEGIN
qu_test.run_test_for2 (owner_in => owner_in
, NAME_IN => program_name_in
, result_out => l_result
, results_out => my_results
, unit_test_guid_list_in => NULL
, test_case_guid_list_in => NULL
, delimiter_in => NULL
);
qu_runtime.pl ( 'Overall result of test of "'
|| owner_in
|| '.'
|| program_name_in
|| '": '
|| l_result
);
LOOP
FETCH my_results
INTO l_result_row;
EXIT WHEN my_results%NOTFOUND;
qu_runtime.pl ( RPAD (' ', l_result_row.h_level * 2)
|| ' '
|| l_result_row.NAME
|| '-Status: '
|| l_result_row.result_status
|| '-Description: '
|| l_result_row.description
);
END LOOP;
END run_test_show_results;
PROCEDURE show_privileges
IS
BEGIN
qu_runtime.pl ('Sorry, not yet implemented!');
END show_privileges;
END qu_helper;
/
|
|