May
2
Written by:
QCTO Blog
Monday, May 02, 2011
by Finn Ellebaek Nielsen
This blog post describes a best practice for comparing datasets with Quest Code Tester for Oracle (CT).
The code described here has been tested with:
- Oracle Database 11g Release 1 11.1.0.6.0.
- Oracle Database 11g Release 2 11.2.0.1.0.
- Code Tester 1.9.1.505.
- Code Tester 2.0.0.582.
Introduction
CT enables you to compare datasets in the form of queries or REF CURSORs
to a corresponding outcome, which is great. However, if there are
differences, CT will only tell you that they are not equal, not details
about where they differ. This is unfortunate, as it may be difficult to
manually investigate the differences outside CT, especially if the data
you need to look at is removed after the test has been executed.
Let's look at an example. We have the following simple function returning a REF CURSOR in the SCOTT schema:
create or replace function get_data
return sys_refcursor as
result sys_refcursor;
begin
open result for
select 1 id,
123.456 n,
'abcDEF' vc,
cast(timestamp'2011-02-01 14:25:30' as date) d
from dual;
return result;
end get_data;
/
We then implement two test cases, one that will succeed and one that will fail.
In the succeeding test case (let's call it '01 Equal') we create an outcome with the following setup:
Query:
select to_number(null) id,
to_number(null) n,
to_char(null) vc,
to_date(null) d
from dual
Initialization:
open e_function_return_value1 for
select 1 id,
123.456 n,
'abcDEF' vc,
cast(timestamp'2011-02-01 14:25:30' as date) d
from dual;
Correspondingly, the failing test case (let's call it '02 Not Equal') will have this setup:
Query:
select to_number(null) id,
to_number(null) n,
to_char(null) vc,
to_date(null) d
from dual
Initialization:
open e_function_return_value1 for
select 123.456789 n,
'ABCdef' vc,
timestamp'2011-02-01 16:47:30' d
from dual;
(the differences from the values returned are marked with underline).
When running our test definition we will get the following results:
- 01 Equal: Cursor variable from program = expected cursor variable.
- 02 Not Equal: Cursor variable from program <> expected cursor variable.
The first one is OK but the second one is not very helpful.
XML Diffing
The fact that CT doesn't provide us what we would like doesn't mean
that we can't get it. We just have to adopt another method of
comparison. My recommended best practice for comparing datasets in CT is
that you convert the datasets to XML and compare against given XML in
the outcome.
This practice has several benefits:
- You don't have to create expected outcome in tables or semi complex REF CURSORS.
This helps ensuring that your test definitions are self enclosed and
that they don't require external data to be in place for their expected
behavior. You could incorporate INSERT/DELETE
statements into the customization's initialization/cleanup sections but
this is fiddly and decoupled from the test case outcome.
- You can either write the XML by hand or call a function (see below) that provided a query/REF CURSOR
against given table(s) will return the data on Oracle's canonical XML
form. This can be done in an environment with reference data.
- You use one common differ for both datasets and XML.
I've written the following two separate blog posts in relation to this:
Based on these blog posts, assume that we have the following in the database (the source code is listed in the blog posts):
- XML_GET: A package used for converting Oracle data (a query, REF CURSOR, ANYDATA etc) to XML.
- XMLDIFF2: A standalone function that provided two XML documents returns an XML document with details about their differences. NULL if no differences.
In order to use this, we adopt the following for each outcome:
Instead of working directly on the output parameter/function return
value/query, we use XML_GET to convert to XML:
- For a dynamic query, this is done with a call to XML_GET.FROM_SELECT,
specifying the query in the input parameter. There's an overloaded
version taking a collection of bind variable name/value pairs as well.
- For a REF CURSOR, this is done with a call to XML_GET.FROM_REF_CURSOR, specifying the REF CURSOR as a parameter.
Furthermore, we call XMLDIFF2 specifying the result from XML_GET as the first parameter and the XML we expect as the second parameter. We then expect this result to be NULL. XMLDIFF2 returns an XMLTYPE, which isn't handled gracefully by CT, ie nothing useful is displayed if XMLDIFF2 returns a value that is not NULL. So let's create a wrapper around XMLDIFF2 that returns a CLOB instead:
create or replace function xmldiff3(
xml1 in xmltype,
xml2 in xmltype
)
return clob as
diff xmltype;
begin
diff := xmldiff2(xml1, xml2);
if diff is not null then
$if not dbms_db_version.ver_le_10 $then
return diff.getclobval(0, 2);
$else
return diff.getclobval;
$end
end if;
return null;
end xmldiff3;
/
And let's create two new test cases to test this out: In the
succeeding one ('03 Equal 2') we create the outcome to be based on an
expression instead of on the function return value:
Base data type of expression: CLOB
Variable name: f_expression1
Data type used to declare variable: CLOB
Expression:
xmldiff3(
xml_gen.from_ref_cursor(q##function_return_value),
XMLTYPE('
<ROWSET>
<ROW>
<ID>1</ID>
<N>123.456</N>
<VC>abcDEF</VC>
<D>2011-02-01T14:25:30</D>
</ROW>
</ROWSET>
')
)
So basically the first argument to XMLDIFF3 is the function return value (our REF CURSOR) that we then convert to XML through the call to XML_GEN.FROM_REF_CURSOR and the second argument is the expected XML to compare against. We then expect this expression to be NULL, which we set up in the outcome with a test type of "IS NULL".
Similarly for the failing one ('04 Not Equal 2') with the following expression:
xmldiff3(
xml_gen.from_ref_cursor(q##function_return_value),
XMLTYPE('
<ROWSET>
<ROW>
<ID>1</ID>
<N>123.456789</N>
<VC>ABCdef</VC>
<D>2011-02-01T16:47:30</D>
</ROW>
</ROWSET>
')
)
The differences from what we'll actually get are marked with an
underline and the XML differ hopefully will pick these differences up
and show them.
When running this, we get the following results:
- 01 Equal: Cursor variable from program = expected cursor variable.
- 02 Not Equal: Cursor variable from program <> expected cursor variable.
- 03 Equal 2: Value is NULL.
- 04 Not Equal 2: Value is NOT NULL.
The result for '04 Not Equal 2' is surprising but it turns out that CT doesn't provide the non-NULL value for the result for the test type "IS NULL" if the datatype is CLOB (I've logged an Enhancement Request with Quest for this to be fixed). Let's change the test type to "=" and provide a NULL value and now we get the following result:
From Program Value of "<DIFFERENCES>
<DIFFERENCE>
<XPATH><![CDATA[/ROWSET/ROW[ID="1"]/N]]></XPATH>
<OPERATION>UPDATE</OPERATION>
<VALUE1><![CDATA[123.456]]></VALUE1>
<VALUE2><![CDATA[123.456789]]></VALUE2>
</DIFFERENCE>
<DIFFERENCE>
<XPATH><![CDATA[/ROWSET/ROW[ID="1"]/VC]]></XPATH>
<OPERATION>UPDATE</OPERATION>
<VALUE1><![CDATA[abcDEF]]></VALUE1>
<VALUE2><![CDATA[ABCdef]]></VALUE2>
</DIFFERENCE>
<DIFFERENCE>
<XPATH><![CDATA..." <> Expected Value "NULL"
which is better but CT's behavior in this test type is to cut off the
value(s) after 450 characters so we don't get the full value. Until
Quest fixes the limitation with CLOB and test type "IS NULL", we can
create a VARCHAR2-based version of the XML differ, such that we get more data:
create or replace function xmldiff4(
xml1 in xmltype,
xml2 in xmltype
)
return varchar2 as
diff xmltype;
begin
return substr(xmldiff3(xml1, xml2), 1, 32767);
end xmldiff4;
/
We can now duplicate our test cases '03 Equal 2' and '04 Not Equal 2' to '05 Equal 3' and '06 Not Equal 3' respectively, using XMLDIFF4 instead of XMLDIFF3 and using the base datatype VARCHAR2 and declaration datatype VARCHAR2(32767). For the new test case '06 Not Equal 3' we must use test type "IS NOT NULL" and in the result we now get:
Value is NOT NULL: <DIFFERENCES>
<DIFFERENCE>
<XPATH><![CDATA[/ROWSET/ROW[ID="1"]/N]]></XPATH>
<OPERATION>UPDATE</OPERATION>
<VALUE1><![CDATA[123.456]]></VALUE1>
<VALUE2><![CDATA[123.456789]]></VALUE2>
</DIFFERENCE>
<DIFFERENCE>
<XPATH><![CDATA[/ROWSET/ROW[ID="1"]/VC]]></XPATH>
<OPERATION>UPDATE</OPERATION>
<VALUE1><![CDATA[abcDEF]]></VALUE1>
<VALUE2><![CDATA[ABCdef]]></VALUE2>
</DIFFERENCE>
<DIFFERENCE>
<XPATH><![CDATA[/ROWSET/ROW[ID="1"]/D]]></XPATH>
<OPERATION>UPDATE</OPERATION>
<VALUE1><![CDATA[2011-02-01T14:25:30]]></VALUE1>
<VALUE2><![CDATA[2011-02-01T16:47:30]]></VALUE2>
</DIFFERENCE>
</DIFFERENCES>
which in fact covers all the differences. We still would not get all
differences in all cases as the difference XML document would be
truncated to
4000 - LENGTH('Value is NOT NULL: ') = 3981 characters (assuming no multi byte characters are used)
but this is still far better than 450. I've logged an Enhancement
Request with Quest in order for CT to save the full value of outcome
expressions in QU_RESULT_FULL_VALUE (currently, only input/output parameter and function return values are saved in this table that was introduced in CT 1.9.0).
SELECT Instead of REF CURSOR
If you prefer to test the outcome with a SELECT statement you can create an outcome expression as follows (testing that the SCOTT.DEPT table is intact):
xmldiff4(
xml_gen.from_select('select * from scott.dept'),
XMLTYPE('
<ROWSET>
<ROW>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</ROW>
<ROW>
<DEPTNO>20</DEPTNO>
<DNAME>RESEARCH</DNAME>
<LOC>DALLAS</LOC>
</ROW>
<ROW>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
<LOC>CHICAGO</LOC>
</ROW>
<ROW>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
<LOC>BOSTON</LOC>
</ROW>
</ROWSET>
')
)
You can get the XML for the expected outcome by calling xml_gen.from_select('select * from scott.dept') in an environment where you know the data is correct.
If you would like to use bind variables, you can create the expression like this (testing that a few of the rows from SCOTT.EMP are intact):
xmldiff4(
xml_gen.from_select(
'select * from scott.emp where empno in (:empno1, :empno2)',
bind_variable_c(
bind_variable_t('empno1', '7654'),
bind_variable_t('empno2', '7934')
)
),
XMLTYPE('
<ROWSET>
<ROW>
<EMPNO>7654</EMPNO>
<ENAME>MARTIN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>1981-09-28T00:00:00</HIREDATE>
<SAL>1250</SAL>
<COMM>1400</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
<MGR>7782</MGR>
<HIREDATE>1982-01-23T00:00:00</HIREDATE>
<SAL>1300</SAL>
<COMM/>
<DEPTNO>10</DEPTNO>
</ROW>
</ROWSET>
')
)
Conclusion
I've demonstrated how you can get more details from unexpected results when using dataset-based outcomes.