Recently I had to do something like this. It did not have to be, and
could not have been made, as general as this.

I can see a couple of considerations here, one on finding the results,
one on
reporting them:

FINDING: MINUS is a blunt but very handy tool. The difficulty in getting
more
precision is that we'd need to distinguish the joined rows from the
outer-joined rows, i.e. those matching on key but not data columns
from those with an unpaired key. This is readily enough done if the
queries run each against a single table, AND that table has a primary
or unique constraint defined. Against multiple tables? Maybe, if joins
are across foreign keys.

Offhand, I'd suggest doing this by establishing the convention that
"key" fields appear first, that the tester must provide the number of
key columns, and that the key columns must be the ordering columns;
that done, the problem becomes manageable. I've some snippets of
PL/SQL about that do column-by-column comparison across tables, which
could be adapted.

REPORTING: How do we report this? Set up a scratch table?
<EMPNO ENAME_1 ENAME_2 DEPTO_1 DEPTNO_2... ENAME_DIFFERS
DEPTO_DIFFERS...> My quick hack simply reported the number of columns
that differed, which was all I needed.


>>> Steven <sfeuer@...> 09/27/02 13:23 PM >>>
At 05:30 PM 9/27/2002 +0300, Albert wrote:
>[I tried to post on the new forum, but it failed]

Albert, please communicate with Patrick Barel <pbarel@...> about the
problem you encountered.

>Hi,
>
>Lately, my test failed on eqQuery assertion. That's good.
>But there is no way to see what data is actually different.
>
>Is it possible to enhance eqQuery so that it prints out the actual
>differences it has found ?
>I doubt, because in PL/SQL you cannot run dynamic select if you do not
>know exactly what columns it has.
>Am I wrong ?

Dear Albert and others:

You are right. This is, I think, a tough one.

This same request came up when I visited Zurich and came across a
project
making extensive use of utPLSQL (which made me so happy). It is a very
reasonable request, but I believe it is VERY challenging.

Right now, utPLSQL takes advantage of the MINUS SQL operator to cleanly
tell us if there are differences. To figure out which row caused the
problem and report on that, well, the dynamic SQL that is required
frankly
boggles my mind.

Perhaps others will be less boggled, so I hereby invite some smart and
energetic person to take on this challenge: even if we just talk about
it
on this or soon the new forum, let's see if anyone has some really great

ideas about how to "get granualar" with a dynamic query (how about
DBMS_SQL.DESCRIBE_COLUMNS, for example?).

Thanks, SF